log_lock_waits is the cheapest lock-contention detector PostgreSQL ships, and through version 18 it is off by default. Turn it on. PostgreSQL 19 will do it for you.
The mechanism is borrowed rather than built. When a backend has to sleep on a heavyweight lock (a row, a relation, a transaction ID, an advisory lock), it arms a timer for deadlock_timeout, one second by default, and when that timer fires it wakes up to run the deadlock detector. With log_lock_waits on, the same wakeup also writes a line to the log:
1 LOG: process 557 still waiting for ShareLock on transaction 793 after 200.070 ms 2 DETAIL: Process holding the lock: 556. Wait queue: 557. 3 CONTEXT: while updating tuple (0,10) in relation "users" 4 STATEMENT: update users set note='b' where id=1
The DETAIL is the reason to want this: the PID that is holding the lock and the queue behind it, which is the fact you would otherwise have to catch live with pg_blocking_pids() while the wait was still happening. When the lock finally arrives, a second line reports the total:
1 LOG: process 557 acquired ShareLock on transaction 793 after 799.733 ms
That second line carries the number you care about. The first one only tells you a wait crossed the threshold; the second tells you how bad it got. Row-lock waits get a CONTEXT line naming the tuple and table; relation-lock waits name the relation by OID (relation 16397 of database 5), so keep ::regclass handy.
The default is off and the context is superuser. The parameter has existed since 8.3, the holder-and-queue DETAIL since 9.4, and in 19 (currently at beta 3) the default becomes on, on the reasoning that a second stuck behind a lock is almost always worth a log entry. I have been saying that for years; it is nice to have the project agree.
One timer, two jobs
There is no separate threshold. The wait has to exceed deadlock_timeout, because the timer that fires the deadlock check is the timer that fires the log message, and when the project flipped the default for 19 it considered adding a second one and declined. (log_recovery_conflict_waits, the standby’s version of this, hangs off the same timer.) So if you lower deadlock_timeout to see shorter waits you are also running the deadlock detector more often, which is not free on a busy system; the docs’ advice to leave it at a second is sound, and 200ms is a debugging setting, not a production one.
deadlock_timeout is superuser context, and the comment in the source says why: “This is PGC_SUSET to prevent hiding from log_lock_waits.” A user who could raise it could make their own waits invisible. That is the whole reason a lock-manager knob has a logging-grade privilege requirement.
Two interactions with lock_timeout. If lock_timeout is shorter than deadlock_timeout, the statement is cancelled before the timer fires and log_lock_waits never says a word; all you get is the canceling statement due to lock timeout error, which is logged anyway. If it is longer, you get the still waiting line and then the cancellation, and no acquired line, because nothing was acquired. And a deadlock, with this on, logs detected deadlock while waiting for ... from the victim a moment before the familiar deadlock detected error, while the survivor logs an acquired.
The cost is close to nothing. The message is only generated for a wait that has already lasted a second; you were already slow. On a badly contended system it can be chatty, and that is the system where you need it most.
The one that refuses to wait
New in 18, default off, superuser. It covers the other outcome: not a wait that took too long, but an attempt that refused to wait at all. When SELECT ... FOR UPDATE NOWAIT (or FOR SHARE NOWAIT) hits a locked row, the client gets could not obtain lock on row in relation "users" and nothing else; the error does not say who has the row. With this on, the log does:
1 LOG: process 571 could not obtain ShareLock on transaction 802 2 DETAIL: Process holding the lock: 570, Wait queue: . 3 STATEMENT: select * from users where id=1 for update nowait 4 ERROR: could not obtain lock on row in relation "users"
(The empty wait queue renders as Wait queue: ., which is either a bug or a very small joke.)
The scope is narrow and the docs say so: row-level NOWAIT only. LOCK TABLE ... NOWAIT fails with the same shape of error and logs nothing extra. lock_timeout cancellations are not “failures” in this sense and are not covered. SKIP LOCKED never fails, so there is nothing to log. The plumbing underneath (a logLockFailure flag on the lock-acquisition path) is general; only the row-lock callers pass it.
If your application uses NOWAIT on rows and its failures are a recurring mystery, this is the answer, after the fact, without the race of querying pg_locks from inside the exception handler. If your application uses NOWAIT as a polling primitive in a hot loop, this will log every miss, and the fix is SKIP LOCKED, not a logging setting. Otherwise it is inert: it costs nothing until something fails, and when something fails you want to know who.
log_lock_waits = on everywhere, on every supported version, today. log_lock_failures = on on 18 unless you know your application polls with NOWAIT. Leave deadlock_timeout alone.