New system views in PostgreSQL 19

Summary

PostgreSQL 19 adds four system views for lock contention, recovery, autovacuum priorities, and dynamic shared memory allocations. The article presents pg_stat_lock as cumulative, cluster-wide statistics with one row per lock type, reporting waits, total wait time, fastpath_exceeded, and stats_reset; waits and wait_time count only successfully acquired locks that exceeded deadlock_timeout, while fast-path overflow attempts are counted separately. Examples reset statistics, reproduce a 2.5-second table lock, and scan 140 partitions, producing 1 wait, 2201.783 milliseconds, and 422 fast-path overflows. The post describes recovery and autovacuum views, noting that autovacuum scores suggest likely priorities rather than guarantees because scoring and worker scheduling can differ. DSM registry entries expose name, type, and size; NULL means initialization failed.

Context

While documenting PostgreSQL 19 monitoring improvements and preparing a talk on Postgres observability, the author found that system views received a dedicated section in the release. The post focuses on the four new views and notes that PostgreSQL 19 was still in beta, with possible changes or reverts before general availability.

Approach / What changed

The post tours each new system view, providing column descriptions and SQL demonstrations. For pg_stat_lock, it resets cluster-wide statistics, reproduces lock contention across two psql sessions, and creates 140 partitions to demonstrate fast-path slot exhaustion. It also explains the DSM registry allocation fields and caveats around autovacuum scoring.

Takeaways

• pg_stat_lock provides cumulative, cluster-wide statistics by lockable object type, including waits, total wait time, fastpath_exceeded, and stats_reset.
• waits and wait_time only record locks successfully acquired after waiting longer than deadlock_timeout; fastpath_exceeded counts over-limit fast-path acquisition attempts without that threshold.
• pg_dsm_registry_allocations exposes DSM registry names, allocation types, and sizes; a NULL size identifies an entry that failed initialization.

Related reading