database-migrations

Database Migrations

A migration runs against live data while old code may still be serving traffic. The dangerous moment is never the steady state before or after — it's the in-between, when the schema has changed but not every app instance has. Design every change to be correct during that window. Heavy detail lives in references/; copy-paste material in examples/; a runnable safety check in scripts/.

Mental model

Two things deploy on different clocks: your schema (one atomic change) and your code (rolled out instance-by-instance over minutes). A migration is safe only if both the old and new code work against both the old and new schema for the overlap window. That single rule explains almost every practice below.

Concern	Safe answer
What changed	the smallest possible step
When old code sees it	it must still work (backward-compatible)
Locks held	none long enough to block traffic
If it goes wrong	a tested, reversible path back
Big rename/retype	expand → migrate → contract, across deploys

1. Additive-first

Prefer additive, backward-compatible changes. Adding a nullable column, adding a table, or adding an index never breaks code that doesn't know about it. Destructive changes (drop/rename column, change type, add NOT NULL) break the old code still running mid-deploy, so they must be sequenced — see §3.

-- SAFE: old code ignores the new column; new code can start using it.
ALTER TABLE users ADD COLUMN email_verified_at timestamptz NULL;

-- UNSAFE in one step: old code still INSERTs rows without this column.
ALTER TABLE users ADD COLUMN email_verified_at timestamptz NOT NULL;

2. Never hold a long lock

Most outages from migrations are lock waits, not data loss. A statement that rewrites a table or takes an ACCESS EXCLUSIVE lock blocks every read/write behind it, and that queue backs up into your connection pool within seconds.

• Add columns as nullable (or with a constant default — on modern Postgres a constant default is metadata-only and does not rewrite the table; a volatile default does).
• Build indexes with CREATE INDEX CONCURRENTLY (no table rewrite, no write lock — but it can't run inside a transaction).
• Backfill in batches with short transactions, not one giant UPDATE that locks every row.
• Set a lock_timeout so a migration that can't get its lock fails fast instead of stalling traffic.