The migration stalled. Everyone stared at the schema diff. A new column was required, but no one agreed on how to add it without downtime.
Adding a new column sounds simple. It isn’t. In production databases with terabytes of data, the wrong ALTER TABLE can lock writes, trigger cascading errors, or break replication. Choosing the right approach depends on the database engine, the table size, and the operational constraints.
In MySQL, adding a column with a default value can rewrite the entire table unless you use ALGORITHM=INPLACE or ALGORITHM=INSTANT where supported. PostgreSQL handles certain new column operations faster, but adding a NOT NULL column without a default still requires a full table rewrite. These details matter. They decide whether your deployment takes seconds or hours.
Best practice is to split the change into safe steps. First, deploy the new column as nullable with no default. Write application code that can handle both old and new data. Backfill in controlled batches, monitoring impact on I/O and locks. Once the column is populated, enforce constraints in a separate migration.