The table was live in production when the request came in: add a new column. No downtime, no side effects, no broken queries. The kind of change that seems simple until it isn’t.
Adding a new column is one of the most common database schema changes. Done wrong, it locks tables, spikes latency, and risks lost revenue. Done right, it becomes a seamless upgrade, invisible to the end user. The details matter: database engine, table size, default values, backfill strategy, and deployment workflow.
In MySQL, adding a column with a non-null default to a large table can trigger a full table rewrite. On Postgres, certain operations are instant if the new column allows nulls and has no default. Understanding the DDL path your database chooses is key. Check explain plans. Measure on staging with production-sized data.
Zero-downtime schema migration means using tools like pt-online-schema-change for MySQL or gh-ost for controlled rollouts. In Postgres, break the change into steps: first add the new column nullable, then backfill data in small batches, finally add constraints or defaults. Monitor locks, replication lag, and query performance at each step.