lock_timeout in PostgreSQL: the difference between a migration with retry and a site that's down

PostgreSQL's lock_timeout parameter is often treated as a configuration detail, but Christophe Pettus, of the PostgreSQL Experts consultancy, argues in his "All Your GUCs in a Row" series that it is the boundary between a migration that needed three attempts and a site that was down for twenty minutes. The piece deserves the attention of any Brazilian team running DDL in production under high concurrent load.

What the parameter does

lock_timeout limits how long a command waits to acquire a single heavyweight lock before giving up with ERROR: canceling statement due to lock timeout (SQLSTATE 55P03, the same code used for NOWAIT failures, which makes client-side handling easier). The default is 0, meaning disabled. It is configurable per session (context user), and if you don't specify a unit, the value is interpreted in milliseconds. The feature arrived in PostgreSQL 9.3, in 2013, and as Pettus notes, the fact that it took so long to exist shows how long people lived with the problem it solves.

Why a trivial migration takes down the entire table

The problem lives in a rule of the lock manager. When a backend requests a lock, PostgreSQL checks two things: whether the request conflicts with already held locks, and whether it conflicts with already waiting locks. Conflicting with either one is enough to enter the queue. This second check exists to prevent a writer from starving forever behind an endless stream of readers, but it has a devastating consequence.

Imagine an ALTER TABLE that needs ACCESS EXCLUSIVE and is waiting behind a long-running SELECT, a report someone kicked off twenty minutes earlier. From the moment the ALTER enters the queue, every subsequent command that touches that table also gets queued behind the ALTER. Reads that would have been served instantly a second earlier now wait on a DDL statement that, in turn, waits on the report. From the application's point of view, the table simply stopped existing. And here's the cruel detail: the ALTER itself would take eleven milliseconds. Pettus reports having joined more than one incident call for exactly this reason, and the root cause was never the ALTER.

The design pattern: short timeout and retry

The solution is to make the ALTER give up. By setting lock_timeout to 2s, the migration waits two seconds for the lock, fails, and the queue behind it drains immediately. The deploy tool catches the error, sleeps, and tries again. If it keeps failing, a human investigates which report is holding the table.

That is the whole design: a short lock_timeout plus a retry loop. In Pettus's words, it's the difference between "the migration needed three attempts" and "the site was down for twenty minutes." Every self-respecting migration framework either already does this automatically or instructs you to do it.

Three common mistakes

1. It limits the wait to acquire the lock, not how long the lock is held. An ALTER COLUMN ... TYPE that rewrites the table acquires the lock in less than a millisecond and then holds it for an hour, without lock_timeout having anything to say about it. Limiting running statements is the job of statement_timeout and, starting with 17, transaction_timeout.

2. It applies separately to each lock acquisition, not once per command. An ALTER TABLE can lock the table, its TOAST table, every index, and the sequences it owns, and each one of these gets a fresh lock_timeout. A transaction with five DDL statements can wait nearly five times the configured value before something fails, and when the fifth statement fails, the previous four get rolled back along with it. Postgres.ai has good material on grabbing all the needed locks at once with an explicit LOCK TABLE, so that if a failure happens, it happens before any work is done.

3. It applies to every wait on a heavyweight lock, including several that don't look like lock waits. A SELECT ... FOR UPDATE blocked on another session's row is waiting on a heavyweight lock on that session's transaction ID. pg_advisory_lock() is a heavyweight lock. And the most dangerous case: CREATE INDEX CONCURRENTLY spends most of its life waiting for old transactions to finish, which it does by waiting on their virtual transaction locks. Run this with a two-second lock_timeout on a busy table and it will be canceled during one of those waits, leaving behind an invalid index that you now need to find (pg_index.indisvalid = false) and drop. Migration tools that set a short lock_timeout for ALTER TABLE need to run SET lock_timeout = 0 before anything CONCURRENTLY, or use a value measured in minutes, and should check indisvalid afterward regardless.

The diagnostic trap

There's a detail that swallows incident information. log_lock_waits logs a waiting session, naming who is blocking it, after deadlock_timeout elapses, whose default is one second. If you set lock_timeout below that, the wait gets canceled before it is logged: you get the ERROR, the statement text (if log_min_error_statement allows it), and no record of who was in the way. The log_lock_failures added in 18 doesn't help, since it only covers NOWAIT.

The way out, in a migration session, is also to lower deadlock_timeout below lock_timeout. Since deadlock_timeout has superuser context, that requires a superuser role or, from 15 onward, a GRANT SET. Short of that, running pg_blocking_pids() against pg_stat_activity while the wait is in progress is the only way to know who is blocking.

What value to use, and where

One to three seconds for DDL is the conventional range, and Pettus doesn't disagree with it. But he insists: the number matters less than the retry loop. Set it for the migration role or in the migration tool itself, never in postgresql.conf, and not for the application's normal roles either, unless you have a specific buildup to limit and can say what the number represents. For the server as a whole, the default 0 is correct. For your migrations, it's the reason you have an outage runbook instead of a simple retry loop.