The Four-Step Backfill Pattern for Adding Required DB Columns Safely

Adding a NOT NULL column to a live production database table can cause outright migration failures or costly table locks on large datasets. A safer approach involves four ordered steps: first add the column as nullable, then backfill existing rows in small batches to avoid locking, then update the application to send the new field with every write, and finally apply the NOT NULL constraint once all rows have values. Skipping or reordering any step undermines the entire process — enforcing the constraint before backfilling breaks on old rows, while backfilling before updating the API leaves new rows empty. This expand-migrate-switch-contract sequence applies broadly to other schema changes such as renaming columns, splitting tables, or moving data between services. The core insight is that safe migrations depend less on clever tricks and more on patience and disciplined sequencing.