Adding a new column to a database sounds simple. It rarely is. On small datasets, ALTER TABLE ADD COLUMN runs fast. At scale, it can lock rows, block writes, or crash queries. A single schema change can spike latency, trigger deadlocks, and break downstream jobs. These problems get worse when foreign keys, triggers, or replication streams are involved.
Before adding a new column, decide whether it’s nullable, has a default value, or needs an index. Each choice affects performance and migration risk. Non-null columns with defaults will backfill the entire table at once unless you split the change. Index creation on an empty column burns CPU and IO without immediate query benefits.
For zero-downtime migrations, use a phased approach. Add the column as nullable and without constraints. Deploy code that writes to both the old and new schema. Gradually backfill rows in controlled batches. Create indexes after backfill completes. Only then set constraints or switches.