Why ALTER TABLE Still Takes Down Production Postgres in 2026

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=3msp95=3msp99=4ms
DURING migration: p50=4msp95=5msp99=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: github.com/pgarchihub/pgarchimigrator
Web Site : http://www.pgarchihub.com

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=3msp95=3msp99=4ms
DURING migration: p50=4msp95=5msp99=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: github.com/pgarchihub/pgarchimigrator
Web Site : http://www.pgarchihub.com
Top comments (0)