A single schema change can decide the fate of performance, uptime, and deployment windows. Adding a new column sounds simple, but in systems with millions of rows or high write traffic, the wrong approach can block queries, lock tables, and trigger long outages. The execution path of an ALTER TABLE statement can vary between database engines, versions, and configurations. Knowing what happens under the hood turns guesswork into control.
When you add a new column in PostgreSQL, the database may scan and rewrite the entire table if you set a default value that is not NULL. This operation can explode storage use and I/O. In MySQL, adding a column without ALGORITHM=INPLACE may lock writes and reads for the full duration of the change. In distributed databases like CockroachDB, schema changes are asynchronous, but metadata updates still propagate across nodes, and data backfills must be planned to avoid throttling operations.
Safe addition of a column often means breaking the change into steps. First, add the column with a NULL default to avoid rewrites. Then backfill data in small batches. Finally, set the default and constraints once the table is ready. For very large datasets, consider online schema change tools such as gh-ost or pt-online-schema-change. These tools create a shadow table with the new column, copy data in the background, and switch over instantly.