IBRAVRA Media Network
Website & Hosting • 12 min read

Zero-Downtime Postgres Migrations: The Blue-Green Playbook — Expand, Contract, and Roll Back Without Locking the Table

A Postgres migration does not fail when the DDL is wrong; it fails when the DDL takes a lock the application cannot wait for. This playbook runs the operation on PostgreSQL 18 documentation: expand-and-contract in five phases, lock-free DDL with exact lock modes, backfill batching counted to the batch, blue-green cutover with rollback math, and the PgBouncer pool sizing that decides whether the cutover connects at all.

By Enow A. Jovial • Published 2026-11-16

The migration that takes your site down is rarely the migration with the wrong SQL. It is the migration with the right SQL run at the wrong lock level, at the wrong hour, with no timeout and no rollback. PostgreSQL documents it plainly: ALTER TABLE takes an ACCESS EXCLUSIVE lock unless a subform notes otherwise, and that lock is the only one that blocks a plain SELECT. Figures and lock modes as documented in PostgreSQL 18.

Zero downtime is a lock audit, not a speed contest. Expand-and-contract in five phases, backfill in counted batches, build indexes with CREATE INDEX CONCURRENTLY, set lock_timeout to 2 seconds and statement_timeout to 30 seconds on the migration session, and size the PgBouncer pool before the cutover — 4 application servers at the default pool size of 20 need 80 of the 97 usable connections, and the fifth server gets refused.

The lock table is the whole problem

PostgreSQL 18 documents eight table-level lock modes, and the only question that matters is which modes conflict with which. SELECT takes ACCESS SHARE, which conflicts only with ACCESS EXCLUSIVE. UPDATE, DELETE, and INSERT take ROW EXCLUSIVE, which conflicts with SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE — but not with SHARE UPDATE EXCLUSIVE. That single non-conflict is the whole zero-downtime technique.

PostgreSQL 18 documentation, section 13.3 Explicit Locking: the table-level lock modes, showing ACCESS SHARE conflicting only with ACCESS EXCLUSIVE, and the commands that acquire each mode
PostgreSQL 18 documentation, section 13.3 Explicit Locking: the table-level lock modes, showing ACCESS SHARE conflicting only with ACCESS EXCLUSIVE, and the commands that acquire each mode
Lock modeConflicts withAcquired byBlocks reads?Blocks writes?Authority
ACCESS SHAREACCESS EXCLUSIVE onlySELECTNoNoPG 18, 13.3
ROW SHAREEXCLUSIVE, ACCESS EXCLUSIVESELECT FOR UPDATENoPartiallyPG 18, 13.3
ROW EXCLUSIVESHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVEUPDATE, DELETE, INSERT, MERGENoNoPG 18, 13.3
SHARE UPDATE EXCLUSIVEAll except ACCESS SHARE, ROW SHARE, ROW EXCLUSIVEVACUUM, ANALYZE, CREATE INDEX CONCURRENTLYNoNoPG 18, 13.3
SHAREAll except ACCESS SHARE, ROW SHARECREATE INDEX (without CONCURRENTLY)NoYesPG 18, 13.3
SHARE ROW EXCLUSIVEAll except ACCESS SHARE, ROW SHARECREATE TRIGGERNoYesPG 18, 13.3
EXCLUSIVEAll except ACCESS SHAREREFRESH MATERIALIZED VIEW CONCURRENTLYNoYesPG 18, 13.3
ACCESS EXCLUSIVEAll modesDROP TABLE, TRUNCATE, REINDEX, most ALTER TABLEYesYesPG 18, 13.3

The timeouts that are all zero by default

PostgreSQL 18 ships with statement_timeout, lock_timeout, and idle_in_transaction_session_timeout all defaulting to zero, and zero means disabled. A fresh database has no protection against a migration that waits forever for a lock, a backfill that runs for six hours, or a session that holds locks while idle in a transaction. The documentation warns against setting these in postgresql.conf, where they would affect all sessions.

SettingDefaultWhat it abortsMigration valueAuthority
statement_timeout0 (disabled)Any statement over the limit30 secondsPG 18, 19.11
lock_timeout0 (disabled)Any statement waiting for a lock over the limit2 secondsPG 18, 19.11
idle_in_transaction_session_timeout0 (disabled)A session idle inside a transaction60 secondsPG 18, 19.11

The ordering between the first two is the arithmetic that matters. A statement that waits 2 seconds for its lock aborts on lock_timeout while statement_timeout would have allowed it 30 seconds — the lock timeout fires first by design:

lock_timeout=2,000 ms<statement_timeout=30,000 ms⇒lock wait aborts at 2 s, never queues the app for 30 s\text{lock\_timeout} = 2{,}000\text{ ms} < \text{statement\_timeout} = 30{,}000\text{ ms} \Rightarrow \text{lock wait aborts at 2 s, never queues the app for 30 s}

