Zero-downtime ALTER TABLE on 50M+ row PostgreSQL tables
SEO Meta 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.
TL;DR
Running ALTER TABLE on a 50M+ row PostgreSQL table will block reads and writes — sometimes for minutes — unless you understand how WAL sequencing, lock escalation, and replication lag interact. The patterns below (concurrent index builds, ghost table swaps, pg_repack) are how you ship schema changes without a maintenance window.
The problem most teams discover too late
You have a 50-million-row orders table. Your product team needs a new column with a NOT NULL constraint and a default value. You run the migration in staging — it finishes in under a second. You deploy to production and your on-call engineer is getting paged at 2am.
What staging with a small dataset almost never tells you: how PostgreSQL behaves under load when it has to acquire locks against millions of rows.
How PostgreSQL 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 the actual heap files. This sequence number, the LSN (Log Sequence Number), determines the ordering of changes.
During an ALTER TABLE, PostgreSQL must:
- Acquire an AccessExclusiveLock on the table
- Write WAL records for every row that needs modification (for table rewrites)
- Flush those records before committing
Step 1 is where production systems break.
Lock escalation: the silent killer
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 |
The dangerous scenario isn’t the ALTER TABLE itself — it’s 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’ve accumulated hundreds of blocked connections.
Set lock_timeout and statement_timeout aggressively on migration connections, and retry with backoff rather than waiting indefinitely.
Replication lag during heavy DDL
If you’re 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 monitor pg_stat_replication.write_lag and replay_lag before and during any large migration.
The three patterns that actually work
1. CREATE INDEX CONCURRENTLY
Don’t use the non-concurrent form on large tables. It builds the index across three passes, each taking only brief ShareUpdateExclusiveLock windows rather than a full AccessExclusiveLock.
-- 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);One caveat: if the build fails due to a concurrent conflict, the index lands in an INVALID state. Always verify with SELECT indexname, indisvalid FROM pg_indexes JOIN pg_index ... before assuming success.
2. Ghost table swap with pg_repack
For column additions, type changes, or constraint modifications, the ghost table pattern is what I reach for first:
- Create a shadow table with the new schema
- Backfill in batches (managing WAL pressure deliberately)
- Dual-write new changes via trigger
- Perform an atomic RENAME when shadow table is synchronized
pg_repack automates this 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.
3. Expand/contract column migrations
Adding a nullable column in PostgreSQL 11+ is a metadata-only operation — instant, regardless of table size. Split your migration across deploys:
-- Deploy 1: add nullable (zero-lock, instant)
ALTER TABLE orders ADD COLUMN metadata jsonb;
-- Background job: backfill in batches
UPDATE orders SET metadata = '{}' WHERE id BETWEEN $1 AND $2 AND metadata IS NULL;
-- 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.
What I’d tell a team starting fresh
Set lock_timeout to 2–3 seconds with retry loops on every migration connection. A migration waiting indefinitely will cascade into full connection exhaustion — not a hypothetical, it happens reliably under production load.
Make non-concurrent index creation an explicit, documented exception requiring a scheduled window. If CONCURRENTLY is the default, teams stop forgetting.
And instrument replication lag before every large migration. 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’ve been burned by it.
The teams that ship schema migrations without incidents aren’t the ones with the best runbooks. They’re the ones who internalized how PostgreSQL’s locking model and WAL pipeline actually interact under load.
Tags: backend, architecture, devops, microservices, api