Proposal: expose heavyweight lock wait start time in pg_stat_activity
Hi,
I'd like to propose exposing the start time of the current heavyweight lock
wait in pg_stat_activity, perhaps as:
*lock_wait_start timestamptz
*
pg_stat_activity currently tells us that a backend is waiting on a lock
through wait_event_type and wait_event, but not when that wait started.
For example:
*SELECT pid, state, wait_event_type, wait_event, query
FROM pg_stat_activity
WHERE wait_event_type = 'Lock';
*
might show:
*pid state wait_event_type wait_event
8123 active Lock transactionid
*
We know PID 8123 is blocked, but not whether it has been waiting for 50 ms
or 15 minutes.
The timestamp already exists in PGPROC.waitStart and is exposed as
pg_locks.waitstart.
A similar idea was discussed in 2016, when the concern was the cost of
acquiring a timestamp for every wait event. For heavyweight locks, that
concern should no longer apply. Since commit 46d6e5f5, PostgreSQL already
records the timestamp using the deadlock-timeout timing infrastructure:
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=46d6e5f5
pg_stat_get_activity() already has the backend's PGPROC available and reads
wait_event_info from it, so exposing waitStart would require only another
atomic read and no new timing instrumentation.
I suggest the name lock_wait_start to make it clear that this applies only
to heavyweight lock waits.
This could allow:
*SELECT pid,
wait_event,
lock_wait_start,
now() - lock_wait_start AS waiting_for
FROM pg_stat_activity
WHERE wait_event_type = 'Lock';
*
Before preparing a patch, does exposing this directly in pg_stat_activity
seem reasonable?
If widening pg_stat_activity is undesirable, would a function such as
pg_stat_get_backend_lock_wait_start(pid) be preferable?
Thanks,
Alex Shapalov
Hi,
I'd like to propose exposing the start time of the current heavyweight lock wait in pg_stat_activity, perhaps as:
lock_wait_start timestamptz
pg_stat_activity currently tells us that a backend is waiting on a lock through wait_event_type and wait_event, but not when that wait started.
pg_locks.waitstart already provides this information. Any reason that
is not sufficient?
pg_locks is meant to be joined with pg_stat_activity anyhow. The two
views aren't read
from the same snapshot, so they can be out of sync. But that's already
true inside
pg_stat_activity, as the docs say:
"To keep the reporting overhead low, the system does
not attempt to synchronize different aspects of activity data for a
backend. As a result,
ephemeral discrepancies may exist between the view's columns."
The same will be true even if we add waitstart to pg_stat_activity.
--
Sami Imseih
Amazon Web Services (AWS)
Hi Alex,
pg_locks.waitstart already provides this information. Any reason that
is not sufficient?
I agree with Sami here. A backend waits on at most one lock at a time,
so a view on your side gives you the same thing today:
CREATE VIEW my_activity AS
SELECT a.*, l.waitstart AS lock_wait_start
FROM pg_stat_activity a
LEFT JOIN pg_locks l ON l.pid = a.pid AND NOT l.granted;
The only thing that view cannot give you is avoiding the lock manager
scan pg_locks does on every read. I do not think that is an issue
unless something queries the view very often, like a monitoring agent
sampling every second. If that is your case, please show numbers for
it. That is the case the patch would need to make.
Thanks,
Shihao
Hi Sami and Shihao
On Mon, Sep 21, 2026 at 3:58 PM Sami Imseih samimseih.pg@gmail.com wrote:
pg_locks.waitstart already provides this information. Any reason that is
not sufficient?
pg_locks is meant to be joined with pg_stat_activity anyhow. The two
views aren't read from the same snapshot, so they can be out of sync.
But that's already true inside pg_stat_activity, as the docs say:
[...]
The same will be true even if we add waitstart to pg_stat_activity.
Agreed on consistency. Both routes are best-effort, so that isn't an
advantage of putting it in pg_stat_activity.
The difference I was interested in is the cost of getting the value.
The pg_locks route builds the lock status data for the cluster and then
the join discards everything except the relevant waiting rows. The value
itself is already available as PGPROC->waitStart, and
pg_stat_get_activity() already has that backend's PGPROC available.
I benchmarked both approaches with 5 sessions blocked on a table lock:
locks held elsewhere clients pg_locks new column
0 1 0.45 ms/query 0.21 ms/query
0 8 7,487 q/s 26,078 q/s
10,000 1 3.96 ms/query 0.24 ms/query
10,000 8 1,429 q/s 24,031 q/s
*So with 10,000 other locks the direct path was about 16-17x faster inthese
tests, while its cost stayed roughly constant.*
I also tested a concurrent pgbench workload while polling. At 10 polls
per second I couldn't measure a workload-level difference above the
noise on this machine, so I'm not claiming one. The measurable benefit
is the monitoring query itself, particularly as the lock table grows.
I also couldn't measure additional overhead on SELECT * FROM
pg_stat_activity; paired runs varied in both directions within the
machine's noise floor.
While implementing this, I found one detail that needs fixing before
waitStart can be exposed directly.
PGPROC->waitStart is cleared in ProcWakeup() when a lock is granted, but
not when a wait ends through RemoveFromWaitQueue(), such as lock_timeout,
query cancellation, or a deadlock victim. pg_locks hides that stale
value because it only exposes waitstart for an ungranted lock.
The patch therefore also clears waitStart in RemoveFromWaitQueue(). An
isolation test covers normal grant, lock_timeout, and deadlock-victim
paths. The timeout and deadlock cases fail without that reset and pass
with it.
I used lock_wait_start rather than wait_start because the value describes
heavyweight lock waits only. This also addresses the naming concern Amit
raised in the earlier waiting_start discussion [2]/messages/by-id/CAA4eK1KDeCX3_ONyW9_4f_csNGuhDeqcJ64ESP1x6atQtgmc1w@mail.gmail.com. Tom suggested in
that discussion that limiting the timestamp to heavyweight locks was
preferable to timing every type of wait [1]/messages/by-id/9663.1482945914@sss.pgh.pa.us.
The timestamp itself has already been maintained since commit 46d6e5f5,
which reuses the deadlock-timeout timestamp rather than acquiring a new
one [3]https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=46d6e5f567906389c31c4fb3a2653da1885c18ee.
Tests with the patch:
regress: 239/239
isolation: 134/134
new isolation spec repeated under full test load: 30/30
These measurements were on an Apple M-series Mac, macOS, TCP loopback,
fsync off, with medians from 3-5 runs. I can rerun the benchmark on a
Linux server if useful.
Patch attached.
[1]: /messages/by-id/9663.1482945914@sss.pgh.pa.us
[2]: /messages/by-id/CAA4eK1KDeCX3_ONyW9_4f_csNGuhDeqcJ64ESP1x6atQtgmc1w@mail.gmail.com
/messages/by-id/CAA4eK1KDeCX3_ONyW9_4f_csNGuhDeqcJ64ESP1x6atQtgmc1w@mail.gmail.com
[3]: https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=46d6e5f567906389c31c4fb3a2653da1885c18ee
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=46d6e5f567906389c31c4fb3a2653da1885c18ee
Thanks,
Alex Shapalov
On Mon, Sep 21, 2026 at 4:59 PM shihao zhong <zhong950419@gmail.com> wrote:
Show quoted text
Hi Alex,
pg_locks.waitstart already provides this information. Any reason that
is not sufficient?I agree with Sami here. A backend waits on at most one lock at a time,
so a view on your side gives you the same thing today:CREATE VIEW my_activity AS
SELECT a.*, l.waitstart AS lock_wait_start
FROM pg_stat_activity a
LEFT JOIN pg_locks l ON l.pid = a.pid AND NOT l.granted;The only thing that view cannot give you is avoiding the lock manager
scan pg_locks does on every read. I do not think that is an issue
unless something queries the view very often, like a monitoring agent
sampling every second. If that is your case, please show numbers for
it. That is the case the patch would need to make.Thanks,
Shihao
Hi Alex,
Patch attached.
Looks like the patch is missing. The archive shows no attachment.
*> So with 10,000 other locks the direct path was about 16-17x faster in>
these tests, while its cost stayed roughly constant.*
I agree the new column is faster. My question is whether that matters
in production. The frequent readers of these views are monitoring
tools, and they usually poll every X seconds or slower. At that rate 4 ms
per poll is noise, and your own pgbench run at 10 polls per second
showed no difference either. So I'm not sure the cost alone is enough
reason to add the column.
For me, creating a view is the right approach.
Thanks,
Shihao