Set them at the top of the migration session:

SET lock_timeout = '2s';
SET statement_timeout = '30s';
SET idle_in_transaction_session_timeout = '60s';

Deadlock detection needs no tuning: deadlock_timeout defaults to 1 second, and PostgreSQL aborts one of the transactions involved. The documentation is candid that which transaction is aborted is difficult to predict — so the playbook treats a deadlock abort as a retry signal, and the CI/CD pipeline must retry on the deadlock error code. The best defence is acquiring locks in a consistent order, which means running DDL against tables in the same order in every deploy.

Expand and contract: the only pattern that never locks

Expand-and-contract is five phases, ordered by what the old code can tolerate. Phase 1, expand: add the new column as nullable, or add the new table, with no constraint the old code can violate. The documentation makes one form of this free: adding a column with a constant default value does not require each row to be updated when the ALTER TABLE runs — the default is returned when the row is next accessed. The trap is the volatile default: a default of clock_timestamp() forces a per-row update at ALTER TABLE time. Phase 2, dual-write: deploy code that writes both shapes. Phase 3, backfill: copy old values into the new shape in batches. Phase 4, contract-read: deploy code that reads the new shape. Phase 5, contract-write: drop the old column or table, after a full deploy cycle in which no old code is running anywhere.

The phase most often skipped is phase 2, and skipping it is the most common expand-and-contract failure. Without dual-write, a write that lands between the backfill and the read switch writes only the old shape, and the new shape goes stale the moment the switch flips. The backfill must also be re-runnable and idempotent — a batch that fails halfway must be safe to run again, because a backfill that aborts on a lock timeout will be run again. Our PostgreSQL connection pooling playbook covers the pooler that sits between the application and the DB during all five phases, and the high-traffic infrastructure comparison prices the application servers whose connection counts the pool math depends on.

Renames deserve their own warning because they look harmless and are not. ALTER TABLE RENAME COLUMN and RENAME TO take ACCESS EXCLUSIVE, and a rename breaks every prepared statement, view, and function that names the old identifier. The expand-and-contract answer to a rename is to not rename: add the new column, dual-write, backfill, switch reads, drop the old column.

Backfill in batches, counted to the batch

A backfill is a loop with exactly two numbers: the batch size and the batch count:

batches=rowsbatch size;  10,000,0001,000=10,000 batches;  10,000,00010,000=1,000 batches\text{batches} = \frac{\text{rows}}{\text{batch size}}; \; \frac{10{,}000{,}000}{1{,}000} = 10{,}000 \text{ batches}; \; \frac{10{,}000{,}000}{10{,}000} = 1{,}000 \text{ batches}

Ten million rows at 1,000 rows per batch is 10,000 batches; at 10,000 rows per batch it is 1,000 batches. The total time is the batch count times the per-batch time, measured in staging — no throughput number is printed here, because none was measured.

Keyset pagination is the batching method the loop uses, because OFFSET degrades as the offset grows. The loop orders by the primary key and takes the next batch after the last key it processed — the DB reads the rows by index, so each batch costs the same CPU and RAM:

UPDATE orders
SET total_cents = (subtotal_cents + tax_cents) * 100
WHERE id > $1
ORDER BY id
LIMIT 1000;

The loop needs a progress record: the last processed key, written to a migration state table after each batch.

Indexes without the write lock

CREATE INDEX CONCURRENTLY is the only index build that does not lock out writes. The documentation states it directly: the option builds the index without taking any locks that prevent concurrent inserts, updates, or deletes. The price is time: a concurrent build performs two scans of the table, waits for existing transactions to terminate before each scan, and cannot run inside a transaction block.

The failure mode is specific and must be handled, not hoped away. If a deadlock or a uniqueness violation interrupts the build, the command fails but leaves behind an INVALID index: ignored for queries because it might be incomplete, but still consuming update overhead on every write to the table. The documented recovery is to drop the index and run CREATE INDEX CONCURRENTLY again, or rebuild it with REINDEX INDEX CONCURRENTLY. A second caveat is sharper: a concurrent unique index starts enforcing uniqueness when the second table scan begins, so other transactions can report constraint violations before the index is usable — and if the build then fails, the invalid index keeps enforcing uniqueness afterwards. Check pg_index after every concurrent build:

SELECT indexrelid::regclass AS index, indisvalid
FROM pg_index
WHERE NOT indisvalid;

Adding a constraint follows the same two-step shape. ADD CONSTRAINT with NOT VALID adds the constraint without scanning the table, and VALIDATE CONSTRAINT then scans under SHARE UPDATE EXCLUSIVE — which blocks neither reads nor writes.

