Head-of-line blocking
The lock queue is first-come, first-served, and that is the whole problem. A statement that conflicts with nothing currently held can still be stuck behind one that does.
10 minutes · lock queue · head-of-line blocking · lock_timeout
The queue is fair, and that is the trouble
When a statement cannot get its lock, it joins a queue. PostgreSQL will not let a later request jump a conflicting earlier one, because that would let a stream of short readers starve a writer forever.
The consequence is the thing nobody expects. A SELECT that conflicts with nothing currently held still waits — because an ACCESS EXCLUSIVE is queued in front of it, and the SELECT conflicts with that.
This shop has a nightly revenue report that takes thirty seconds. It is an ordinary SELECT and it bothers nobody: other SELECTs run right through it. Now put one ALTER TABLE behind it.
ALTER TABLE orders ADD COLUMN shipped_at timestamptz;
Watch: The lock queue. Scrub to the moment it starts growing and read who each waiter says it is behind.
The ALTER TABLE holds its lock for two milliseconds. The outage lasts thirty seconds, and none of it is the ALTER's fault.
Who is actually blocking whom
A thirty-second SELECT is running. An ALTER TABLE arrives and queues behind it. A second, ordinary SELECT arrives after that.
Predict
What is the second SELECT waiting on?
The first line of every migration
You cannot stop a long query from being in front of you. What you can do is refuse to wait behind it.
SET lock_timeout bounds how long a statement will wait for a lock. When it fires, the statement gives up and errors, and — this is the point — it leaves the queue, so everything behind it goes through.
This is not the same as statement_timeout, which bounds how long a statement runs. Cancelling a migration that is waiting is free. Cancelling one half-way through rewriting forty-one million rows throws away the work and holds the lock while it rolls back.
SET lock_timeout = '2s'; ALTER TABLE orders ADD COLUMN shipped_at timestamptz;
Watch: The statement gives up after two seconds. Watch what the blocked-writes figure does when it does.
A migration that fails because it could not get a lock in two seconds is a migration you re-run. A migration that took the site down for thirty is a postmortem.
The database
The same one every claim above was made about. Nothing here is graded — run whatever you like.
Answer the 1 prediction above first.
All tracks