The ALTER Rewrote Nothing and the Table Was Unreadable for Nine Minutes: Lock Queues Are Not First-Come-First-Served

TL;DR A metadata-only ALTER TABLE in PostgreSQL can trigger a full read outage even if it completes in milliseconds and rewrites no data. This happens because the pending ACCESS EXCLUSIVE lock request blocks every subsequent query—including simple SELECT statements—creating a lock convoy that lasts as long as the oldest open transaction on that table.

The migration was instant. The outage was not.

It is a common scenario for platform teams: an incident report arrives describing a ten-minute API outage. The logs show a database migration—specifically an ALTER TABLE—that was documented as a metadata-only change with no table rewrite. It was tested against a production clone, shipped during a quiet window, and Postgres itself reports the statement took less than a millisecond to execute.

Yet, for the duration of that migration's attempt to acquire a lock, the table was completely unreadable. The migration didn't cause the delay; a reporting query that had been running for nine minutes before the migration even started did. Because the migration was waiting for that report to finish, every other user request that arrived afterward was forced to wait behind the migration. The duration of your "instant" migration was actually dictated by a query the migration author never saw and did not control.

No rewrite is not the same property as no lock

The "safe migration" canon traditionally focuses on avoiding table rewrites. While important for managing I/O and disk space, rewrite avoidance is not a proxy for lock safety. According to the PostgreSQL documentation, an ALTER TABLE statement acquires an ACCESS EXCLUSIVE lock unless explicitly noted otherwise. This is the strictest lock level available, and as the locking docs state, it is the only mode that "blocks a SELECT (without FOR UPDATE/SHARE) statement."

Over the last several Postgres releases, many operations were optimized to avoid rewrites or full scans, but they notably retained their high lock requirements.

Only operations in the latter half of this list are truly "safe" under heavy load. If you use the Expand-Contract Playbook, you are already managing schema drift, but the lock acquisition order remains the hidden variable that can turn a "safe" metadata change into a site-wide incident.

The queue is the mechanism

To understand why the outage happens, we must look at the lock manager's queue. Imagine three transactions arriving in this specific order:

1. Transaction A (The Long Reader): A heavy SELECT for a daily report starts. It acquires ACCESS SHARE and runs for 11 minutes.
2. Transaction B (The Migration): An ALTER TABLE arrives one minute later. It requires ACCESS EXCLUSIVE. It cannot proceed because Transaction A holds a conflicting lock. It enters the wait queue.
3. Transaction C (The Web Request): A standard SELECT arrives one second after the migration.

Crucially, Transaction C does not run, even though its ACCESS SHARE lock does not conflict with Transaction A. It is blocked by Transaction B.

The PostgreSQL documentation explains this through the definition of a "soft block" in pg_blocking_pids. A process is blocked if it "is waiting for a lock that would conflict with the blocked process's lock request and is ahead of it in the wait queue." As Marco Slot at Citus notes, any SELECT that comes after a pending ALTER will be blocked until the ALTER is finished.

This leads to connection pool saturation. Because every blocked SELECT holds its connection while waiting, and because "a transaction seeking a lock will wait indefinitely" by default, a single long-running query can cause every available connection to pile up behind an ACCESS EXCLUSIVE request.

Key Takeaway: A pending exclusive lock acts as a barrier in the queue, blocking all subsequent reads even if the migration itself hasn't started its work yet.

Proving it happened, after the fact and during

If you are in the middle of a stall, the PostgreSQL Wiki suggests starting with:

SELECT relation::regclass, * FROM pg_locks WHERE NOT granted;

However, the official documentation warns that pg_locks does not easily show the order of the queue. To see the actual chain of blocking, you should use pg_blocking_pids() joined to pg_stat_activity. This function identifies exactly which PIDs are holding up your migration and, by extension, your entire application. Be aware that pg_blocking_pids requires a brief exclusive access to the lock manager's shared state, so use it judiciously under extreme load.

For post-mortem analysis, ensure log_lock_waits is enabled alongside a reasonable deadlock_timeout. This allows you to find lock convoys in your logs long after the migration has finished and the evidence has vanished from the active process list.

Bounding the damage

The most effective way to prevent a lock_timeout postgres disaster is to use a lock_timeout. This setting aborts any statement that waits longer than a specified time to acquire a lock. By default, it is zero (disabled).

A common practitioner pattern—not explicitly documented as a recommendation but widely used—is the "retry loop." By setting a lock_timeout of, say, 100ms, the migration will fail immediately if it cannot get its lock, rather than staying in the queue and blocking traffic. You can then retry the migration until it finds a gap between long-running queries.

Note the documented ordering constraint: if statement_timeout is non-zero, it is pointless to set lock_timeout to a larger value, as the statement timeout will trigger first. As discussed in our piece on inverted timers, your guardrails must be sequenced correctly to be effective.

Furthermore, you must address the root cause—the long-running transaction—by setting idle_in_transaction_session_timeout or statement_timeout. Without these, your database remains vulnerable to the "eleven-minute report" problem.

MySQL has the same problem and documents it more plainly

This is not a Postgres quirk; it is a fundamental property of lock managers designed to avoid writer starvation. In MySQL's InnoDB, metadata locks follow a similar priority: "Write lock requests have higher priority than read lock requests." The MySQL documentation explicitly shows an example where a RENAME TABLE (exclusive) jumps ahead of a waiting INSERT because of this priority. In both systems, the metadata locks are held until the transaction ends, creating the same potential for an accidental outage.

The variable nobody puts in the migration review

A standard migration review asks: "What does this statement do?" The question that actually predicts the outage is: "What else will be running when this arrives?"

If you do not know the duration of your longest-running transaction, you do not know the potential duration of your outage. Because lock_timeout and transaction bounds are disabled by default in most managed Postgres environments, most teams only discover this mechanism after a "simple" column addition takes the site down.

"Why this matters" is clear: as Postgres matures, more operations become "metadata-only," giving engineers a false sense of security. The bottleneck has shifted from the data on disk to the queue in the lock manager.

Investigating these incidents after the fact is difficult because the evidence—the lock queue—is transient. Operate watches your production environment in real-time, identifying these lock convoys as they form. By the time a human operator looks at the dashboard, Operate has already correlated the pending ALTER with the blocking PID and can draft a PR to wrap your migrations in appropriate lock_timeout logic, ensuring your next "instant" migration truly stays that way.

Sources & further reading

• According to PostgreSQL Docs, ALTER TABLE requires ACCESS EXCLUSIVE unless otherwise specified.
• According to Citus Data, a pending ALTER blocks all subsequent SELECT queries.
• According to MySQL Docs, write lock requests take priority over read lock requests to prevent starvation.
• According to PostgreSQL Release Notes, version 11 added non-null default additions without rewrites.