PostgreSQL 19 introduces the pg_stat_lock view, which provides cumulative statistics about lock activity across the database cluster. It helps database administrators investigate lock contention, measure time spent waiting for locks, and identify situations where relation locks exceed the available fast-path slots.
In this article, I will demonstrate these capabilities through two practical scenarios.
postgres=# \d pg_stat_lock View "pg_catalog.pg_stat_lock" Column | Type | Collation | Nullable | Default -------------------+--------------------------+-----------+----------+--------- locktype | text | | | waits | bigint | | | wait_time | double precision | | | fastpath_exceeded | bigint | | | stats_reset | timestamp with time zone | | | The view contains the following columns:
locktype: Type of lockable object, such as a relation or transaction ID
waits: Number of qualifying lock waits
wait_time: Total time spent waiting for locks, in milliseconds
fastpath_exceeded: Number of times a lock could not be acquired through the fast path because the available slots were exceeded
stats_reset: Time when the statistics were last reset
Initially, all counters are zero in my test environment. The view returns 12 rows, one for each lock type.
postgres=# SELECT * FROM pg_stat_lock; locktype | waits | wait_time | fastpath_exceeded | stats_reset ------------------+-------+-----------+-------------------+------------------------------- relation | 0 | 0 | 0 | 2026-10-26 08:56:30.602553+03 extend | 0 | 0 | 0 | 2026-10-26 08:56:30.602553+03 frozenid | 0 | 0 | 0 | 2026-10-26 08:56:30.602553+03 page | 0 | 0 | 0 | 2026-10-26 08:56:30.602553+03 tuple | 0 | 0 | 0 | 2026-10-26 08:56:30.602553+03 transactionid | 0 | 0 | 0 | 2026-10-26 08:56:30.602553+03 virtualxid | 0 | 0 | 0 | 2026-10-26 08:56:30.602553+03 spectoken | 0 | 0 | 0 | 2026-10-26 08:56:30.602553+03 object | 0 | 0 | 0 | 2026-10-26 08:56:30.602553+03 userlock | 0 | 0 | 0 | 2026-10-26 08:56:30.602553+03 advisory | 0 | 0 | 0 | 2026-10-26 08:56:30.602553+03 applytransaction | 0 | 0 | 0 | 2026-10-26 08:56:30.602553+03 (12 rows) An important detail is that waits and wait_time only include waits that exceed deadlock_timeout and end with successful lock acquisition. In my environment, the configured threshold is one second:
vahiddb=# SHOW deadlock_timeout; deadlock_timeout ------------------ 1s (1 row) Investigating Lock Waits
In this scenario, I will demonstrate how pg_stat_lock records lock waits caused by concurrent transactions.
Concurrent Updates
I will use an existing table named tbl_counter. The table contains a counter row that both sessions attempt to update.
Before starting the experiment, I reset the lock statistics:
vahiddb=# SELECT pg_stat_reset_shared('lock'); pg_stat_reset_shared ---------------------- (1 row) Session 1
Start a transaction and update the counter:
--session 1: vahiddb=# begin; update tbl_counter set counter=counter+1; BEGIN UPDATE 1 The update completes, but the transaction remains open. PostgreSQL retains the relevant locks until the transaction ends.
Session 2
While Session 1 remains open, execute the same update:
--session 2: vahiddb=# update tbl_counter set counter=counter+1; The second update waits because Session 1 has modified the same row and has not yet committed.
At this point, querying pg_stat_lock shows that the counters have not increased for this wait:
vahiddb=# SELECT * FROM pg_stat_lock; locktype | waits | wait_time | fastpath_exceeded | stats_reset ------------------+-------+-----------+-------------------+------------------------------- relation | 0 | 0 | 0 | 2026-10-26 08:56:30.602553+03 extend | 0 | 0 | 0 | 2026-10-26 08:56:30.602553+03 frozenid | 0 | 0 | 0 | 2026-10-26 08:56:30.602553+03 page | 0 | 0 | 0 | 2026-10-26 08:56:30.602553+03 tuple | 0 | 0 | 0 | 2026-10-26 08:56:30.602553+03 transactionid | 0 | 0 | 0 | 2026-10-26 08:56:30.602553+03 virtualxid | 0 | 0 | 0 | 2026-10-26 08:56:30.602553+03 spectoken | 0 | 0 | 0 | 2026-10-26 08:56:30.602553+03 object | 0 | 0 | 0 | 2026-10-26 08:56:30.602553+03 userlock | 0 | 0 | 0 | 2026-10-26 08:56:30.602553+03 advisory | 0 | 0 | 0 | 2026-10-26 08:56:30.602553+03 applytransaction | 0 | 0 | 0 | 2026-10-26 08:56:30.602553+03 (12 rows) The waits and wait_time values remain zero in the displayed statistics.
Why?
The second transaction is still waiting. PostgreSQL records this qualifying wait in the cumulative statistics when the lock is successfully acquired after the wait ends, rather than incrementing the counters continuously while the transaction is blocked.
Session 1
Commit the first transaction:
vahiddb=*# commit; COMMIT Session 2 can now proceed and complete its update.
Query the statistics again:
vahiddb=# SELECT * FROM pg_stat_lock; locktype | waits | wait_time | fastpath_exceeded | stats_reset ------------------+-------+------------+-------------------+------------------------------- relation | 0 | 0 | 0 | 2026-10-26 08:56:30.602553+03 extend | 0 | 0 | 0 | 2026-10-26 08:56:30.602553+03 frozenid | 0 | 0 | 0 | 2026-10-26 08:56:30.602553+03 page | 0 | 0 | 0 | 2026-10-26 08:56:30.602553+03 tuple | 0 | 0 | 0 | 2026-10-26 08:56:30.602553+03 transactionid | 1 | 332098.097 | 0 | 2026-10-26 08:56:30.602553+03 virtualxid | 0 | 0 | 0 | 2026-10-26 08:56:30.602553+03 spectoken | 0 | 0 | 0 | 2026-10-26 08:56:30.602553+03 object | 0 | 0 | 0 | 2026-10-26 08:56:30.602553+03 userlock | 0 | 0 | 0 | 2026-10-26 08:56:30.602553+03 advisory | 0 | 0 | 0 | 2026-10-26 08:56:30.602553+03 applytransaction | 0 | 0 | 0 | 2026-10-26 08:56:30.602553+03 (12 rows) The recorded wait_time is approximately 332 seconds.
The important observation is that the wait was recorded under transactionid, rather than relation. Although both sessions executed an UPDATE against the same table, the second transaction waited for the transaction ID of the transaction holding the conflicting row lock.
This illustrates why the locktype column is useful: it identifies the type of lockable object associated with the recorded wait, rather than simply naming the SQL operation that caused it.
Table-Level Lock Contention
Now let's generate a wait involving a relation lock.
First, reset the statistics again:
vahiddb=# SELECT pg_stat_reset_shared('lock'); pg_stat_reset_shared ---------------------- (1 row) Session 1
Acquire an exclusive table lock and keep the transaction open:
--session 1: vahiddb=# BEGIN; LOCK TABLE tbl_counter IN ACCESS EXCLUSIVE MODE; BEGIN LOCK TABLE The ACCESS EXCLUSIVE lock conflicts with the ACCESS SHARE lock required by a normal SELECT.
Session 2
While Session 1 holds the lock, execute:
--session 2: vahiddb=# select * from tbl_counter; The query waits until Session 1 releases its lock.
Session 1
Commit the transaction:
vahiddb=*# commit; COMMIT After Session 2 completes, query the statistics:
vahiddb=# SELECT * FROM pg_stat_lock; locktype | waits | wait_time | fastpath_exceeded | stats_reset ------------------+-------+-----------+-------------------+------------------------------- relation | 1 | 63338.103 | 0 | 2026-10-26 09:34:16.935286+03 extend | 0 | 0 | 0 | 2026-10-26 09:34:16.935286+03 frozenid | 0 | 0 | 0 | 2026-10-26 09:34:16.935286+03 page | 0 | 0 | 0 | 2026-10-26 09:34:16.935286+03 tuple | 0 | 0 | 0 | 2026-10-26 09:34:16.935286+03 transactionid | 0 | 0 | 0 | 2026-10-26 09:34:16.935286+03 virtualxid | 0 | 0 | 0 | 2026-10-26 09:34:16.935286+03 spectoken | 0 | 0 | 0 | 2026-10-26 09:34:16.935286+03 object | 0 | 0 | 0 | 2026-10-26 09:34:16.935286+03 userlock | 0 | 0 | 0 | 2026-10-26 09:34:16.935286+03 advisory | 0 | 0 | 0 | 2026-10-26 09:34:16.935286+03 applytransaction | 0 | 0 | 0 | 2026-10-26 09:34:16.935286+03 (12 rows) The relation row records one qualifying wait, with approximately 63.3 seconds of accumulated wait time.
This example differs from the concurrent-update scenario. The ACCESS EXCLUSIVE lock directly prevents another session from acquiring the relation lock required by its SELECT statement. Consequently, the recorded wait appears under relation.
The wait duration depends on the actual time the second session spends waiting. PostgreSQL only includes waits exceeding deadlock_timeout that subsequently end in successful acquisition.
Detecting Fast-Path Lock Overflow
PostgreSQL uses a fast-path mechanism to acquire certain relation locks efficiently. When the available fast-path slots are insufficient for a backend's relation-lock requirements, additional lock acquisitions can use the regular shared lock table instead.
The fastpath_exceeded counter helps identify this activity. Unlike waits and wait_time, it does not depend on a lock wait exceeding deadlock_timeout.
This is particularly interesting for partition-heavy workloads, where a single query can access thousands of relations.
First, check the current value of max_locks_per_transaction:
vahiddb=# SHOW max_locks_per_transaction; max_locks_per_transaction --------------------------- 128 (1 row) This setting helps determine the capacity used for lock management. It is not a direct limit on the total number of locks a transaction can acquire, and the fast-path slot limit should not be interpreted as simply being identical to this value.
To generate a workload that accesses many relations, I created a range-partitioned table with 5,000 partitions.
vahiddb=# CREATE TABLE tbl_parts ( id integer ) PARTITION BY RANGE (id); CREATE TABLE Next, I created the partitions using a DO block:
vahiddb=# DO $$ BEGIN FOR i IN 1..5000 LOOP EXECUTE format( 'CREATE TABLE tbl_parts_%s PARTITION OF tbl_parts FOR VALUES FROM (%s) TO (%s)', i, (i - 1) * 1000, i * 1000 ); END LOOP; END $$; DO Time: 68588.738 ms (01:08.589) The operation completed successfully and took approximately 68.6 seconds in my environment.
The table is empty, but its partition structure provides a useful test case because a query against the parent can require locks on many relations.
Before executing the test query, I reset the lock statistics:
vahiddb=# SELECT pg_stat_reset_shared('lock'); pg_stat_reset_shared ---------------------- (1 row) At this stage, the fastpath_exceeded counters were zero.
Resetting the statistics makes it easier to attribute subsequent counter changes to the experiment. This function resets cluster-wide lock statistics, so it should be used carefully on shared or production servers.
Now, execute a simple aggregate query:
vahiddb=# SELECT count(*) FROM tbl_parts; count ------- 0 (1 row) Although the table contains no rows, PostgreSQL still needs to process the partitioned relation and its partitions. The query therefore provides a way to investigate relation-lock activity without inserting thousands of rows.
Next, query pg_stat_lock again:
vahiddb=# SELECT * FROM pg_stat_lock; locktype | waits | wait_time | fastpath_exceeded | stats_reset ------------------+-------+-----------+-------------------+------------------------------- relation | 0 | 0 | 92796 | 2026-10-26 10:13:47.582012+03 extend | 0 | 0 | 0 | 2026-10-26 10:13:47.582012+03 frozenid | 0 | 0 | 0 | 2026-10-26 10:13:47.582012+03 page | 0 | 0 | 0 | 2026-10-26 10:13:47.582012+03 tuple | 0 | 0 | 0 | 2026-10-26 10:13:47.582012+03 transactionid | 0 | 0 | 0 | 2026-10-26 10:13:47.582012+03 virtualxid | 0 | 0 | 0 | 2026-10-26 10:13:47.582012+03 spectoken | 0 | 0 | 0Source：https://dev.to/vahidusefzadeh/postgresql-19-monitoring-lock-contention-and-fast-path-overflow-with-pgstatlock-454Related posts
SOLID Principles Explained: 5 Essential Principles for Writing Clean, Maintainable Code
How to Build a Professional Resume in 2026: Best Resume Formats, Templates, and ATS Tips
How to Explain Your Project in a Technical Interview
Stop Calling It "Refactoring" When You Are Just Moving The Bugs Around
PostgreSQL migrations that lock a busy table
