Adding a new column sounds simple. It isn’t. Done wrong, it locks queries, stalls deployments, and burns through CPU. Done right, it lands with zero downtime, full integrity, and immediate availability.
First, define the new column in your schema with a type that matches the data you will store. Set sensible defaults. Avoid nulls unless they’re intrinsic to the field’s meaning.
Second, decide how to populate it. For small datasets, a single migration step may work. For large datasets, break the update into backfill batches. Deploy the empty column, backfill asynchronously, then apply constraints and indexes when the data load is complete.
Third, understand the database engine’s behavior. In PostgreSQL, adding a column with a constant default locks the table while rewriting all rows. Instead, add it without default, update in batches, then alter the default for future inserts. MySQL and others have their own performance quirks—know them before you commit.