db-migration-safety

Database migration safety

Every production database migration follows one rule: the old code and the new code must both work against both the old and the new schema, at every point in the rollout.

If that's not true, you're betting on deploy atomicity. Deploy atomicity doesn't exist.

The three-step pattern

For any non-trivial change:

1. Expand — add the new thing, leaving the old in place.
2. Migrate — shift reads and writes to the new thing. Backfill if needed.
3. Contract — once nothing uses the old, remove it.

Each step ships as its own deploy. Each is independently reversible. Never combine expand and contract in one release.

Change cookbook

Add a nullable column

• Safe. One step. Deploy.

Add a NOT NULL column with a default

Dangerous on large tables — a naive ALTER TABLE ... NOT NULL DEFAULT x rewrites the table.

Safe version:

1. Add the column nullable. Deploy.
2. Backfill in batches (e.g., 1000 rows at a time with sleeps). Monitor replication lag.
3. Change code to always write the new column.
4. Add a NOT NULL check constraint as NOT VALID (Postgres) or equivalent.
5. Run VALIDATE CONSTRAINT in a low-traffic window.
6. In a later release, promote to a real NOT NULL column.

Rename a column

Almost never worth it.

If you must:

1. Add the new column.
2. Code writes to both old and new (dual-write).
3. Backfill new from old.
4. Code reads from new.
5. Code stops writing to old.
6. Much later, drop old.

Six deploys for a rename. Usually the new name isn't worth it. Consider a view as an alias.

Change a column type

Same as rename: add the new column, dual-write, backfill, switch reads, drop old.

For type widening (VARCHAR(50) → VARCHAR(200)), some databases support it in place. Check your specific DB.

Add an index

• Postgres: CREATE INDEX CONCURRENTLY. Never the non-concurrent form in production.
• MySQL: ALGORITHM=INPLACE, LOCK=NONE (version-dependent — check).
• Large indexes: schedule off-peak, monitor replication lag.