---
title: "Zero-Downtime ALTER TABLE on 50M+ Row PostgreSQL Tables"
published: true
description: "How WAL sequencing and lock escalation affect ALTER TABLE on large PostgreSQL tables — and the exact migration patterns that eliminate maintenance windows at scale."
tags: postgresql, architecture, devops, performance
canonical_url: https://blog.mvpfactory.co/zero-downtime-alter-table-postgresql
---
## What We Will Build
By the end of this workshop, you will understand exactly why a migration that finishes in under a second on staging can page your on-call engineer at 2am on production. More importantly, you will walk away with three concrete patterns — concurrent index builds, ghost table swaps, and expand/contract migrations — that let you ship schema changes on 50M+ row tables without a maintenance window.
## Prerequisites
- PostgreSQL 11+ (some patterns are version-specific)
- A table with significant row counts to reason about
- Basic familiarity with SQL DDL and `EXPLAIN`
- Access to `pg_stat_replication` if you run streaming replication
---
## Step 1: Understand How WAL Sequencing Works
The Write-Ahead Log (WAL) is PostgreSQL's durability mechanism. Every modification — INSERT, UPDATE, DELETE, and DDL — is first written as a WAL record before it touches actual heap files. This sequence number, the LSN (Log Sequence Number), determines the ordering of changes.
During an `ALTER TABLE`, PostgreSQL must:
1. Acquire an **AccessExclusiveLock** on the table
2. Write WAL records for every row that needs modification (for table rewrites)
3. Flush those records before committing
Step 1 is where production systems break.
---
## Step 2: Understand Lock Escalation
PostgreSQL's lock hierarchy has 8 levels. An ordinary `SELECT` takes an `AccessShareLock`. An `ALTER TABLE` takes the heaviest: `AccessExclusiveLock` — which conflicts with every other lock mode.
| Operation | Lock Mode | Conflicts with ALTER TABLE? |
|---|---|---|
| SELECT | AccessShareLock | Yes |
| INSERT / UPDATE / DELETE | RowExclusiveLock | Yes |
| CREATE INDEX CONCURRENTLY | ShareUpdateExclusiveLock | No |
| VACUUM | ShareUpdateExclusiveLock | No |
| CLUSTER / REINDEX | AccessExclusiveLock | Yes |
Here is the gotcha that will save you hours: the dangerous scenario is not the `ALTER TABLE` itself — it is the **lock queue**. A single long-running transaction blocks your `ALTER TABLE` from acquiring its lock. Everything that arrives after the request then queues behind it. Within seconds, you have accumulated hundreds of blocked connections.
Set `lock_timeout` and `statement_timeout` aggressively on migration connections, and retry with backoff rather than waiting indefinitely.
---
## Step 3: Monitor Replication Lag Before You Migrate
If you are running streaming replication, WAL-heavy schema changes create a secondary problem. A table rewrite on a 50M-row table generates significant WAL volume. Your standbys will fall behind.
With synchronous replication, this directly stalls your primary. With async replication, standbys temporarily serve stale reads — and your connection pooler's read routing will direct live traffic there without knowing it.
Always check this before any large migration:
sql
SELECT client_addr, write_lag, replay_lag
FROM pg_stat_replication;
If standbys are already behind, defer. WAL backlog on a streaming replica compounds under additional write pressure in ways that are hard to predict until you have been burned by it.
---
## Step 4: Apply the Three Patterns That Actually Work
### Pattern 1 — CREATE INDEX CONCURRENTLY
Let me show you a pattern I use in every project. Never use the non-concurrent form on a large table. The concurrent build takes only brief `ShareUpdateExclusiveLock` windows across three passes rather than a full `AccessExclusiveLock`.
sql
-- Never do this on a large production table:
CREATE INDEX idx_orders_user_id ON orders(user_id);
-- Always use this instead:
CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders(user_id);
The docs do not mention this prominently, but if the build fails due to a concurrent conflict, the index lands in an `INVALID` state. Always verify before assuming success:
sql
SELECT indexname, indisvalid
FROM pg_indexes
JOIN pg_index ON indexrelid = (SELECT oid FROM pg_class WHERE relname = 'orders')
WHERE indexname = 'idx_orders_user_id';
### Pattern 2 — Ghost Table Swap with pg_repack
For column additions, type changes, or constraint modifications, reach for the ghost table pattern:
1. Create a shadow table with the new schema
2. Backfill in batches (managing WAL pressure deliberately)
3. Dual-write new changes via trigger
4. Perform an atomic `RENAME` when the shadow table is synchronized
**[pg_repack](https://github.com/reorg/pg_repack)** automates this entire workflow. The final `AccessExclusiveLock` for the rename completes in milliseconds once the shadow table is current — far below the threshold that causes cascading connection failures.
### Pattern 3 — Expand/Contract Column Migrations
Here is the minimal setup to get this working safely. Adding a nullable column in PostgreSQL 11+ is a metadata-only operation — instant, regardless of table size. Split your migration across deploys:
sql
-- Deploy 1: add nullable (zero-lock, instant)
ALTER TABLE orders ADD COLUMN metadata jsonb;
sql
-- Background job: backfill in batches
UPDATE orders
SET metadata = '{}'
WHERE id BETWEEN $1 AND $2
AND metadata IS NULL;
sql
-- Deploy 2: enforce constraint once backfill confirms 100% coverage
ALTER TABLE orders ALTER COLUMN metadata SET DEFAULT '{}';
ALTER TABLE orders ALTER COLUMN metadata SET NOT NULL;
Each individual step is safe. The only risk is attempting all three in a single transaction at scale.
---
## Gotchas
**Staging lies to you.** Small datasets complete in milliseconds. The lock queue behavior only emerges under real concurrent load against millions of rows. Never trust staging migration timing.
**Lock queues cascade fast.** One blocked `ALTER TABLE` waiting on a long-running transaction will accumulate hundreds of queued connections in seconds. Always set `lock_timeout` to 2–3 seconds with retry loops on migration connections.
**INVALID indexes are silent.** A `CREATE INDEX CONCURRENTLY` that fails does not error loudly — it leaves behind an invalid index that the query planner ignores. Always query `pg_index.indisvalid` post-migration.
**Replication lag compounds.** Running a heavy migration while your standbys are already lagging will make things significantly worse. Monitor before you migrate, not after.
**Non-concurrent index creation needs a documented exception process.** If `CONCURRENTLY` is your enforced default, teams stop forgetting it.
---
## Conclusion
The teams that ship schema migrations without incidents are not the ones with the best runbooks. They are the ones who internalized how PostgreSQL's locking model and WAL pipeline actually interact under load.
Concrete starting points: set `lock_timeout` to 2–3 seconds with retry on every migration connection, make `CREATE INDEX CONCURRENTLY` your non-negotiable default, and check `pg_stat_replication` before every large migration.
**Further reading:**
- [PostgreSQL Lock Documentation](https://www.postgresql.org/docs/current/explicit-locking.html)
- [pg_repack GitHub](https://github.com/reorg/pg_repack)
- [PostgreSQL WAL Internals](https://www.postgresql.org/docs/current/wal-intro.html)
Top comments (0)