Lock waits got a start time in pg_locks

A snapshot with no clock in it

The lock view has always been able to say who is waiting for what. It has one row per lock held or requested, a boolean saying whether the request was granted, and enough identity columns to work out which relation or transaction is involved. Join it to the activity view and you can build a blocking tree in one query, which is what every incident runbook does.

What it could not say is how long. Every row was a fact about the present instant with no history attached, so a chain of four sessions all showed as waiting and nothing distinguished the one that had been stuck for eleven minutes from the one that joined the queue while you were typing. The workarounds were all approximations. Transaction start time from the activity view is the age of the transaction, not of the wait, and those differ by however much work the session did before it hit the lock. The wait event tells you what kind of thing it is blocked on and says nothing about when it started.

PostgreSQL 14 added a timestamp recording when the wait began, set when the request goes on the queue and left null when it did not have to. The System Views section of the release notes carries it in one line, between the memory contexts view and the session statistics. It is the smallest change on this hub and probably the one that pays back fastest, because it turns a blocking tree from a picture into a triage order.

A real wait, with a duration on it

Two sessions are needed to make a lock wait, and the second one here is opened from inside the first so the whole thing is one script. The first session updates a row and holds the transaction open; the second tries to update the same row and queues.

CREATE EXTENSION dblink;
CREATE TABLE invoice (id integer PRIMARY KEY, state text);
INSERT INTO invoice VALUES (1, 'open'), (2, 'open');

CREATE EXTENSION
CREATE TABLE
INSERT 0 2

BEGIN;
UPDATE invoice SET state = 'paid' WHERE id = 1;
SELECT dblink_connect('waiter', 'dbname=' || current_database() || ' user=postgres');
SELECT dblink_send_query('waiter', 'UPDATE invoice SET state = ''void'' WHERE id = 1');
SELECT pg_sleep(2);
SELECT locktype, relation::regclass AS rel, mode, granted,
       date_trunc('second', clock_timestamp() - waitstart) AS waiting_for
FROM pg_locks
WHERE NOT granted;
COMMIT;

BEGIN
UPDATE 1
 dblink_connect 
----------------
 OK
(1 row)

 dblink_send_query 
-------------------
                 1
(1 row)

 pg_sleep 
----------
 
(1 row)

   locktype    | rel |   mode    | granted | waiting_for 
---------------+-----+-----------+---------+-------------
 transactionid |     | ShareLock | f       | 00:00:02
(1 row)

COMMIT

One row, one duration, and two things in it that surprise people who have only read about row locks.

The lock being waited for is on a transaction ID, not on the table. PostgreSQL does not keep row locks in shared memory; the lock is recorded on the row itself, and a session that finds a row locked waits on the transaction that holds it by requesting a share lock on that transaction’s ID. So the relation column is empty, and a blocking-tree query that groups by relation loses exactly the waits an operator most wants to see. Group by the blocking process instead, which pg_blocking_pids gives you directly.

The second is that the granted column is the filter that matters and the timestamp is meaningful only alongside it. A granted lock has a null start time, always, because it never waited. That is not an absence of data and it should not be coalesced to anything.

The other null, and why it is not a bug

There is a second case where the column is null on a row that is genuinely waiting, and it is brief and real. The timestamp is written after the request has been placed on the queue rather than atomically with it, so a reader can catch a row in between. The documentation says so plainly and it is easy to miss.

The consequence for a monitoring query is small and worth getting right. A wait that has just started can appear with no start time for a moment, so any expression computing a duration has to tolerate a null rather than assume one means the lock is granted. Treat null-and-not-granted as “waiting, duration unknown yet” and it stays correct; treat it as zero and a dashboard reports a wait that keeps restarting.

Fast-path locks are the other thing to know about before writing a query over this view. Weak locks on a relation, the ordinary ones a read takes, are recorded in a per-backend area rather than in the shared lock table, and they appear with the fast-path flag set. They never wait, so they are never interesting here, and filtering to ungranted rows excludes them anyway.

What it looks like after the upgrade

The column is unchanged on every later major, so nothing here breaks on the way out of 14.

SELECT count(*) FILTER (WHERE attname = 'waitstart') AS has_waitstart,
       count(*) FILTER (WHERE attname = 'fastpath')  AS has_fastpath
FROM pg_attribute
WHERE attrelid = 'pg_locks'::regclass AND attnum > 0;

 has_waitstart | has_fastpath 
---------------+--------------
             1 |            1
(1 row)

What changes around it is the vocabulary for describing the wait rather than the timing of it. PostgreSQL 17 added a catalog of wait events with descriptions, which means the name your blocking query reports beside the duration can be resolved to a sentence rather than looked up in the manual. The waits themselves, and this column, work the same way they always did.

Turning a duration into a tree

One duration on one row is triage. What an incident needs is the chain, and the chain needs a second function rather than a cleverer query against this view.

The lock view can tell you that a request is not granted and now how long it has waited. It cannot easily tell you who is holding the thing it is waiting for, because working that out means matching the ungranted row against every granted row with the same target, and the target is spread across six identity columns whose meaning depends on the lock type. pg_blocking_pids does that matching in the server and returns an array of process identifiers, which is the correct starting point and is much harder to get wrong.

The shape that follows is a recursive walk: start from the sessions that are waiting, follow the blockers, and stop at a session that is blocked by nobody. That session is the root, and it is the only one worth acting on, because everything behind it clears the moment it does. Attach the wait duration from this view to each node and the tree tells you both who to act on and how long the queue has existed.

Two details save time when you write it. The blocking array can hold more than one process, because a lock request can be behind several holders at once, so the walk is over a graph rather than a list and needs a visited set. And a root that is blocked by nobody and is nevertheless not running anything is the classic case: a session idle inside a transaction, holding a lock it took several minutes ago, which is visible as a time total on the database as well as a row here.

What to alert on

• The oldest ungranted lock in the cluster, as a duration. One number, and it is the one that says whether anything is stuck right now.
• The count of sessions waiting behind a single blocker. A chain of one is a coincidence; a chain of six is a pile-up, and the blocker is the thing to act on rather than any of the waiters.
• Waits older than your statement timeout, which should be impossible and therefore indicates a session that has no timeout set.

The temptation is to alert on the number of ungranted locks, and it is the wrong signal. A busy cluster has locks queued constantly and they clear in milliseconds. Duration is what separates normal contention from a problem, and before this column existed that distinction could not be drawn from this view at all.

For the whole diagnosis rather than the timing part of it, the blocking tree and what to do about each shape of it is the longer treatment, and pairing the duration here with the statement identifier from the activity view is what turns “something is blocked” into “this statement, which usually takes forty milliseconds, has been waiting for nine minutes”.

What it costs to read

Nothing gates the column and nothing needs restarting. The timestamp is written once when a request queues, by the process doing the waiting, into memory it already owns.

Reading the view is the part with a cost, and it is worth knowing before putting it on a short poll interval. Assembling it takes a lock on the lock manager’s own partitions, so a query against it on a cluster with many thousands of held locks is not free and competes with the very contention you are investigating. Sample it on the order of seconds rather than continuously, and filter to ungranted rows in the query rather than pulling everything and filtering afterwards.