lock_timeout is a schema-migration safety parameter that happens to apply to every lock in the system. It caps how long a statement will wait to acquire any single heavyweight lock before giving up with ERROR: canceling statement due to lock timeout (SQLSTATE 55P03, the same code NOWAIT failures use, which is convenient for client code). Default 0, meaning disabled. Context user. Milliseconds if you don’t specify units. It arrived in 9.3, and that it took until 2013 to exist tells you how long people lived with the problem it solves.
The problem is one rule in the lock manager. When a backend asks for a lock, PostgreSQL checks two things: whether the request conflicts with locks already held, and whether it conflicts with locks already being waited for. Conflict with either and you join the queue. The second check is what stops a writer from starving behind an unending stream of readers, and it has a consequence that has ended more deploys than anything else in lock.c: an ALTER TABLE that needs ACCESS EXCLUSIVE and is waiting behind one long-running SELECT causes every subsequent statement touching that table to queue behind the ALTER. Reads that would have been granted instantly a moment earlier now wait on a DDL statement that is itself waiting on a report somebody started twenty minutes ago. From the application’s point of view the table has ceased to exist. The ALTER itself would have taken eleven milliseconds. I have been on the incident call for exactly this more than once, and the root cause was never the ALTER.
lock_timeout makes the ALTER give up. Set it to 2s and the migration waits two seconds for its lock, fails, and the queue behind it drains immediately. Your deploy tooling catches the error, sleeps, tries again; if it keeps failing, a human goes looking for the report. That is the entire design pattern, a short lock_timeout and a retry loop, and it is the difference between “the migration needed three attempts” and “the site was down for twenty minutes.” Every migration framework worth using either does this for you or tells you to.
Three things people get wrong about it.
It bounds the wait to acquire a lock, not the time the lock is held. An ALTER COLUMN ... TYPE that rewrites the table will take its lock in under a millisecond and then hold it for an hour, and lock_timeout will not say a word. What’s holding the lock is a running statement, and bounding running statements is statement_timeout’s job (and, from 17, transaction_timeout’s).
It applies separately to each lock acquisition, not once per statement. An ALTER TABLE may lock the table, its TOAST table, each of its indexes, and any sequences it owns, and each of those gets a fresh lock_timeout. A transaction containing five DDL statements can wait nearly five times the configured value before anything fails, and when the fifth statement does fail, the first four roll back with it. Postgres.ai has a good treatment of taking every lock you need up front with explicit LOCK TABLE, so the failure, if it comes, comes before any work is done.
And it applies to every heavyweight lock wait, including several that don’t look like lock waits. 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. CREATE INDEX CONCURRENTLY spends most of its life waiting for older transactions to finish, and it does that by waiting on their virtual transaction locks. Run it with a two-second lock_timeout on a busy table and it will be cancelled in one of those waits, leaving an invalid index behind that you now have to find (pg_index.indisvalid = false) and drop. Migration tooling that sets a short lock_timeout for ALTER TABLE needs to SET lock_timeout = 0 before anything CONCURRENTLY, or set it to something measured in minutes, and should check indisvalid afterwards regardless.
One diagnostic trap. log_lock_waits logs a waiting session, naming the blocker, after deadlock_timeout has elapsed, which defaults to one second. Set lock_timeout below that and the wait is cancelled before it is ever logged: you get the ERROR, and the statement text if log_min_error_statement allows, and no record of who was in the way. (18’s log_lock_failures does not help; it covers only NOWAIT.) In a migration session that also drops deadlock_timeout under its lock_timeout (deadlock_timeout is superuser context, so that takes a superuser role or, on 15 and later, a GRANT SET), the log tells you what you need. Otherwise, pg_blocking_pids() against pg_stat_activity while the wait is in progress is the only way to know.
As for the value: one to three seconds for DDL is the conventional range and I have no quarrel with it. The number matters less than the retry loop. Set it for the migration role or in the migration tool, never in postgresql.conf, and not for the application’s normal roles unless you have a specific pile-up you are trying to bound and can say what the number represents. For the server as a whole, the default is correct. For your migrations, it is the reason you have an outage runbook instead of a retry loop.