Re: Proposal: expose heavyweight lock wait start time in pg_stat_activity

Hi Sami and Shihao

On Mon, Sep 21, 2026 at 3:58 PM Sami Imseih samimseih(dot)pg(at)gmail(dot)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]. Tom suggested in
that discussion that limiting the timestamp to heavyweight locks was
preferable to timing every type of wait [1].

The timestamp itself has already been maintained since commit 46d6e5f5,
which reuses the deadlock-timeout timestamp rather than acquiring a new
one [3].

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] https://postgr.es/m/9663.1482945914@sss.pgh.pa.us
[2]
https://postgr.es/m/CAA4eK1KDeCX3_ONyW9_4f_csNGuhDeqcJ64ESP1x6atQtgmc1w@mail.gmail.com
[3]
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(at)gmail(dot)com> wrote:

> 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
>
>
>