Skip to content

Zero-downtime schema migrations: expand, migrate, contract

Change a live schema only in steps both running versions survive, and never let DDL wait in front of your traffic.

Predict first

Before you read: predict

ExamHall's candidate API runs on four pods, all on release 41, which reads candidates.name. Release 42 reads candidates.full_name instead. The deploy replaces the pods one at a time, and once two of the four pods run release 42 it runs the migration, then replaces the other two:

ALTER TABLE candidates RENAME COLUMN name TO full_name;

On an idle copy of the table it finishes in a few milliseconds, and no transaction is open on candidates during the deploy. Every pod serves logins throughout: release 41 runs SELECT id, name FROM candidates WHERE id = $1, and release 42 runs SELECT id, full_name FROM candidates WHERE id = $1.

Which logins fail during the deploy?

Pick one

The 30-second version

During a rolling deploy old and new code share one schema, so change it in steps both survive: add a nullable column, write both, backfill in batches, switch reads, drop the old column a release later. Most ALTERs wait for every open transaction on their table, and every later query queues behind them: set a short lock_timeout and retry.

Change a live schema only in steps both running versions survive, and never let DDL wait in front of your traffic.

How it works

Step 1 of 13

Two versions, one schema. During a rolling deploy, old and new pods query the same tables, so every schema state must work for both.

Analogy: A telephone company changing an area code

The new code is switched on while the old one still connects, for months. Directories are reprinted, phones are set to dial the new code, and only then is the old code withdrawn. In the exchange, an engineer re-patching a trunk first busies it out.

At the telephone company In the migration
both codes connect during the permissive-dialling months expand: the schema serves old and new code (step 2)
new directory entries carry both codes; staff re-key the old directory a page at a time write both, then backfill in batches (steps 3–4)
phones dial the new code; the old code goes after the last month switch reads, then contract (steps 5–6)
switching the code overnight with no permissive months a one-release rename: old callers fail (step 7)
a busied-out trunk takes no new calls while the engineer waits for the last call to end a waiting ACCESS EXCLUSIVE request queues every later query (steps 8–9)
after five minutes he reopens it and tries again later lock_timeout, then retry (step 10)

Breaks down when: a caller who dials the withdrawn code hears a message telling him the new one; an old pod that queries a dropped column just gets an error, so the contract must wait until no such pod runs.

Contrasting case: a column nobody reads yet, added to an idle table at night: no second version and no open transaction, so there is nothing to keep compatible and nothing to wait for.

In real terms: every schema state between two releases must serve both, and every DDL statement should give up quickly rather than hold the queue.

Examples, run for real

4.1 ExamHall EXH-2730: splitting candidates.name with four pods serving

ExamHall's candidate API runs on four pods. Three plans split name into first_name and last_name; in each, every pod writes and reads candidates throughout, including while the release rolls out one pod at a time. MR !3030 is the third plan:

4.1 PostgreSQL 17.10 · 4 pods, replaced one at a time · every pod writes one candidate and reads every candidate after each step
4.1 one release (add both columns, copy, drop name, then roll out): failed requests 20 (42703) · reads missing a name 0
4.1 one release (RENAME COLUMN name TO full_name, then roll out): failed requests 20 (42703) · reads missing a name 0
4.1 MR !3030 (expand, 3 releases with a batched backfill, then drop name): failed requests 0 · reads missing a name 0

The one-release plans fail every old pod's requests until the rollout replaces it (step 7). MR !3030 never leaves a schema that a running version cannot use (steps 1–6); the drop comes only after its third release.

4.2 Payments ledger: building an index while entries arrive

A ledger builds an index on ledger_entries (account_id) while the API inserts entries and reads balances. The script holds each build in the middle of its table scan, then sends three inserts and three reads:

