Adding a new column in production is not as simple as typing ALTER TABLE. Schema changes can block writes, trigger long-running locks, and break dependent services. The right approach depends on your database engine, table size, and read/write patterns.
In PostgreSQL, ALTER TABLE ADD COLUMN is fast when default values are NULL and no rewrite is needed. Adding a non-null column with a default can rewrite the entire table, so consider adding it nullable first, then updating it in batches, and finally setting the constraint. MySQL behaves differently—ADD COLUMN typically locks the table unless you use ALGORITHM=INPLACE or ALGORITHM=INSTANT in versions that support it. Even then, defaults or generated columns may force a copy.
When working with a large dataset, plan the change. Measure the impact on replication lag. Use feature flags to gate the use of the new column until the schema is ready on all environments. Always test on a staging copy of production data to check for edge cases: foreign keys, triggers, or ORM migrations that generate suboptimal SQL.
For distributed systems, coordinate schema versions across services. Ship code that can handle both old and new column states, deploy the migration, and then enable the new column in application logic. This zero-downtime migration pattern keeps users online while you evolve the schema.