Adding a new column is one of the most common schema changes in relational databases. Done wrong, it can lock tables, block writes, or cause downtime. Done right, it’s seamless. The approach depends on scale, database engine, and the type of data you need to store.
In PostgreSQL, adding a nullable column without a default is instant. Adding a column with a default value on a large table writes to every row, which can be slow. In MySQL, the storage engine and version dictate whether ALTER TABLE is instant or requires a full table copy. Modern MySQL with ALGORITHM=INSTANT can add certain column types without heavy I/O.
For large datasets, it’s safer to break changes into steps. First, add the column as nullable with no default. Then backfill in small batches while monitoring performance. Finally, set defaults and constraints. This pattern reduces locks and keeps queries responsive.
Application code must handle the rollout. Deploy schema changes and code changes in separate steps. Ensure deployments are backward compatible—reads and writes should work whether or not the new column is present or fully populated.