You needed a new column, and you needed it without downtime.
Adding a new column is one of the most common schema changes in relational databases. It is also one of the most dangerous when done at scale. A careless ALTER TABLE can block queries, lock tables, and take down production. The right approach depends on database type, storage engine, and live workload.
In PostgreSQL, ALTER TABLE ADD COLUMN is fast when adding a nullable column without a default. It only updates metadata and avoids rewriting existing rows. But adding a default or a NOT NULL constraint triggers a full table rewrite. That can choke performance on large datasets.
In MySQL with InnoDB, online DDL can add a column without a full lock, depending on the MySQL version. Still, on massive tables, operations may rebuild data under the hood. Plan for replication lag, binlog size, and buffer pool effects.
If you must backfill data into a new column, do it in controlled batches. For example, update small row sets with a WHERE clause and LIMIT. This avoids large transactions that bloat the write-ahead log or undo log. Use application logic to populate new values gradually before enforcing strict constraints.
Adding an indexed column is another dimension. Create the column first, backfill data, then add the index. This limits the size of index creation operations. In Postgres, consider CREATE INDEX CONCURRENTLY to avoid blocking writes; in MySQL, review online index creation options.
For zero-downtime migrations, use techniques like:
- Deploying code that can handle the old and new schema.
- Rolling out the column addition first, then updating services to use it.
- Cleaning up old paths only after the new data is verified.
Schema evolution is inevitable. The key is controlling blast radius. Always test migrations in staging with production-scale data. Monitor locks, replication lag, and query behavior during the change.
Don’t let your next new column bring down production. See how you can run safe, live database schema changes with hoop.dev — spin it up in minutes and watch it work.