Adding a new column sounds simple. In small datasets, it is. In production systems with terabytes of data, it becomes a test of precision, speed, and safety. A careless schema migration can lock rows, block writes, or cause downtime. The right approach depends on your database engine, infrastructure, and deployment strategy.
For PostgreSQL, adding a nullable new column without a default value is fast because it only updates the table metadata. Adding a non-null column with a default is slower since it rewrites the table. In MySQL, ALTER TABLE often requires a full table copy unless you use tools like pt-online-schema-change or gh-ost. These choices matter.
You must also think about backfilling. For high-traffic applications, backfill in small batches to avoid long transactions or replication lag. Use feature flags to deploy code that can safely handle both old and new schemas during the migration. Deploy the new column first, backfill next, then update application logic to use it, and finally remove any transitional code.