The migration had gone wrong, and the logs were full of red. A new column was needed—fast. The schema wasn’t built for today’s data requirements, and every query was slowing to a crawl. The fix was clear: alter the table, add a new column, and make it production-ready without downtime.
Adding a new column sounds simple. In small datasets, it is. In active production systems with millions of rows, it can be dangerous. SQL engines lock writes during certain operations. Default values on new columns can trigger costly full-table rewrites. The wrong move can choke a critical workload.
The safe approach is a migration script tuned for your database engine. MySQL and PostgreSQL handle new columns differently. PostgreSQL can add a new column with a default value instantly if the default is constant and not volatile. MySQL may rewrite entire tables depending on storage engine and version. Always check the docs for your version before running an ALTER TABLE in production.
For zero-downtime migrations, break the change into steps. Add the column as nullable first. Then backfill in small batches while live traffic continues. Once complete, set NOT NULL constraints and defaults. This lowers the lock risk and keeps the API layer in sync with the database schema.