When working with relational databases, adding a new column should be trivial. Yet it often becomes a choke point during production releases. A poorly planned schema change can lock tables, cause outages, or silently corrupt data. This post covers how to add a new column safely, with zero downtime, and without degrading performance.
First, know your database engine. MySQL, PostgreSQL, and SQL Server handle ALTER TABLE ADD COLUMN differently. In PostgreSQL, adding a new column with a default value will rewrite the table — a potential performance hit. Instead, add the column without a default, then backfill in controlled batches. This avoids long locks.
Second, ensure backward compatibility. If your application reads and writes from the same table across multiple deployment versions, the new column must not break older services. Add it as nullable. Deploy code that can handle both states before starting the backfill process.
Third, plan the index strategy early. Adding a column is not the same as making it useful. If the new column is part of a frequently queried path, create indexes in separate migrations to minimize lock contention.