Ship every schema change as two deploys

The claim

A migration that changes the shape of a table and the code that reads it in the same deploy cannot be rolled back. During the seconds or minutes when old and new application code are both running — which is every deploy, unless you take the site down — one of them is talking to a schema it does not understand. Split the change across two deploys and both problems disappear.

The pattern: expand, migrate, contract

Take a common request: rename users.phone to users.phone_e164 and reformat the values. The one-step version is a single migration and a code change. It breaks the moment a request lands on an old container.

The three-step version:

Deploy 1 — expand. Add the new column. Write to both, read from the old.

ALTER TABLE users ADD COLUMN phone_e164 text;

Backfill — separate from any deploy. Batched, resumable, throttled:

UPDATE users SET phone_e164 = normalise_phone(phone)
WHERE phone_e164 IS NULL AND id IN (
  SELECT id FROM users WHERE phone_e164 IS NULL ORDER BY id LIMIT 5000
);

Loop that with a short pause between batches. A single UPDATE over two million rows holds row locks for the entire statement and will bloat the table; five thousand at a time with a 200 ms sleep finishes in under half an hour and nobody notices.

Deploy 2 — switch reads. Read from the new column, still write both.

Deploy 3 — contract. Stop writing the old column, then drop it, ideally a week later once you are confident.

ALTER TABLE users DROP COLUMN phone;

Each deploy is independently reversible, because at no point does running code depend on a schema change made in the same release.

The locks that actually bite

Postgres takes an ACCESS EXCLUSIVE lock for most ALTER TABLE forms. The lock is usually held for microseconds — adding a nullable column without a default is a catalogue change only — but acquiring it requires waiting for every existing transaction on that table to finish, and while it waits, it queues every new query behind it. One long-running report can turn a instantaneous migration into a two-minute outage.

Guard against it:

SET lock_timeout = '3s';
SET statement_timeout = '30s';
ALTER TABLE users ADD COLUMN phone_e164 text;

If the lock cannot be acquired in three seconds, the migration fails cleanly and you retry rather than stalling the application. This one setting prevents more incidents than any amount of careful scheduling.

Three specific operations to handle differently

• Indexes: always CREATE INDEX CONCURRENTLY. It takes longer and cannot run inside a transaction, which means it cannot live in a normal migration file in most frameworks — run it as its own step. A failed concurrent build leaves an invalid index behind; check with SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid; and drop any you find.
• NOT NULL: add a CHECK (col IS NOT NULL) NOT VALID constraint, run VALIDATE CONSTRAINT separately, then set the column NOT NULL. Modern Postgres uses the validated constraint to skip the full table scan.
• Foreign keys: same pattern — NOT VALID first, validate second. The validation takes a lesser lock and can run during business hours.

Making it testable

The pattern only holds if someone verifies that old code still works against the new schema. Add one step to your pipeline: check out the previous release tag, run its test suite against a database that has had the new migration applied, and fail the build if anything breaks. That single job catches the case where a developer added a NOT NULL column with no default, which is the most common way a well-intentioned expand step takes the site down anyway.

What this buys you

The obvious benefit is avoiding downtime. The one that matters more is that rollback becomes a real option. When a deploy misbehaves at 16:40, the difference between reverting the application in ninety seconds and reasoning about whether a migration can be safely reversed is the difference between an inconvenience and an evening.

Two rules to enforce in review

1. No pull request contains both a schema change and a code change that depends on it. If a reviewer sees both, it splits.
2. Every migration sets lock_timeout. Put it in the migration template so nobody has to remember.

The cost is that a rename now takes three deploys over a week instead of one on Tuesday afternoon. In exchange, no deploy in that sequence can take the site down, and every one of them can be undone in the time it takes to redeploy the previous container image.