Zero-downtime PostgreSQL schema migrations: WAL & locks

Meta: Learn how PostgreSQL WAL and MVCC interact during DDL. Understand ACCESS EXCLUSIVE lock escalation and the migration patterns that prevent production stalls. (157 chars)

---

TL;DR

Naive ALTER TABLE commands acquire ACCESS EXCLUSIVE locks that block every read and write. PostgreSQL’s MVCC protects you from concurrent data modifications — not from DDL. The fix isn’t clever timing: it’s using the right migration patterns, CREATE INDEX CONCURRENTLY, and tools like pg_repack.

---

The problem: why ALTER TABLE stalls production

Most teams assume MVCC protects them from DDL. It doesn’t.

When you run ALTER TABLE orders ADD COLUMN processed_at TIMESTAMPTZ, PostgreSQL acquires an ACCESS EXCLUSIVE lock — the strongest in its eight-tier hierarchy. It conflicts with everything, including the ACCESS SHARE locks held by ordinary SELECT queries.

The dangerous part isn’t the lock itself. It’s the queue.

If a long-running transaction is already reading your table, your ALTER TABLE waits. Every query arriving after it queues behind your migration. By the time the migration completes, hundreds of requests are piling up. Your database releases the lock in milliseconds — but your connection pool is already exhausted.

---

How WAL and MVCC interact during DDL

PostgreSQL’s Write-Ahead Log records every change — data modifications, index updates, DDL operations — before applying them to data files. This guarantees durability and powers point-in-time recovery.

MVCC, layered on top of WAL, gives each transaction a consistent snapshot. Readers see the row version that existed when their transaction started. Writers create new versions rather than modifying in place. This works beautifully for DML — your UPDATE and INSERT operations coexist peacefully.

But DDL is different. Schema changes modify the system catalog, not row versions. Catalog changes must be serialized. There is no safe “in-between” version of a table schema. PostgreSQL enforces this with catalog-level locks, and ACCESS EXCLUSIVE is the mechanism.

---

The lock escalation sequence

The exact sequence that determines whether your migration blocks for milliseconds or minutes:

-- What happens when you run this on a live, busy table:
ALTER TABLE orders ADD COLUMN metadata JSONB DEFAULT '{}';

1. PostgreSQL requests ACCESS EXCLUSIVE on orders
2. An existing SELECT holds ACCESS SHARE — the DDL request queues
3. All subsequent queries queue behind the waiting DDL
4. The SELECT finishes; DDL acquires the lock, runs in ~10ms
5. Lock releases — 200 queued queries hit simultaneously

The migration took 10ms. The incident lasted 45 seconds.

PostgreSQL lock conflict matrix

The table below covers the most operationally critical lock modes. For the full 8×8 compatibility matrix, see the PostgreSQL explicit locking documentation.

Requested →	ACCESS SHARE	ROW SHARE	ROW EXCLUSIVE	SHARE UPDATE EXCL	SHARE ROW EXCL	ACCESS EXCLUSIVE
ACCESS SHARE	—	—	—	—	—	BLOCKS
ROW SHARE	—	—	—	—	BLOCKS	BLOCKS
ROW EXCLUSIVE	—	—	—	—	BLOCKS	BLOCKS
SHARE UPDATE EXCL	—	—	—	BLOCKS	BLOCKS	BLOCKS
SHARE ROW EXCL	—	—	BLOCKS	BLOCKS	BLOCKS	BLOCKS
ACCESS EXCLUSIVE	BLOCKS	BLOCKS	BLOCKS	BLOCKS	BLOCKS	BLOCKS

CREATE INDEX CONCURRENTLY uses ShareUpdateExclusiveLock — which is why it doesn’t stall reads or writes.

---

Migration patterns that don’t block

Safe column addition (pre-PG 11 compatibility)

-- Step 1: Add nullable column (brief catalog lock, no table rewrite)
ALTER TABLE orders ADD COLUMN metadata JSONB;

-- Step 2: Backfill in batches using keyset pagination.
-- Safe for tables with gaps or non-sequential PKs — unlike BETWEEN with
-- hardcoded ranges, this advances through actual existing rows.
-- Run from your migration tooling, advancing $last_id each iteration:
--
--   last_id = 0
--   WHILE rows_affected > 0:
--     UPDATE orders
--       SET metadata = '{}'
--     WHERE id > $last_id
--       AND id <= $last_id + 10000
--       AND metadata IS NULL;
--     last_id += 10000
--     sleep(100ms)  -- throttle between batches

-- Step 3: Set default for future rows (brief lock)
ALTER TABLE orders ALTER COLUMN metadata SET DEFAULT '{}';

In PostgreSQL 11+, adding a column with a non-volatile default no longer rewrites the table — the default is stored in pg_attribute and materialized on read. This eliminates one of the most common migration incidents entirely.

Always set lock_timeout

SET lock_timeout = '2s';
ALTER TABLE orders ADD COLUMN processed_at TIMESTAMPTZ;

Fail fast, never queue. A migration that times out is recoverable; a migration that queues is an incident.

Index creation — no exceptions

-- Never on production:
CREATE INDEX ON orders (customer_id);

-- Always:
CREATE INDEX CONCURRENTLY ON orders (customer_id);

---

pg_repack and logical replication for structural changes

For large tables requiring column type changes or constraint additions, pg_repack rebuilds the table as an online copy, then performs an atomic swap with a brief lock at the end. The numbers matter: on a 500GB table, a naive ALTER COLUMN TYPE can hold ACCESS EXCLUSIVE for 20+ minutes. pg_repack reduces that final lock window to under a second — but plan for the disk headroom and job duration upfront.

For the highest-stakes migrations, teams use logical replication: apply the migration on a replica, redirect traffic, promote. The ACCESS EXCLUSIVE window shrinks to the cutover itself.

---

What to take away

1. Set lock_timeout. A timed-out DDL statement surfaces immediately and can be retried at a quieter moment. A queuing DDL statement silently holds the gate while connections accumulate behind it, exhausting your pool before the lock even releases. Fail-fast is recoverable; queue buildup is an incident.

2. Use CREATE INDEX CONCURRENTLY without exception. There is no valid reason to take a full table lock for index creation on a live system. ShareUpdateExclusiveLock exists precisely for this.

3. For structural changes on large tables, evaluate pg_repack or a logical replication swap. The goal is accepting only the brief final lock window — not the full duration of the rebuild. On tables above a few hundred gigabytes, the difference between these approaches and naive DDL is measured in minutes versus milliseconds.

---

Tags: backend, architecture, devops