Adding a new column to an existing table is one of the most common schema changes. It’s also one of the most misunderstood. On small datasets, the change is almost invisible. On production systems with millions of rows, a simple ALTER TABLE can lock writes, slow queries, and ripple through dependent services.
The right approach starts with knowing the database engine’s behavior. In PostgreSQL, adding a nullable column with no default is fast—it updates metadata only. But adding a column with a non-null default rewrites the table, triggering heavy I/O. MySQL’s behavior varies by version; some support instant ADD COLUMN for certain situations, but others still rewrite data. Always test in a staging environment that mirrors production size and load.
Backfill strategies matter. If the column needs initial values, avoid doing it in one massive transaction. Instead, run batched updates, control transaction size, and monitor replica lag. This minimizes load spikes and reduces the risk of downtime.