On 30th of June 2026, Michael Paquier committed patch:
Add backend-level lock statistics This commit adds per-backend lock statistics, providing the same information as pg_stat_lock. It is now possible to retrieve those stats (lock wait counts, wait times, and fast-path exceeded count) on a per-backend basis. This data can be retrieved with a new system function called pg_stat_get_backend_lock(), that returns one tuple per lock type based on the PID provided in input. Like pg_stat_get_backend_io(), this is useful if joined with pg_stat_activity to get a live picture of the locks behavior for each running backend. pgstat_flush_backend() gains a new flag value, able to control the flush of the lock stats. This commit is straight-forward, relying on the infrastructure provided by 9aea73fc61d4 (backend-level pgstats). Bump catalog version. No need to touch PGSTAT_FILE_FORMAT_ID as backend statistics are never written to disk. Author: Bertrand Drouvot <bertranddrouvot.pg@gmail.com> Reviewed-by: Tatsuya Kawata <kawatatatsuya0913@gmail.com> Reviewed-by: Michael Paquier <michael@paquier.xyz> Reviewed-by: Tristan Partin <tristan@partin.io> Reviewed-by: Rui Zhao <zhaorui126@gmail.com> Discussion: https://postgr.es/m/aiAzEY+cMQb/W8yu@bdtpg
Commit message is pretty verbose, but let's try to see it in action.
Started three separate psqls. In first of them, ran:
=$ begin; BEGIN =*$ lock table t in access exclusive mode ; LOCK TABLE
and then I let it be.
Then, in another backend (with pid 2191568) I ran:
=$ select * from t;
which, of course, hanged, because of lock from psql #1.
Then, couple of seconds later, I ran the same select in psql #3 (pid 2191757).
And then I closed transaction with LOCK.
I could see data about the lock and waiting like:
=$ select * from pg_stat_lock; locktype │ waits │ wait_time │ fastpath_exceeded │ stats_reset ──────────────────┼───────┼───────────┼───────────────────┼─────────────────────────────── relation │ 2 │ 13525.1 │ 0 │ 2026-07-06 09:50:46.914409+02 extend │ 0 │ 0 │ 0 │ 2026-07-06 09:50:46.914409+02 frozenid │ 0 │ 0 │ 0 │ 2026-07-06 09:50:46.914409+02 page │ 0 │ 0 │ 0 │ 2026-07-06 09:50:46.914409+02 tuple │ 0 │ 0 │ 0 │ 2026-07-06 09:50:46.914409+02 transactionid │ 0 │ 0 │ 0 │ 2026-07-06 09:50:46.914409+02 virtualxid │ 0 │ 0 │ 0 │ 2026-07-06 09:50:46.914409+02 spectoken │ 0 │ 0 │ 0 │ 2026-07-06 09:50:46.914409+02 object │ 0 │ 0 │ 0 │ 2026-07-06 09:50:46.914409+02 userlock │ 0 │ 0 │ 0 │ 2026-07-06 09:50:46.914409+02 advisory │ 0 │ 0 │ 0 │ 2026-07-06 09:50:46.914409+02 applytransaction │ 0 │ 0 │ 0 │ 2026-07-06 09:50:46.914409+02 (12 rows)
But I could have also checked it by backend. And I could have done it from other connection:
=$ select * from pg_stat_get_backend_lock(2191568); locktype │ waits │ wait_time │ fastpath_exceeded │ stats_reset ──────────────────┼───────┼───────────┼───────────────────┼───────────── relation │ 1 │ 8849.524 │ 0 │ [null] extend │ 0 │ 0 │ 0 │ [null] frozenid │ 0 │ 0 │ 0 │ [null] page │ 0 │ 0 │ 0 │ [null] tuple │ 0 │ 0 │ 0 │ [null] transactionid │ 0 │ 0 │ 0 │ [null] virtualxid │ 0 │ 0 │ 0 │ [null] spectoken │ 0 │ 0 │ 0 │ [null] object │ 0 │ 0 │ 0 │ [null] userlock │ 0 │ 0 │ 0 │ [null] advisory │ 0 │ 0 │ 0 │ [null] applytransaction │ 0 │ 0 │ 0 │ [null] (12 rows) =$ select * from pg_stat_get_backend_lock(2191757); locktype │ waits │ wait_time │ fastpath_exceeded │ stats_reset ──────────────────┼───────┼───────────┼───────────────────┼───────────── relation │ 1 │ 4675.576 │ 0 │ [null] extend │ 0 │ 0 │ 0 │ [null] frozenid │ 0 │ 0 │ 0 │ [null] page │ 0 │ 0 │ 0 │ [null] tuple │ 0 │ 0 │ 0 │ [null] transactionid │ 0 │ 0 │ 0 │ [null] virtualxid │ 0 │ 0 │ 0 │ [null] spectoken │ 0 │ 0 │ 0 │ [null] object │ 0 │ 0 │ 0 │ [null] userlock │ 0 │ 0 │ 0 │ [null] advisory │ 0 │ 0 │ 0 │ [null] applytransaction │ 0 │ 0 │ 0 │ [null] (12 rows)
Of course stats from my test DB will not be really interesting, and, given that it's development version of Pg, I can't have real production workload on it, so data in the stats aren't exciting. But I assume you can see what information can be gathered.
Anyway, it's amazing addition, and it will be great help when diagnosing Pg. Thanks a lot to everyone involved.