Migrate PostgreSQL safely

A migration and a deployment do not happen at the same instant. For a while, old and new application instances run against the same database, and both must keep working.

That rules out the obvious version of many changes. Rename a column in one step:

ALTER TABLE app.users
RENAME COLUMN name TO display_name;

The migration succeeds, and every still-running old instance starts failing with column "name" does not exist. The database did nothing wrong. The deployment model did.

Expand and contract

The safe pattern splits every breaking change into compatible steps. Add new structures before code requires them. Backfill in restartable batches, switch reads and writes in separate releases, then remove old structures after the compatibility window.

For the rename, that means: add display_name as a nullable column, deploy code that writes both columns, backfill old rows in batches, deploy code that reads the new column, and only then drop name. Each step works with the versions running before and after it. A failed deploy in the middle strands you in a state that still functions.

Verify the backfill before switching reads:

SELECT count(*) FROM app.users
WHERE display_name IS NULL AND name IS NOT NULL;

Zero means the new column is complete.

Bound the blast radius

Schema changes take locks, and a lock you wait for politely still ruins the site: every query queued behind your ALTER TABLE waits too. Bound how long a migration can wait or run:

SET lock_timeout = '2s';
SET statement_timeout = '5min';

With lock_timeout, a migration that cannot get its lock quickly fails fast and can retry off-peak, instead of stalling production traffic behind it. A failed migration is annoying. A hung one is an outage.

Large constraints, indexes, and table rewrites can lock or scan production data. Test the exact operation on a realistic copy and decide rollback before deployment. “It ran instantly on my laptop” says nothing about a table with fifty million rows.

CREATE INDEX CONCURRENTLY reduces blocking on a live table, but it cannot run inside a transaction block, and migration tools wrap steps in transactions by default. Treat it as its own restartable migration step, with the tool’s transaction wrapper disabled for that step. If it fails partway, it leaves an INVALID index behind; drop it and rerun rather than assuming the index works.