The migration was failing again, and the logs pointed to a missing column. The fix was simple: add a new column. The challenge was doing it without downtime, without breaking queries, and without making the schema harder to maintain.
Adding a new column is one of the most common database changes, but also one of the easiest to get wrong in production. The process depends on the database engine, schema design, and the data migration strategy in play. Even in modern systems with zero-downtime deployment pipelines, schema evolution requires precision.
In PostgreSQL, ALTER TABLE ADD COLUMN is fast if constraints are simple and there’s no default value that rewrites the table. Adding a default that’s not NULL can lock the table for longer than expected. MySQL has improved with instant ADD COLUMN in newer versions, but older versions still copy the whole table for certain operations. For large tables, that can block reads and writes.
Before running any ALTER TABLE in production, test the change in a staging environment with the same data size. Review query plans to ensure indexes and JOIN conditions still work as expected. If the new column is intended for indexing, avoid creating the index in the same migration on a heavily used table. Batch migrations: add the column first, backfill data in small transactions, then create the index.