The database schema was final, until it wasn’t. A client needed real-time metrics, and the only clean way forward was to add a new column. In production. On a table with millions of rows.
Adding a new column sounds simple, but the wrong approach can lock writes, spike CPU, or cause hours of downtime. In PostgreSQL, ALTER TABLE ADD COLUMN is a fast metadata change if you set a default of NULL. But adding a column with a non-null default rewrites the entire table. MySQL behaves differently by engine type: InnoDB can sometimes add a column instantly, but not if constraints or certain data types are involved. Knowing the execution path matters.
Plan the migration. First, audit indexes, triggers, and dependent views. A new column can break assumptions in ORM models or ETL pipelines. Check your application code for hard-coded column lists. Make sure your migrations are idempotent and safe to run in multiple environments.
For zero-downtime changes, use a phased approach. Deploy the new column with NULL values. Deploy code that can read from it without requiring data. Backfill asynchronously in small batches to avoid locking and replication lag. Then enforce constraints when the data is complete.