Hi Sami and Shihao On Mon, Sep 21, 2026 at 3:58 PM Sami Imseih [email protected] 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/[email protected] [2] https://postgr.es/m/caa4ek1kdecx3_onyw9_4f_csnguhdeqcj64esp1x6atqtgm...@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 <[email protected]> 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 > > >
