Adding a new column to a table is one of the most common schema changes in any database. It should be fast, safe, and repeatable. The real challenge is doing it without downtime, without locking the table, and without breaking queries that expect the old schema.
A proper process starts with understanding the database engine’s behavior. In PostgreSQL, adding a nullable column without a default is an instantaneous metadata-only change. Adding a column with a non-null default, however, will rewrite the entire table, which can lock writes for a long time. MySQL and MariaDB behave differently depending on the storage engine, and not all ALTER operations are online.
Version control for schema is not optional. Every new column should be part of a migration script that can run forward and backward. This ensures reproducibility across environments and a clean rollback path in case the column needs to be dropped or altered again.
When adding columns to large production tables, use phased rollouts. First, add the column as nullable with no default. Then backfill data in small batches to avoid load spikes. Finally, apply constraints and defaults once the data is stable. This pattern avoids long blocking operations and reduces deployment risk.