The two statements that look safe and are not: ALTER TABLE ADD COLUMN with a volatile default, which rewrites every row at ALTER TABLE time, and ALTER TABLE SET DATA TYPE, which takes ACCESS EXCLUSIVE and rewrites the table. Both read as trivial changes, and both block the whole table for the duration of the rewrite.

Blue-green cutover and the rollback math

Blue-green is two databases, not two deploys. Blue serves production; green is the copy with the migration applied. The cutover is a sequence with a measured window: stop writes to blue, drain the pool, promote green, point the application at green, resume writes. The DNS record is the pointer that flips, and the HTTP health check follows it. The rollback is the same sequence in reverse:

rows to reconcile=write rate (rows/s)×cutover window (s);  rows written to green after cutover cannot be replayed to blue by reversing the pointer\text{rows to reconcile} = \text{write rate (rows/s)} \times \text{cutover window (s)}; \; \text{rows written to green after cutover cannot be replayed to blue by reversing the pointer}

That second clause is the part teams miss. Rolling back the pointer does not roll back the data: every row written to green after the cutover exists only on green, and a pointer rollback orphans it. The rollback plan must therefore name one of three answers before the cutover starts: accept the loss of the window's writes — the JSON payloads the REST API received in the window — replay them from the application log or logical replication, or keep the window so short that the loss is acceptable and named in advance.

The drain is where PgBouncer earns its place in this guide. PgBouncer's pool_mode defaults to session, which releases a server connection back to the pool only when the client disconnects — a long-lived application connection never releases. Whether the DB runs on a VPS, in Docker, or on AWS, the pool_mode setting is the same. Transaction mode releases the server connection when the transaction finishes, which is the mode a blue-green cutover wants: connections return to the pool between transactions, and the drain completes in seconds rather than at the next deploy. Set pool_mode to transaction on the green pooler before the first connection opens — a pooler in session mode during a cutover is a drain that never drains.

PgBouncer configuration documentation: pool_mode with session, transaction, and statement modes, and max_client_conn, the settings that decide whether a blue-green cutover can drain and reconnect
PgBouncer configuration documentation: pool_mode with session, transaction, and statement modes, and max_client_conn, the settings that decide whether a blue-green cutover can drain and reconnect

The pooler during cutover: PgBouncer math

The pool math uses documented defaults only. PostgreSQL's max_connections is typically 100, and superuser_reserved_connections defaults to 3, so the application has 97 usable connections. PgBouncer's default_pool_size is 20 server connections per user and database pair, and it listens on TCP port 6432 by default:

server connections=app servers×default_pool_size;  4×20=80≤97;  5×20=100>97\text{server connections} = \text{app servers} \times \text{default\_pool\_size}; \; 4 \times 20 = 80 \leq 97; \; 5 \times 20 = 100 > 97

Four application servers at the default pool size need 80 server connections, inside the 97 usable. Five servers need 100, which is 3 over the usable ceiling. The green environment must therefore not open its pool while blue still holds one, or the pool size must be lowered: 5 servers at 19 connections each need 95. PgBouncer's reserve_pool_size defaults to 0 (disabled) and its reserve_pool_timeout defaults to 5.0 seconds — a reserve pool is available, but it is off until configured. Run the whole stack through the website cost calculator with the pool sizes as line items before the cutover window is booked.

Founder & CEO Insight — Enow A. Jovial

"A migration is a negotiation with every query running against the table at that moment. The teams that take their sites down are not the teams that write bad SQL — they write the same ALTER TABLE everyone writes. They take their sites down because they ran it without a timeout, without counting the pool, and without a rollback for the window's writes."
— Enow A. Jovial, Founder & Chief Executive Officer

Who should not do this

A database with no traffic outside business hours should take the maintenance window and run the plain ALTER TABLE. A table under 100,000 rows should not be backfilled in batches — the whole backfill is one short statement.

The execution checklist

  • [x] Step 1: Audit every DDL statement against the lock table — anything that takes ACCESS EXCLUSIVE is rewritten or split.
  • [x] Step 2: Open the migration session with SET lock_timeout = '2s' and SET statement_timeout = '30s'.
  • [x] Step 3: Expand first — add the new column nullable, with a constant default if one is needed and never a volatile one.
  • [x] Step 4: Deploy dual-write code, then backfill in keyset batches of 1,000 or 10,000 rows.
  • [x] Step 5: Build indexes with CREATE INDEX CONCURRENTLY only, never inside a transaction block, and check pg_index for invalid indexes after every build.
  • [x] Step 6: Add constraints as NOT VALID, then VALIDATE CONSTRAINT, then SET NOT NULL — the validated CHECK constraint lets the NOT NULL step skip its table scan.
  • [x] Step 7: Count the pool before the cutover — app servers times default_pool_size must stay under 97 usable connections — and name the rollback answer for the window's writes.

A zero-downtime migration is five disciplines: the lock audit, the timeouts, the phases, the counted backfill, and the pool math.