The migration was live, and the schema needed a new column. One change, but it had to be exact. No gaps. No downtime. Failure meant broken queries and angry users.
Adding a new column sounds simple, but in production it is a high-stakes operation. Schema changes can lock tables, block writes, and bring down critical systems. The right approach depends on the database engine, data size, and uptime requirements.
In PostgreSQL, ALTER TABLE ... ADD COLUMN is fast for metadata-only changes, but setting a default value on an existing table can rewrite every row. The operation grows dangerous as row count grows. Use ADD COLUMN first without a default, then backfill data in controlled batches. Apply the default in a separate step to avoid long locks.
In MySQL, adding a new column can be safe with ALGORITHM=INPLACE for certain cases, but watch for triggers that force a table copy. For massive datasets, online schema migration tools like pt-online-schema-change or gh-ost help by copying rows in the background while staying in sync with writes.
For distributed databases, adding columns requires careful coordination. Some engines ensure schema changes propagate cluster-wide before writes hit the new column, others allow schema drift. Always confirm replication state and force consistency before application code starts using the new field.