Zero-downtime schema migrations in Postgres

Most schema migrations are fast, and most migration outages come from fast migrations. Adding a nullable column takes milliseconds of work, but it needs the strongest lock Postgres has, and waiting for that lock can stop every query on the table. Whether a migration is safe depends far less on how much work it does than on which lock it takes, how long it holds it, and what it waits behind.

This article covers the lock queue that turns a quick ALTER TABLE into an outage, the lock levels that matter, which changes rewrite or scan a table, how to add constraints and indexes without blocking writes, how to backfill large tables, and how to sequence schema and code deploys. Examples use Postgres 12 or later; MySQL notes are at the end.

How a one-line migration takes the site down

Postgres grants table locks in arrival order. When a request for a lock conflicts with a lock that is already held, it waits; and any later request that conflicts with the waiting request queues behind it, even if it would be compatible with everything actually held. That rule prevents starvation, and it is what makes DDL dangerous:

1. A long query, say a 20-second report, holds an ACCESS SHARE lock on orders.
2. The migration runs ALTER TABLE orders ADD COLUMN …, which needs ACCESS EXCLUSIVE. It conflicts with the report, so it waits.
3. Every ordinary SELECT, INSERT and UPDATE on orders now conflicts with the waiting ALTER, so they wait too. The connection pool fills within seconds and the application starts failing requests.

The long query does not have to be a report. A transaction left open by a bug, an idle in transaction session, a pg_dump or an analyst’s open console all hold locks for as long as they live. The migration that triggers the outage is usually innocent; it just arrived at the wrong time.

The locks that matter

Postgres has eight table lock modes. For migrations, five of them are worth knowing by name:

• ACCESS SHARE: taken by every SELECT. Conflicts only with ACCESS EXCLUSIVE.
• ROW EXCLUSIVE: taken by INSERT, UPDATE and DELETE. Despite the name, it doesn’t conflict with itself; row-level conflicts are handled separately.
• SHARE UPDATE EXCLUSIVE: taken by VACUUM, ANALYZE, CREATE INDEX CONCURRENTLY and VALIDATE CONSTRAINT. Allows reads and writes; only conflicts with other schema maintenance. This is the lock you want long-running steps to take.
• SHARE: taken by a plain CREATE INDEX. Allows reads, blocks all writes for the whole build.
• ACCESS EXCLUSIVE: taken by most ALTER TABLE forms, DROP, TRUNCATE and VACUUM FULL. Conflicts with everything, including plain reads.

So there are two separate questions for every DDL statement: how strong a lock does it need, and what does it do while holding it? A statement that takes ACCESS EXCLUSIVE but only touches the catalog is safe if it can get the lock quickly. A statement that takes ACCESS EXCLUSIVE and then rewrites a 200 GB table is an outage no matter what.

lock_timeout, and retrying

The fix for the lock queue is to bound how long a migration may wait. SET lock_timeout = '2s' makes any statement that can’t acquire a lock within two seconds fail instead of waiting. Its queue is released, the stalled queries continue, and the worst case is a two-second hiccup on one table. The migration runner then retries the step, a few times with backoff, and fails the deploy loudly if it still can’t get in.

Three related settings belong in the same place:

• statement_timeout on migrations, so a statement that unexpectedly scans or rewrites a large table is cancelled rather than holding its lock for an hour.
• idle_in_transaction_session_timeout on application roles, so a transaction a bug forgot to close can’t hold locks forever.
• One lock-taking statement per transaction. Many migration frameworks wrap a whole file in one transaction, so locks taken by the first statement are held until the last one finishes. Keep each risky step in its own transaction, or split files.

Which changes rewrite or scan the table

Once the lock is held, the question is how long. Some changes are catalog-only, some must check every row, and some rewrite the table and all of its indexes:

• Adding a column is catalog-only if it is nullable or its default is a constant. Since Postgres 11, a constant default is stored once in the catalog and returned for old rows without rewriting them. A volatile default such as gen_random_uuid() or clock_timestamp() must be computed per row, so it rewrites the table.
• Changing a column’s type rewrites the table and rebuilds its indexes, unless the change is binary-compatible: varchar(50) to varchar(100) or to text is free; int to bigint is not. The int-to-bigint migration on a primary key nearing 2³¹ is common enough to plan for before it is urgent.
• Dropping a column is catalog-only (the space is reclaimed as rows are rewritten), and so is renaming one. Both are cheap for the database and expensive for the application, as covered below.

Constraints and indexes without long locks

The general trick is to split one operation that does slow work under a strong lock into a fast step under the strong lock and a slow step under a weak one:

• Foreign keys and CHECK constraints: add them with NOT VALID. That is catalog-only, and the constraint is enforced for new writes from that moment. Then VALIDATE CONSTRAINT checks existing rows while holding only SHARE UPDATE EXCLUSIVE, so reads and writes carry on.
• NOT NULL: SET NOT NULL normally scans the table under ACCESS EXCLUSIVE. From Postgres 12, if a valid CHECK (col IS NOT NULL) constraint exists, it skips the scan. So add that check as NOT VALID, validate it, set NOT NULL, and drop the check.
• Indexes: CREATE INDEX CONCURRENTLY builds without blocking writes, at the cost of two table scans and waiting for every transaction that might use the old snapshot. It can’t run in a transaction block, and if it fails it leaves an INVALID index behind that still slows down writes and must be dropped. (What each index costs once it exists is in the article on indexes.)
• Unique constraints and primary keys: build the unique index concurrently, then ADD CONSTRAINT … UNIQUE USING INDEX, which only attaches the existing index.

Backfilling in batches

