Migrations that don't wake anyone up

A migration on a table with a thousand rows is a command. A migration on a table with two billion rows is a project, and the difference in approach is total.

Here is the pattern that works, and the specific things that go wrong.

the locks that kill you#

The failure mode is almost always a lock held longer than expected, blocking every query behind it, until connections exhaust and the application falls over.

In Postgres, these are safe (metadata-only, fast):

• ADD COLUMN with no default, or with a non-volatile default (since PG 11).
• DROP COLUMN (marks it dead, does not rewrite).
• ADD CONSTRAINT ... NOT VALID, then VALIDATE CONSTRAINT separately.
• CREATE INDEX CONCURRENTLY.
• Renaming things.

These rewrite the table and hold an exclusive lock for the duration:

• ALTER COLUMN TYPE (most of the time).
• ADD COLUMN with a volatile default.
• SET NOT NULL directly (use a CHECK constraint validated separately, then convert).

The one everyone hits: CREATE INDEX without CONCURRENTLY blocks writes for the entire build. On a large table that is minutes to hours. CONCURRENTLY takes longer and does not block, and it can fail — leaving an invalid index you must drop and retry.

The one nobody expects: even a fast ALTER TABLE must acquire an exclusive lock, and it will queue behind any long-running transaction. Then everything queues behind it. A migration that takes 5 ms can cause a 10-minute outage if a reporting query is holding a lock.

Set lock_timeout before every DDL statement:

SET lock_timeout = '3s';
ALTER TABLE orders ADD COLUMN region text;

If it cannot get the lock in three seconds, it fails and you retry, rather than queueing behind something and taking the site down. This one line prevents a large share of migration incidents.

expand and contract, always#

Never change a column in place. Four deploys:

1. Expand. Add the new column, nullable. Deploy. Old code ignores it.

2. Backfill and dual-write. Application writes both columns. Backfill existing rows in batches. Deploy.

-- in a loop, with a pause between batches
UPDATE orders SET region = derive_region(country)
WHERE region IS NULL AND id BETWEEN $1 AND $2;

Batch size in the thousands. Sleep between batches. Monitor replication lag and back off if it grows — a backfill that outruns replication is how you take down your read replicas.

3. Switch reads. Read from the new column. Deploy. Still fully reversible, the old column is intact and still being written.

4. Contract. Stop writing the old column, deploy, and drop it days later.

Four deploys instead of one. Every intermediate state works with both the old and new code, so any deploy can be rolled back independently.

the backfill rules#

Idempotent. It will be interrupted. It must be safe to re-run.

Resumable. Track progress. A backfill that starts over from the beginning after a failure will never finish on a large table.

Rate limited. Not "as fast as possible." A backfill competing with production traffic for I/O is a self-inflicted incident. Watch replication lag, watch p99 latency, and throttle.

Observable. Log progress. "Backfilled 4.2M of 180M rows, ETA 6h" lets someone make a decision. Silence does not.

Kill-switchable. You need to be able to stop it immediately without deploying. A flag it checks between batches.

testing it#

On a copy of production, at production size. A migration tested on a 10,000-row development database tells you the syntax is right and nothing about the runtime.

With concurrent load. Locks only matter under contention. Run the migration against a restored copy while replaying production traffic.

With the rollback. Actually run it. A rollback plan that has never been executed is a hypothesis.

the checklist#

Before any production migration on a large table:

• [ ] Tested on production-sized data with concurrent load
• [ ] lock_timeout set on every DDL statement
• [ ] Expand-contract, not in-place
• [ ] Backfill is batched, rate-limited, resumable, and killable
• [ ] Rollback tested
• [ ] Replication lag monitored with a threshold to pause at
• [ ] Someone is watching, with the kill switch, for the duration
• [ ] Not on a Friday

That last one is not superstition. It is that the people who understand the change should be available for the two days after it, and on Friday they are not.