The table was live in production when the request came in: add a new column. No migrations planned, no downtime allowed, and millions of rows to touch. The risk was obvious. The path forward was not.
Adding a new column sounds simple until you consider scale, locking, and query performance. In PostgreSQL, for example, adding a nullable column with a default can rewrite the entire table. In MySQL, altering large tables can block reads and writes for minutes or hours. At the right scale, that’s unacceptable.
The safest way to add a new column is to break the change into two steps. First, create the column without a default to avoid a full table rewrite. Then backfill the data in controlled batches. Finally, add the default constraint once the backfill is complete. This approach keeps the migration fast and avoids long locks.
If you use migrations in a CI/CD pipeline, you also need version control for schema changes. Store migration scripts in the repo. Tag them with application versions. Make rollbacks possible. Automated tests that target migrations can catch changes that would break downstream queries.