Postgres ALTER TABLE Lock Queues Can Block Every Read

A Postgres ALTER TABLE lock is the table-level lock a schema change must hold to run, and for most forms of ALTER TABLE it is ACCESS EXCLUSIVE, the strictest mode Postgres has. The statement can finish in a millisecond once it holds the lock. The risk is the time it spends waiting to get it.

We have read many incident reviews where a migration "documented as metadata-only" took an API down for minutes. The migration was not slow. A report that had been running before the migration started was slow, and everything else queued behind the migration while it waited.

In this post we cover why avoiding a rewrite is not the same as avoiding a lock, how the lock queue turns one long query into a read outage, how to prove it from live state and logs, how to bound it with lock_timeout, when that guard backfires, and how MySQL behaves the same way.

The Migration Took a Millisecond and the Outage Took Minutes

Here is the shape we see, as an illustration. A team adds a nullable column during a quiet window. The change was tested on a production clone, and Postgres later reports the statement itself ran in under a millisecond. For the minutes before it ran, the table answered nothing.

A reporting query had opened a transaction on the table before the migration arrived. The ALTER waited for it, and every web request after the ALTER waited for the ALTER. The outage lasted as long as the report, which the migration author never saw.

That is why we think migration reviews ask the wrong question. "What does this statement do" predicts its run time. "What else will be holding this table when it arrives" predicts the outage.

No Rewrite Is Not the Same Property as No Lock

Safe-migration advice usually focuses on avoiding table rewrites, which matters for I/O and disk. It says little about locks. The ALTER TABLE documentation states that an ACCESS EXCLUSIVE lock is acquired unless a subform says otherwise, and the explicit locking chapter is blunt about what that means. "Only an ACCESS EXCLUSIVE lock blocks a SELECT (without FOR UPDATE/SHARE) statement."

Operation	Avoids	Lock on the table
ADD COLUMN with a non-volatile default	A table rewrite	ACCESS EXCLUSIVE
SET NOT NULL with a valid CHECK constraint proving no nulls	A full table scan	ACCESS EXCLUSIVE
DROP COLUMN	A rewrite (the column is hidden, not removed)	ACCESS EXCLUSIVE
ADD FOREIGN KEY	The strictest lock	SHARE ROW EXCLUSIVE
VALIDATE CONSTRAINT	Blocking writes	SHARE UPDATE EXCLUSIVE
ATTACH PARTITION	Locking the parent exclusively	SHARE UPDATE EXCLUSIVE on the parent, ACCESS EXCLUSIVE on the attached table

The first three rows are where teams get caught. They are fast once they run, so they look safe in review, and they still take the one lock mode that stops reads.

How the Postgres ALTER TABLE Lock Queue Blocks Every Read in Four Steps

The outage is a property of the lock manager's queue, not of the migration. We walk through it in four steps, because each step is normal behavior and only the combination hurts.

1. A Long Reader Holds ACCESS SHARE

A report or analytics query opens a transaction and reads the table, taking an ACCESS SHARE lock. Locks are normally held until the end of the transaction, so the lock lasts as long as the report, plus any time the session sits idle before committing.

2. The ALTER Waits for ACCESS EXCLUSIVE

The migration arrives and requests ACCESS EXCLUSIVE, which conflicts with every other mode, including the reader's ACCESS SHARE. It cannot be granted, so it waits. By default it waits indefinitely, because lock_timeout is zero unless someone sets it.

3. Every New Query Queues Behind the ALTER

A web request now asks for ACCESS SHARE. It does not conflict with the long reader, but it does conflict with the pending ACCESS EXCLUSIVE request ahead of it, so it waits too. The pg_blocking_pids documentation names this a soft block, where a process "is waiting for a lock that would conflict with the blocked process's lock request and is ahead of it in the wait queue."

This ordering is deliberate. If new readers could jump the queue, a busy table would never give the ALTER a gap, and the writer would starve. Postgres chooses fairness to the writer, and the price is that a pending exclusive request becomes a wall.