4.2 CREATE INDEX, held mid-scan: inserts done 0 of 3 · reads done 3 of 3 · index valid at the end true
4.2 CREATE INDEX CONCURRENTLY, held mid-scan: inserts done 3 of 3 · reads done 3 of 3 · index valid at the end true

The plain build lets reads through and stops writes until it ends (judgment-change-safety step 3); the concurrent build lets both continue, at the cost of a longer build (step 11).

Picture it

A new green steel arch footbridge is being built beside an old stone footbridge over a river, while people with bags keep walking across the old one.
An illustration to help it stick. The mechanism is in the sections above.

Try it

Answer first, then check. Nothing is saved.

Question 1 of 6

A payments ledger stores ledger_entries.amount numeric(12,2) in euros (12.50) and is moving to amount_cents bigint (1250). Its API runs on three pods and ships by rolling update. Put the plan in the order in which no request fails and no read finds an entry without an amount, during every rollout too.

Put the plan in the order in which no request fails and no read finds an entry without an amount, during every rollout too.

Your order

  1. 1empty
  2. 2empty
  3. 3empty
  4. 4empty
  5. 5empty
  6. 6empty

Tap the lines in the order you expect.

Question 2 of 6

A ticketing service's nightly migration runs, in one session:

SET lock_timeout = '2s';
ALTER TABLE seats ADD COLUMN hold_expires_at timestamptz;

Tonight a sales export opened a transaction, read seats, and will keep the transaction open for 40 minutes. While the ALTER waits, four seat lookups arrive, each SELECT … FROM seats WHERE id = $1.

What happens to the four lookups and to the ALTER?

Pick one

Question 3 of 6

The ledger's migration runner wraps every migration file in BEGIN … COMMIT. MR !3031 adds this file, and the deploy runs it while the API keeps inserting entries:

CREATE INDEX CONCURRENTLY ledger_entries_account_idx ON ledger_entries (account_id);

What happens?

Pick one

Question 4 of 6

A chat service is moving messages.body to messages.text. All of its pods run v7, which writes and reads only body. Its runbook:

  1. ALTER TABLE messages ADD COLUMN text text;
  2. Roll out v8, which writes body and text and reads body, one pod at a time; start the backfill below when the rollout starts.
  3. When the backfill has finished, roll out v9, which reads text and writes both.
  4. Roll out v10, which writes and reads only text.
  5. One release later: ALTER TABLE messages DROP COLUMN body;

The backfill:

Every pod keeps saving messages through each rollout. After step 3, v9 shows some messages with no text.

Where is the bug?

Code for this question
const { rows: [{ max }] } = await db.query('SELECT max(id) AS max FROM messages');for (let from = 1; from <= max; from += 1000) {  const to = Math.min(from + 999, max);  await db.query('UPDATE messages SET text = body WHERE id BETWEEN $1 AND $2 AND text IS NULL', [from, to]);}
Pick one

Question 5 of 6

A wallet ledger must refuse every entry with amount_cents <= 0: new entries, and every entry already stored (all of which pass today). The API inserts and reads entries all day. Each option builds on ALTER TABLE entries ADD CONSTRAINT amount_positive CHECK (amount_cents > 0).

Which one adds the rule so that inserts and reads go on while Postgres checks the stored entries?

Pick one

Question 6 of 6

A music service records each play once per play_id. A migration ran

CREATE UNIQUE INDEX CONCURRENTLY IF NOT EXISTS plays_play_id_key ON plays (play_id);

and failed: two play ids were stored twice. The team deleted the second copies and redeployed the same migration, which then succeeded. Since then, every INSERT INTO plays … ON CONFLICT (play_id) DO NOTHING from the new API fails.

Which change to the migration, run on the next deploy, makes those inserts work?

Pick one

Go deeper

