Adding locks statistics

Hi hackers,

Please find attached a patch to add a new view (namely pg_stat_lock) that provides
lock statistics.

It’s output is like the following:

postgres=# select * from pg_stat_lock;
locktype | requests | waits | timeouts | deadlock_timeouts | deadlocks | fastpath | stats_reset
------------------+----------+-------+----------+-------------------+-----------+----------+-------------------------------
relation | 612775 | 1 | 0 | 0 | 0 | 531115 | 2025-08-01 09:18:26.476275+00
extend | 3128 | 0 | 0 | 0 | 0 | 0 | 2025-08-01 09:18:26.476275+00
frozenid | 11 | 0 | 0 | 0 | 0 | 0 | 2025-08-01 09:18:26.476275+00
page | 1 | 0 | 0 | 0 | 0 | 0 | 2025-08-01 09:18:26.476275+00
tuple | 3613 | 0 | 0 | 0 | 0 | 0 | 2025-08-01 09:18:26.476275+00
transactionid | 6130 | 0 | 0 | 0 | 0 | 0 | 2025-08-01 09:18:26.476275+00
virtualxid | 15390 | 0 | 0 | 0 | 0 | 15390 | 2025-08-01 09:18:26.476275+00
spectoken | 12 | 0 | 0 | 0 | 0 | 0 | 2025-08-01 09:18:26.476275+00
object | 8393 | 0 | 0 | 0 | 0 | 0 | 2025-08-01 09:18:26.476275+00
userlock | 0 | 0 | 0 | 0 | 0 | 0 | 2025-08-01 09:18:26.476275+00
advisory | 44 | 0 | 0 | 0 | 0 | 0 | 2025-08-01 09:18:26.476275+00
applytransaction | 0 | 0 | 0 | 0 | 0 | 0 | 2025-08-01 09:18:26.476275+00

It means that it provides historical trends of locks usage per lock type.

It can be used for example for:

1. checking if "waits" is close to "requests". Then it means you usually have to
wait before acquiring the lock, which means you may have a concurrency issue.

2. lock_timeout and deadlock_timeout tuning (lock_timeout is visible only in the
logs if log_min_error_statement is set appropriately).

3. checking the "requests"/"fastpath" ratio to see if "max_locks_per_transaction"
needs tuning (see c4d5cb71d2).

If any points need more details, it might be a good idea to start sampling pg_locks.

The patch is made of 2 sub-patches:

0001 - It adds a new stat kind PGSTAT_KIND_LOCK for the lock statistics.

This new statistic kind is a fixed one because its key is the lock type
so that we know its size is LOCKTAG_LAST_TYPE + 1.

This statistic kind records the following counters:

- requests: Number of requests for this lock type.
- waits: Number of times requests for this lock type had to wait.
- timeouts: Number of times requests for this lock type had to wait longer than
lock_timeout.
- deadlock_timeouts: Number of times requests for this lock type had to wait longer
than deadlock_timeout.
- deadlocks: Number of times a deadlock occurred on this lock type.
- fastpath: Number of times this lock type was taken via fast path.

No extra details is added (like the ones, i.e relation oid, database oid, we
can find in pg_locks). The idea is to provide an idea on what the locking
behaviour looks like.

Those new counters are incremented outside of the wait events code path,
as suggested in [1].

There are no major design choices, it relies on the current statistics machinery.

0002 - It adds the pg_stat_lock view

It also adds documentation and some tests.

Remarks:

- maybe we could add some metrics related to the lock duration (we have some hints
thanks to the timeout ounters though)
- if this is merged, a next step could be to record those metrics per backend

[1]: https://www.postgresql.org/message-id/CA%2BTgmobptuUWo7X5zcQrWKh22qeAn4eL%2B%3Dwtb8_ajCOR%2B7_tcw%40mail.gmail.com

Looking forward to your feedback,

Regards,

--
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com