Why Your Postgres Migration Locked the Whole Table (and the Pattern That Doesn't)

The ALTER TABLE itself is usually not slow — the outage comes from Postgres queueing every other query behind the lock it is waiting for. The fix is two habits: set lock_timeout on every migration session so a blocked DDL statement fails fast instead of freezing traffic, and split anything that rewrites or validates a table into a non-blocking two-step (NOT VALID then VALIDATE, or CREATE INDEX CONCURRENTLY). Everything else in this post is detail on which operations need which treatment.

This is written for Postgres 12 and up, which is where the cheap paths for most of these operations landed. Behavior described here holds as of mid-2026.

Why did a one-second ALTER TABLE take the whole app down?

The symptom is confusing the first time. Your migration adds a column. The migration log shows it eventually completed in a few hundred milliseconds. Meanwhile the API returned 504s for four minutes, and your error tracker is full of:

ERROR:  canceling statement due to statement timeout

Here is what actually happened. Most DDL takes an ACCESS EXCLUSIVE lock, which conflicts with everything, including plain SELECT. Your ALTER TABLE asked for that lock while some long-running query — an analytics SELECT, an idle-in-transaction session left open by a connection pool, a pg_dump — still held ACCESS SHARE. So the ALTER went into the lock queue and waited.

The part people miss: Postgres queues lock requests in order. Once your ALTER is waiting, every new query that needs any conflicting lock on that table lines up behind it, even though those queries could have run fine against the old table. One waiting DDL statement converts a slow query into a full table outage.

You can watch it happen:

SELECT pid, state, wait_event_type, left(query, 60) AS query
FROM pg_stat_activity
WHERE pid = ANY(pg_blocking_pids(<your_migration_pid>));

If that returns a 20-minute-old SELECT from a BI tool, you have found the real cause. The migration was never the problem; it was the thing standing in the doorway.

Takeaway: a blocked migration is more dangerous than a slow one, because everything behind it in the lock queue blocks too.

Which Postgres migrations are actually safe to run at any time?

Not every DDL statement is a hazard. The distinction that matters is whether the operation needs to rewrite the table, scan it, or merely update the catalog.

Operation	Lock behavior	Safe on a large, busy table?
ADD COLUMN (nullable, or with a non-volatile DEFAULT)	Brief ACCESS EXCLUSIVE, catalog-only	Yes, with lock_timeout
ADD COLUMN ... DEFAULT now() (volatile default)	Full table rewrite	No — backfill instead
DROP COLUMN	Brief ACCESS EXCLUSIVE, catalog-only	Yes, but breaks SELECT * clients
CREATE INDEX	Blocks writes for the whole build	No — use CONCURRENTLY
CREATE INDEX CONCURRENTLY	SHARE UPDATE EXCLUSIVE, allows reads and writes	Yes
SET NOT NULL directly	ACCESS EXCLUSIVE + full scan	No — validate a CHECK first
ADD FOREIGN KEY	ACCESS EXCLUSIVE + scan of both tables	No — use NOT VALID then VALIDATE
ALTER COLUMN TYPE varchar(50) -> varchar(100)	Catalog-only	Yes
ALTER COLUMN TYPE int -> bigint	Full table rewrite	No — new column, backfill, swap
RENAME COLUMN	Catalog-only, instant	Only with expand/contract deploys

The two columns to internalize: "catalog-only" operations are cheap but still need the lock, so they still need a timeout. "Rewrite or scan" operations are the ones that need a different shape entirely.

Takeaway: the question is never "is this ALTER fast?" but "does it rewrite, does it scan, and how long will it wait for its lock?"

The lock_timeout retry pattern

Every migration session should refuse to wait. Set lock_timeout low — a couple of seconds — so a contended DDL statement gives up instead of building a queue behind it:

SET lock_timeout = '3s';
ALTER TABLE orders ADD COLUMN fulfillment_note text;

If the lock is busy, you get this instead of an outage:

ERROR:  canceling statement due to lock timeout

That error is a success. It means the guard worked. Then you retry, because the blocking query has usually finished by the next attempt:

import time
import psycopg

DDL = "ALTER TABLE orders ADD COLUMN fulfillment_note text"

def migrate(dsn: str, attempts: int = 10) -> None:
    for attempt in range(1, attempts + 1):
        try:
            with psycopg.connect(dsn, autocommit=False) as conn:
                with conn.cursor() as cur:
                    cur.execute("SET lock_timeout = '3s'")
                    cur.execute(DDL)
                conn.commit()
            print(f"applied on attempt {attempt}")
            return
        except psycopg.errors.LockNotAvailable:
            wait = min(2 ** attempt, 60)
            print(f"attempt {attempt} blocked; retrying in {wait}s")
            time.sleep(wait)
    raise RuntimeError("could not acquire lock; investigate long-running queries")

Two details that trip people up. lock_timeout is per-session, not per-statement, so it has to be set on the same connection that runs the DDL — setting it in a different session from your migration tool does nothing. And statement_timeout is not a substitute: it caps total execution time, which will also kill a legitimate long VALIDATE CONSTRAINT you actually wanted to finish.

Most migration frameworks let you set this once. In Django you can put SET lock_timeout in a RunSQL at the top of the migration; in Rails, disable_ddl_transaction! plus a lock_timeout in the connection setup does it. If you would rather not hand-roll the guard, Squawk lints Postgres migration files in CI and fails the build when a statement takes an unsafe lock, which catches the mistake before it reaches production.

Takeaway: a migration that fails with "lock timeout" is a working safety net, not a broken deploy.

How do you add a NOT NULL column to a large table without a rewrite?

Directly running ALTER TABLE ... SET NOT NULL forces a full sequential scan under ACCESS EXCLUSIVE to prove no nulls exist. On a large table that is minutes of total downtime.

Since Postgres 12, you can prove the same thing with a CHECK constraint that validates without blocking writes, and Postgres will then accept SET NOT NULL without rescanning:

-- 1. Add the constraint unvalidated. Instant; applies to new rows only.
ALTER TABLE orders
  ADD CONSTRAINT orders_fulfillment_note_not_null
  CHECK (fulfillment_note IS NOT NULL) NOT VALID;

-- 2. Backfill in batches, committing each batch.
UPDATE orders SET fulfillment_note = ''
WHERE fulfillment_note IS NULL
  AND id IN (SELECT id FROM orders WHERE fulfillment_note IS NULL LIMIT 5000);

-- 3. Validate. Takes SHARE UPDATE EXCLUSIVE: reads and writes keep running.
ALTER TABLE orders VALIDATE CONSTRAINT orders_fulfillment_note_not_null;

-- 4. Now cheap, because the validated CHECK already proves it.
SET lock_timeout = '3s';
ALTER TABLE orders ALTER COLUMN fulfillment_note SET NOT NULL;
ALTER TABLE orders DROP CONSTRAINT orders_fulfillment_note_not_null;

The same NOT VALID / VALIDATE split works for foreign keys, and it is the single most useful trick in this whole area.

Index creation has its own version of this: CREATE INDEX CONCURRENTLY cannot run inside a transaction block, which is why ORMs that wrap every migration in a transaction fail with CREATE INDEX CONCURRENTLY cannot run inside a transaction block. Mark the migration non-transactional. Also check the result afterward — a concurrent build that fails leaves an invalid index behind that silently does nothing:

SELECT indexrelid::regclass AS index_name
FROM pg_index WHERE NOT indisvalid;

Drop and rebuild anything that shows up there.

Takeaway: any operation that needs to scan the table has a two-step form that moves the scan out from under the exclusive lock.

What about renames, drops, and type changes?

These are not lock problems — they are deploy-ordering problems. RENAME COLUMN is instant in the catalog, and that is exactly what makes it dangerous: the old application code is still running and still selecting the old name.

The pattern is expand/contract, and it takes three deploys:

1. Expand. Add the new column. Write to both old and new from application code. Backfill the old rows.
2. Migrate reads. Deploy code that reads the new column. Old code is gone at this point.
3. Contract. Stop writing the old column, then drop it in a later deploy.

The same three-step shape covers int → bigint on a primary key, splitting one column into two, and moving a column to another table. It is slow in calendar time and completely uneventful in production, which is the trade you want.

One more footgun worth naming: DROP COLUMN is catalog-only and fast, but any client doing SELECT * into a struct or row class with strict field mapping will break the moment the column disappears. Deploy the code change first.

If you would rather have this orchestrated for you than hand-written, pgroll implements expand/contract by exposing versioned views of the schema so old and new application versions each see the shape they expect. For teams that want migrations reviewed as declarative schema state with destructive-change linting in CI, Atlas is the one that treats the schema as code and diffs it rather than asking you to write the steps by hand.

Takeaway: renames and drops are safe at the database level and unsafe at the deploy level — sequence the code, not the lock.

FAQ

What does "canceling statement due to lock timeout" mean in Postgres?
It means your statement waited longer than lock_timeout for a lock another session was holding, so Postgres aborted it. It is a protective failure, not corruption — nothing was applied, and retrying after the blocking query finishes normally succeeds.

Does ALTER TABLE ADD COLUMN lock the table in Postgres?
Yes, it takes a brief ACCESS EXCLUSIVE lock, but since Postgres 11 adding a column with a constant default no longer rewrites the table, so the lock is held for milliseconds. The risk is not the duration of the ALTER — it is how long the ALTER waits in the lock queue while other queries pile up behind it.

How do I add an index in production without downtime?
Use CREATE INDEX CONCURRENTLY, and run it outside a transaction block. It takes roughly twice as long and can leave an invalid index behind if it fails, so check pg_index WHERE NOT indisvalid afterward and rebuild anything listed.

Bottom line

If you change one thing after reading this, set lock_timeout on your migration connection and add a retry loop — that alone converts the most common Postgres migration outage into a retried deploy step. Learn the NOT VALID / VALIDATE split next, since it covers NOT NULL, foreign keys, and check constraints with one idea. Use expand/contract for anything that changes a column's name, type, or existence, and accept that it costs three deploys. Reach for tooling — a CI linter like Squawk, or a full orchestrator like pgroll or Atlas — once you have more than a couple of people writing migrations and code review stops catching the unsafe ones reliably.

Related reading

• Postgres Full-Text Search in Production: How to Load-Test the Index and Pin Down Relevance
• How Long Should You Keep Idempotency Keys? A TTL Strategy for Webhook Dedup
• Stop Guessing Your Webhook Dedup TTL: Derive It From the Delivery Contract

Comments

[Mads Hansen]

Strong explanation of the lock queue effect. One operational addition I would make is to serialize the migration runner with a Postgres advisory lock and add jitter to the retry delay. If several deploy replicas all hit the same blocked DDL, deterministic exponential backoff can make them wake and contend in the same rhythm.

I also prefer SET LOCAL lock_timeout inside the migration transaction where possible, plus a distinctive application_name. That keeps a pooled connection from carrying the timeout into unrelated work and makes the blocker/waiter pair easy to identify in pg_stat_activity. Before each retry, capture pg_blocking_pids() and the blocking transaction age; after a bounded wall-clock budget, fail the deploy and investigate instead of retrying indefinitely.

That turns fail-fast DDL into a complete operational contract: one migrator, short lock acquisition, observable blockers, jittered retries, and a hard stop.