4. The Connection Pool Fills Up

Each blocked query holds its connection while it waits. Within seconds on a busy service the pool is exhausted, and requests that never touch the locked table start failing because no connection is free. That is how one table's lock queue becomes a whole-service outage, the same hold-and-wait shape we covered in our connection pool starvation analysis.

Prove the Lock Queue From Live State and Logs

During the stall, the fastest evidence is pg_blocking_pids joined to pg_stat_activity. It shows who is blocked, by whom, and how long each session has been in its current state.

SELECT a.pid, a.state, now() - a.xact_start AS xact_age,
       pg_blocking_pids(a.pid) AS blocked_by, left(a.query, 60) AS query
FROM pg_stat_activity a
WHERE cardinality(pg_blocking_pids(a.pid)) > 0
   OR a.pid IN (SELECT unnest(pg_blocking_pids(pid)) FROM pg_stat_activity)
ORDER BY a.xact_start;

The oldest transaction at the top is usually the root, and the ALTER sits just behind it with a long list of blocked sessions behind that. After the fact the live state is gone, so we keep log_lock_waits on, which logs a message when a session waits longer than deadlock_timeout for a lock.

Lock queues vanish within seconds of resolving, which is why the evidence for root cause analysis is easy to lose. It helps to connect an investigator read-only to your database and logs, so the blocking chain and the migration log can be lined up while they still exist.

Bound the Wait With lock_timeout and a Retry Loop

The fix is to stop the ALTER from waiting at the front of the queue. We set a short lock_timeout for the migration session only, and retry until it finds a gap between long transactions.

• Set lock_timeout per session. The client configuration documentation says it aborts any statement that waits longer than the limit for a lock, and that setting it in postgresql.conf is not recommended because it would affect all sessions.
• Keep it below statement_timeout. The same page notes it is "rather pointless" to set lock_timeout at or above a nonzero statement_timeout, because the statement timeout would always trigger first.
• Retry with backoff. A failed attempt costs one short wait. A retry loop with a small delay usually lands in a gap within a few tries.
• Bound the long transactions too. idle_in_transaction_session_timeout ends sessions left idle inside a transaction, which removes the most common blocker.

Our guide to idle in transaction sessions covers finding those blockers, and the full migration procedure is in our zero downtime database migration guide. Timer ordering matters beyond the database as well, as our post on 504 gateway timeouts shows.

When lock_timeout Makes the Migration Worse

A short lock_timeout is not free. On a table that always has a long transaction open, such as one read by continuous analytics, the retry loop never finds a gap and the migration simply never runs. We have seen teams lengthen the timeout until it stops failing, which brings the outage back.

In that case the guard is telling the truth. The table needs its long readers moved to a replica, bounded with a statement timeout, or paused for the change. We would fix the blocker rather than tune the guard.

Retries also need a ceiling. A loop that retries forever inside a deploy pipeline turns a lock problem into a stuck release, so we cap attempts and fail the deploy loudly when the cap is reached.

MySQL Metadata Locks Follow the Same Priority Rule

This is not a Postgres quirk. The MySQL reference manual on metadata locking says "Write lock requests have higher priority than read lock requests", and that the server defers releasing metadata locks on tables used in a transaction until the transaction ends.

The outcome is the same. A DDL statement waiting for a metadata lock held by an open transaction blocks the queries that arrive after it, so we ask the same review question on both databases.

Review the Postgres ALTER TABLE Lock, Not Just the Rewrite

As more operations become metadata-only, the risk moves from the data on disk to the queue in the lock manager. We would add two lines to every migration review, the lock each statement takes and the longest transaction likely to be open when it runs, and treat a Postgres ALTER TABLE lock request without lock_timeout as a defect.

Check the oldest open transaction before the next migration window. Operate runs dedicated AI agents inside your own infrastructure that investigate production issues through read-only adapters, and every finding cites its evidence.