If you’ve run PostgreSQL in production long enough, you’ve either caused this incident or watched someone else cause it: a Friday afternoon schema change, a table with tens of millions of rows, and a ALTER TABLE statement that looked completely harmless in a staging environment with 200 rows.
Then production connections start queuing. Then the connection pool exhausts. Then the on-call pager goes off.
The Mechanism, Precisely
PostgreSQL’s ALTER TABLE takes an ACCESS EXCLUSIVE lock for the duration of certain operations — the strictest lock level in the system, blocking every other transaction, reads included, until it releases. Two of the most common ways to trigger a long-held version of this lock:
ADD COLUMN ... DEFAULT — if the default isn’t a constant PostgreSQL can store once in the catalog, it has to be computed and written into every existing row, under that lock, before the statement returns. On a 50-million-row table, that’s not a metadata change — it’s a full table rewrite with the table unavailable the entire time.
An incompatible ALTER COLUMN ... TYPE — changing a column’s type where PostgreSQL can’t prove the old values are already valid under the new type (most type changes beyond a handful of specific, compatible pairs) forces the same full rewrite, same lock.
Neither of these is a PostgreSQL bug. Both are PostgreSQL correctly doing what you asked — rewrite every row, hold the lock so nothing reads a half-rewritten table. The problem isn’t the database; it’s asking it to do that rewrite inline, synchronously, while production traffic is still trying to use the table.
What People Actually Do About It
In practice, teams converge on a handful of real patterns, roughly in order of how much custom engineering they require:
Just schedule a maintenance window. Works, until your business doesn’t have one anymore, or the table’s grown past the point a window of any reasonable length covers it.
Hand-roll the expand/backfill pattern yourself: add a new nullable column, backfill it in batches from application code or a script, dual-write old and new during the transition, then swap. This genuinely works, and is the right instinct — it’s also a real amount of custom code to write correctly (batch sizing, resuming after a failure, not fighting your own autovacuum) for every migration you need it for.
Logical-replication-based tooling for the cases expand/backfill can’t cover cheaply — an incompatible type change, or restructuring into partitions — building a parallel copy of the table and keeping it in sync via PostgreSQL’s own native logical replication until a near-instant cutover.
pg_repack, for the specific case of reclaiming bloat/dead tuples without a long lock — a different, narrower problem than a schema change, but often reached for alongside these for table maintenance.
Where This Project Fits
I ended up building pgArchiMigrator specifically to turn pattern #2 and #3 above into something you don’t re-implement per migration. It looks at the operation you’re asking for and picks automatically between three strategies:
Direct DDL— when the change genuinely is metadata-only (most ADD COLUMN calls with a constant default, most index creation via CREATE INDEX CONCURRENTLY), just run the fast path. No point building infrastructure for a problem that isn’t there.
Expand & Backfill — pattern #2 above, implemented once, correctly: new column added alongside the old, backfilled in batches, dual-write during the transition, swapped in.
Shadow Table — pattern #3: a full copy kept in sync via PostgreSQL’s own logical replication, for an incompatible ALTER COLUMN TYPE or a PARTITION_TABLE restructure (which always uses this strategy, regardless of table size — there’s no cheaper way to turn an existing table into a partitioned one in place).
The strategy selection itself lives in one place in the codebase (internal/strategy) as an actual decision table, not something scattered across call sites.
Does It Actually Work? (Measured, Not Asserted)
The repo has a load-testing tool built in (cmd/loadtest) that drives real concurrent application traffic against a real table while a real migration runs, and reports p50/p95/p99 query latency before, during, and after. The number is re-measured on every CI run, not written once and left to rot in a README:
ADD_COLUMN with a volatile default, 5,000,000 rows, forcing
EXPAND_BACKFILL (not the cheap metadata-only path):
BEFORE/AFTER (baseline): p50=3ms p95=3ms p99=4ms
DURING migration: p50=4ms p95=5ms p99=6ms
p99 during the migration was 1.5x the baseline p99.
That’s the actual number this specific migration type produces on a shared GitHub Actions runner — not a tuned benchmark machine. Your own hardware will very likely do better.
Try It Without Touching Your Own Database
git clone https://github.com/pgarchihub/pgarchimigrator.git
cd pgarchimigrator/playground
docker compose up -d --wait
This spins up a real pre-seeded 5-million-row table and lets you watch a real zero-downtime ALTER COLUMN TYPE (id: integer → bigint — the single most common real-world reason teams reach for this: running out of int32 room on a primary key) run against it. No manual setup, no signup.
Apache 2.0, source on GitHub: http://www.github.com/pgarchihub/pgarchimigrator
Web Site : http://www.pgarchihub.com
Top comments (0)