PostgreSQL Write-Ahead Log Internals for Zero-Downtime Schema Migrations

---
title: "Zero-Downtime PostgreSQL Schema Migrations: WAL, Locks, and the Patterns That Don't Stall Production"
published: true
description: "Learn how PostgreSQL WAL and MVCC interact during DDL. Understand ACCESS EXCLUSIVE lock escalation and the migration patterns that prevent production incidents."
tags: postgresql, architecture, devops, performance
canonical_url: https://blog.mvpfactory.co/zero-downtime-postgresql-schema-migrations
---

What You Will Learn

By the end of this workshop, you will understand exactly why naive ALTER TABLE commands cause production incidents, how PostgreSQL's WAL and MVCC interact during DDL, and the exact patterns you need to run schema changes on live tables without stalling reads or writes.

This is not theoretical. I have seen a 10ms migration cause a 45-second incident. Here is how to never let that happen to you.

---

Prerequisites

• PostgreSQL 11+ (some behavior differs on earlier versions — I will call it out)
• Basic familiarity with SQL DDL
• A production table you care about

---

The Problem Most Teams Get Wrong

Here is the assumption that causes incidents:

"MVCC protects us from DDL contention."

It does not. MVCC protects you from concurrent data modifications. It does nothing for schema changes.

When you run this on a live, busy table:

ALTER TABLE orders ADD COLUMN metadata JSONB DEFAULT '{}';

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 is not the lock itself. It is the queue. Here is the exact sequence:

1. PostgreSQL requests ACCESS EXCLUSIVE on orders
2. An existing SELECT holds ACCESS SHARE — the DDL request queues behind it
3. Every query arriving after it queues 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. Your connection pool exhausted before the lock even released.

---

How WAL and MVCC Actually 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 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 UPDATE and INSERT.

But DDL modifies the system catalog, not row versions. Catalog changes must be serialized — there is no safe "in-between" version of a table schema. ACCESS EXCLUSIVE is the enforcement mechanism. No way around it; only ways to minimize the window.

---

Step-by-Step: Migration Patterns That Don't Block

Step 1 — Always Set lock_timeout First

Let me show you a pattern I use in every project:

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. Set this before every DDL statement on a live system — no exceptions.

Step 2 — Safe Column Addition (Pre-PG 11 Compatible)

-- 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
-- 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 '{}';

The docs do not always make this clear, but 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.

Step 3 — Index Creation: No Exceptions

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

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

CREATE INDEX CONCURRENTLY uses ShareUpdateExclusiveLock, which is why it does not stall reads or writes. The lock conflict matrix makes this clear: ACCESS EXCLUSIVE blocks everything; SHARE UPDATE EXCLUSIVE blocks almost nothing in normal operation.

Step 4 — Structural Changes on Large Tables

For column type changes or constraint additions on large tables, pg_repack rebuilds the table as an online copy, then performs an atomic swap with a brief lock at the end. On a 500GB table, 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: apply the change on a replica, redirect traffic, promote. The ACCESS EXCLUSIVE window shrinks to the cutover itself.

---

Gotchas

The queue is invisible until it is too late. Your monitoring will not show the DDL as slow — it is waiting, not running. Watch pg_stat_activity for state = 'idle in transaction' and wait_event_type = 'Lock'.

Batching with BETWEEN and hardcoded ranges breaks on tables with gaps. Use keyset pagination (id > $last_id) as shown above. It advances through actual existing rows safely.

pg_repack is not zero-configuration. It needs roughly the same free disk space as the table it rebuilds. Underestimating this has caused more than one well-planned migration to fail mid-run.

lock_timeout does not retry for you. Set it, catch the error in your migration tooling, and retry at a quieter moment. This is recoverable. Queuing is not.

---

Conclusion

Three rules to carry forward:

1. SET lock_timeout before every DDL statement on a live system
2. CREATE INDEX CONCURRENTLY without exception
3. For structural changes on large tables, evaluate pg_repack or a logical replication swap

Here is the minimal setup to get this working in any migration tool: run each DDL statement in its own transaction, set lock_timeout at the session level, and never let a column backfill happen inside the same transaction as the ALTER TABLE.

Between long migration runs, this is genuinely a good moment to step away from the screen. I use HealthyDesk for guided desk stretches — waiting on pg_repack to rebuild a 400GB table is exactly the right time for a break reminder.

For further reading: the PostgreSQL explicit locking documentation has the full 8×8 lock conflict matrix. It is worth bookmarking before your next schema migration.