Adding a new column sounds simple. In production, it can kill performance if done wrong. Large datasets slow down schema changes. Locking tables can freeze live traffic. A careless ALTER TABLE will bring a service to its knees.
The right approach starts with knowing your database engine’s alter strategies. In PostgreSQL, adding a nullable column without a default is instant. MySQL with InnoDB needs more care—online DDL can be safe, but defaults can still cause table rebuilds. For massive tables, use tools like pt-online-schema-change or gh-ost. They avoid blocking writes by creating a shadow table and swapping it in.
Plan the deployment in steps. First, add the column with a safe migration. Next, backfill data in small batches to avoid locking. Then set the default and constraints once rows are populated. Wrap this logic in feature flags so application code only touches the new column when it’s ready.
Monitor everything—DDL progress, replication lag, query plans. Schema changes impact indexes, cache keys, and writes. Always test the migration flow on a recent clone of production. Verify query performance before and after.