PostgreSQL

PostgreSQL — ALTER TABLE blocked, or cancelled by lock timeout

Written and reviewed by Sahil Srivastav

PostgreSQLMigrationsLocking

ERROR:  canceling statement due to lock timeout
CONTEXT:  while locking relation "orders"
STATEMENT:  ALTER TABLE orders ADD COLUMN settled_at timestamptz

What this error actually means

Your DDL asked for an `ACCESS EXCLUSIVE` lock on the table and could not get it within `lock_timeout`, so PostgreSQL cancelled it. If you had no `lock_timeout` set, it would instead have waited — and that waiting is where outages come from.

The mechanism people miss is the **lock queue**. While your `ALTER TABLE` waits for an exclusive lock, every subsequent query that needs any conflicting lock queues *behind* it, including plain `SELECT`s that would otherwise have run happily alongside the existing holder. So one long-running transaction plus one DDL statement is enough to stall all traffic on that table, even though the DDL itself has not started and the reads do not conflict with each other.

This is why a migration that takes milliseconds in staging takes a site down in production. The duration of the `ALTER` is irrelevant; what matters is how long it waits, and how much traffic piles up behind it while it does. Getting cancelled by `lock_timeout` is the good outcome — it means the guard worked.

Causes, most common first

1A long-running or idle-in-transaction session holding the table. Any open transaction that touched the table holds at least `ACCESS SHARE`, which conflicts with `ACCESS EXCLUSIVE`. An idle-in-transaction session blocks DDL indefinitely while appearing to do nothing at all.
2DDL run with no lock_timeout. Without a bound, the statement waits forever and accumulates a queue behind it. Almost every migration-induced outage has this as its proximate cause.
3An operation that rewrites the whole table. Some changes require a full rewrite while holding the exclusive lock — changing a column type, adding a column with a volatile default on older versions, `VACUUM FULL`. Duration then scales with table size and the lock is held throughout.
4CREATE INDEX without CONCURRENTLY. A plain `CREATE INDEX` blocks writes for the entire build, which on a large table is minutes. `CONCURRENTLY` exists precisely to avoid this and is almost always what you want in production.
5Adding a foreign key or NOT NULL that validates existing rows. The validation scan runs while holding a strong lock. On a large table this is a long hold, and it is avoidable by adding the constraint as `NOT VALID` and validating separately.

When you see it

A migration hangs, and then all queries on that table hang too, while other tables stay healthy
Connection pool exhaustion within seconds of starting a deploy
The blocker turns out to be an old idle-in-transaction session or a long analytics query
`pg_locks` shows a queue of ungranted locks all waiting on the same relation
Recovery is immediate the moment the DDL is cancelled or the blocker ends

How to diagnose it

Step 1

Find who is blocking the DDL

`pg_blocking_pids()` gives you the answer directly. Run it while the migration is stuck — the blocker is usually not what anyone expects.

SELECT a.pid, a.state, now() - a.xact_start AS xact_age, left(a.query, 90)
FROM pg_stat_activity a
WHERE a.pid = ANY(pg_blocking_pids(<ddl_pid>));

Step 2

See the queue that has formed behind it

Ungranted locks on the same relation are the traffic you are currently stalling. The size of this list is the size of the incident.

SELECT l.pid, l.mode, l.granted, a.state, left(a.query, 60)
FROM pg_locks l JOIN pg_stat_activity a USING (pid)
WHERE l.relation = 'orders'::regclass
ORDER BY l.granted, l.pid;

Step 3

Know whether your change rewrites the table

Check the operation against the version-specific rules before running it. Adding a nullable column is instant on modern PostgreSQL; changing a column type generally is not.

Step 4

Test the migration against production-sized data

Staging tables with a thousand rows cannot reveal a rewrite that takes four minutes. Time the migration on a restored production-sized copy before it goes anywhere near production.

The fix

Always set `lock_timeout` before DDL, and keep it short. A migration that fails after two seconds is a retryable non-event; a migration that waits five minutes is an outage. This one line converts the worst failure mode into the mildest.

Wrap DDL in a retry loop with backoff. With a short `lock_timeout`, the migration simply waits for a quiet moment and succeeds on a later attempt — which is exactly the behaviour you want and cannot get by waiting in the lock queue.

Use `CREATE INDEX CONCURRENTLY` for indexes on any table with traffic. It takes longer and cannot run inside a transaction block, and it can leave an invalid index if it fails — check `pg_index.indisvalid` afterwards and drop-and-retry if needed. Those are small prices for not blocking writes.

Split validating changes into two steps. Add constraints as `NOT VALID`, then `VALIDATE CONSTRAINT` in a separate transaction that takes a weaker lock. Same for `NOT NULL` on newer versions via a check constraint. The expensive scan then happens without blocking writes.

Eliminate the blockers as standing policy: `idle_in_transaction_session_timeout` in production, and a rule that analytics and export queries do not run against the primary during deploy windows. The DDL is rarely the problem — the transaction it waits behind is.

-- Waits indefinitely and queues all traffic behind it
ALTER TABLE orders ADD COLUMN settled_at timestamptz;

-- Fails fast instead of taking the site down; retry from the migration runner
SET lock_timeout = '2s';
ALTER TABLE orders ADD COLUMN settled_at timestamptz;
RESET lock_timeout;

-- Index without blocking writes (cannot run inside a transaction block)
CREATE INDEX CONCURRENTLY orders_settled_at_idx ON orders (settled_at);

-- Constraint in two steps: cheap lock, then validate without blocking writes
ALTER TABLE orders ADD CONSTRAINT orders_amount_positive
    CHECK (amount > 0) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_amount_positive;

How to stop it coming back

Make `lock_timeout` mandatory in the migration tooling so no DDL can be run without it
Keep `idle_in_transaction_session_timeout` set in production; it removes the most common blocker
Require `CONCURRENTLY` for index creation on tables above a row threshold, enforced in migration review
Time every migration against a production-sized restore before it ships
Deploy schema changes separately from code that depends on them, so a cancelled migration is never an outage

Practise this failure in a real repository

Gronex ships this as a runnable repository: a schema change that must ship while reads and writes continue. The tests run traffic throughout the migration and assert no failed request and no lost write, so a migration that merely completes does not pass.

FAQ

Why do SELECTs block when my ALTER is only waiting?

Because lock requests queue in order. Your pending `ACCESS EXCLUSIVE` request sits ahead of subsequent readers, and PostgreSQL will not let them jump the queue, so they wait for your DDL — which is itself waiting for someone else. Two conflicting parties become a full stall on that table.

Is CREATE INDEX CONCURRENTLY always better?

In production with live traffic, nearly always. It is slower, cannot run in a transaction block, and can leave an invalid index if interrupted — so check `indisvalid` afterwards. Blocking writes for minutes is worse than all of that.

Which ALTER TABLE operations are safe on a large table?

Version-dependent, so check the docs for your release rather than trusting habit. Broadly: adding a nullable column is instant on modern PostgreSQL, adding one with a constant default is too, renaming is instant, dropping is instant. Changing a column type, adding a validated foreign key, and `VACUUM FULL` all rewrite or scan and need the two-step or concurrent treatment.

What lock_timeout should I use?

A few seconds. Long enough to succeed during a brief write, short enough that a queue never builds. Pair it with retries — the combination is what makes migrations boring.

Idle in transaction sessions piling up Canceling statement due to statement timeout Zero-downtime database migration

Other errors engineers hit next to this one

Full error and symptom index →