Pro tips
  1. In Prisma ORM 8, keep a large table's backfill out of the expand migration. As of 2026-10-03 Prisma's expand-and-contract guide puts the backfill in the migration file, as "a dataTransform that runs in the same transaction as the schema change". PostgreSQL holds a lock "until the end of the transaction", so on a busy table the ALTER's ACCESS EXCLUSIVE lock would last the whole backfill. When: the expand step of a column change on a table with real traffic (steps 2 and 4). Verify: both passages, from the saved pages in sources/. Status: DOC (Prisma guide and PostgreSQL 13.3, fetched 2026-10-03); no Prisma project was run.
Source

PostgreSQL and Prisma behaviour can change: the facts below are as of 2026-10-03. No item is keyed on a source; every key is executed. Valid as of 2026-10-03.

  • PostgreSQL 17 documentation, 13.3 Explicit Locking: https://www.postgresql.org/docs/17/explicit-locking.html (pg-explicit-locking.txt). Quoted: ACCESS EXCLUSIVE "Conflicts with locks of all modes"; "Many forms of ALTER INDEX and ALTER TABLE also acquire a lock at this level"; "Only an ACCESS EXCLUSIVE lock blocks a SELECT (without FOR UPDATE/SHARE) statement"; "Once acquired, a lock is normally held until the end of the transaction." Supports steps 8–9 and tip 1.
  • PostgreSQL 17 documentation, 9.27 System Information Functions (pg_blocking_pids): https://www.postgresql.org/docs/17/functions-info.html (pg-functions-info.txt). Quoted: a process blocks another if it "is waiting for a lock that would conflict with the blocked process's lock request and is ahead of it in the wait queue (soft block)". Supports step 8 and the opening question's evidence.
  • PostgreSQL 17 documentation, 19.11 Client Connection Defaults (lock_timeout): https://www.postgresql.org/docs/17/runtime-config-client.html (pg-client-defaults.txt). Quoted: "Abort any statement that waits longer than the specified amount of time while attempting to acquire a lock"; "A value of zero (the default) disables the timeout"; "Setting lock_timeout in postgresql.conf is not recommended because it would affect all sessions." Supports step 10.
  • PostgreSQL 17 documentation, ALTER TABLE: https://www.postgresql.org/docs/17/sql-altertable.html (pg-alter-table.txt). Quoted: VALIDATE CONSTRAINT "acquires a SHARE UPDATE EXCLUSIVE lock"; "With NOT VALID, the ADD CONSTRAINT command does not scan the table and can be committed immediately"; for an added column with a non-volatile default or none, "In neither case is a rewrite of the table required"; RENAME has "no effect on the stored data". Supports steps 7, 9 and 13.
  • PostgreSQL 17 documentation, CREATE INDEX, Building Indexes Concurrently: https://www.postgresql.org/docs/17/sql-createindex.html (pg-create-index.txt). Quoted: "PostgreSQL supports building indexes without locking out writes"; a failed build leaves "an “invalid” index. This index will be ignored for querying purposes because it might be incomplete; however it will still consume update overhead"; "The recommended recovery method in such cases is to drop the index and try again"; for IF NOT EXISTS, "there is no guarantee that the existing index is anything like the one that would have been created". Supports steps 11–12.
  • Danilo Sato, martinfowler.com, ParallelChange (2014): https://martinfowler.com/bliki/ParallelChange.html (fowler-parallel-change.txt). Quoted: "Parallel change, also known as expand and contract, is a pattern to implement backward-incompatible changes to an interface in a safe manner, by breaking the change into three distinct phases: expand, migrate, and contract." Supports steps 2–6.
  • Prisma documentation, Expand-and-contract migrations (Prisma ORM 8): https://www.prisma.io/docs/guides/data-migration (prisma-expand-contract.txt). Quoted, as of 2026-10-03: "first add the new column and copy the data across (expand), then remove the old column once nothing reads it (contract)"; "a dataTransform that runs in the same transaction as the schema change"; "Wait until nothing reads or writes published before you continue." Supports steps 2–6 and tip 1.

Related lessons

Checked against the tools and docs as of 2026-10-03.