Populating a new column on a large table with one UPDATE is a long transaction with every cost that implies: it locks every row it touches until it commits, so concurrent updates to those rows wait; it writes a new version of every row, doubling the table’s size until vacuum catches up; it produces a burst of WAL that replicas may lag behind; and it holds back vacuum across the whole database for its duration. (Why long transactions hurt everything else is covered in the article on transaction isolation.)

Backfill instead in batches of a few thousand rows, each its own transaction:

• Walk the primary key in order and remember where the last batch stopped. Re-scanning from the start for WHERE new_col IS NULL gets slower with every batch.
• Pause between batches, and slow down or stop when replication lag or database CPU crosses a threshold. A backfill is the least urgent work in the system.
• Make it idempotent and restartable. It will be interrupted by a deploy, a failover or someone pressing Ctrl-C.
• Run it from a job, not the migration. A migration that takes three hours blocks every deploy behind it.

Code and schema, deployed separately

During a rolling deploy, old and new versions of the application run side by side against the same database, so every schema state has to work with both. A rename done in one step breaks the old code the moment it lands, and dropping a column breaks any code that still selects it, including ORMs that cache the column list when the process starts. The fix is to split the change into steps that are each safe on their own: add the new column, write to both, backfill, switch reads, stop writing the old one, and only then drop it. That expand, migrate, contract sequence is the same one used to change APIs and event schemas, and it is walked through step by step, with a figure, in the article on API evolution. The database-specific part is the one above: making each of those steps take only brief locks.

Making it hard to get wrong

• Lint migrations in CI. Tools such as squawk (for SQL files) and strong_migrations (for Rails) reject the known-dangerous forms: non-concurrent index builds, volatile defaults, type changes, constraints without NOT VALID, and missing timeouts.
• Test against production-sized data. A migration that takes 40 ms on a developer’s laptop can take 40 minutes on the real table. A restored snapshot of production, or at least a table of the right size, is the only honest test.
• Watch while it runs. pg_stat_activity with pg_blocking_pids() shows who is waiting on whom; pg_stat_progress_create_index shows how far a concurrent index build has got.
• Run migrations as their own deploy step, before the code that needs them, not inside application start-up. Ten instances starting at once should not race to alter the same table.

A safe migration, step by step

Replacing a decimal amount with an integer amount_cents on a large, busy table: timeouts first, a catalog-only expand, a batched backfill, NOT NULL without a scan, a concurrent index, the contract step, and a query for spotting lock waits while it runs.

-- Every DDL statement in a migration gets a short lock_timeout, so a blocked
-- ALTER gives up in seconds instead of queueing the whole application behind it.
-- The migration runner retries the step a few times with backoff.
SET lock_timeout = '2s';
SET statement_timeout = '15s';

-- 1. Expand: a new nullable column is a catalog change, done in milliseconds.
ALTER TABLE orders ADD COLUMN amount_cents bigint;

-- 2. Deploy code that writes both amount and amount_cents on every insert and update.

-- 3. Backfill in small batches, each its own short transaction, outside the migration.
--    Run repeatedly (from a job) until it updates zero rows; pause between batches
--    and watch replication lag.
UPDATE orders
SET amount_cents = (amount * 100)::bigint
WHERE id IN (
  SELECT id FROM orders
  WHERE amount_cents IS NULL AND id > $1     -- $1: the last id the previous batch reached
  ORDER BY id
  LIMIT 5000
)
RETURNING id;

-- 4. NOT NULL without a full-table scan under ACCESS EXCLUSIVE: add the check
--    unvalidated (instant), validate it under a weaker lock, then SET NOT NULL,
--    which Postgres 12+ proves from the valid constraint without scanning.
ALTER TABLE orders ADD CONSTRAINT orders_amount_cents_nn
  CHECK (amount_cents IS NOT NULL) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_amount_cents_nn;   -- SHARE UPDATE EXCLUSIVE
ALTER TABLE orders ALTER COLUMN amount_cents SET NOT NULL;
ALTER TABLE orders DROP CONSTRAINT orders_amount_cents_nn;

-- 5. Indexes: built concurrently, outside a transaction block. If it fails it
--    leaves an INVALID index behind, which must be dropped before trying again.
SET statement_timeout = 0;
CREATE INDEX CONCURRENTLY orders_amount_cents_idx ON orders (amount_cents);

-- 6. Switch reads to amount_cents, deploy, then stop writing amount, deploy.
-- 7. Contract, once no running code mentions the old column.
ALTER TABLE orders DROP COLUMN amount;

-- While any of this runs: who is waiting on whom?
SELECT pid, wait_event_type, state, now() - xact_start AS xact_age,
       pg_blocking_pids(pid) AS blocked_by, left(query, 60) AS query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0 OR now() - xact_start > interval '1 minute'
ORDER BY xact_start;

References

1. PostgreSQL Documentation. Explicit Locking: Table-Level Locks. postgresql.org/docs/current/explicit-locking.html
2. PostgreSQL Documentation. ALTER TABLE (notes on rewrites, NOT VALID and SET NOT NULL) and CREATE INDEX: Building Indexes Concurrently. postgresql.org/docs
3. GoCardless Engineering (2016). Zero-downtime Postgres migrations: the hard parts. gocardless.com/blog
4. Kane, A. strong_migrations: catch unsafe migrations in development. github.com/ankane/strong_migrations
5. Squawk: a linter for Postgres migrations. squawkhq.com
6. GitHub. gh-ost: triggerless online schema migration for MySQL. github.com/github/gh-ost
7. Sadalage, P. & Ambler, S. (2006). Refactoring Databases: Evolutionary Database Design. Addison-Wesley. databaserefactoring.com