PostgreSQL 19: Unpacking New System Views

Deep Dive into PG19 Observability

The article provides a commendable deep dive into the new system views in PostgreSQL 19, particularly focusing on pg_stat_lock, pg_stat_recovery, and pg_stat_autovacuum_scores. The author's expertise in PostgreSQL locks shines through, making the explanation of pg_stat_lock and its nuances, like the deadlock_timeout condition for counting waits and the fastpath_exceeded counter, particularly insightful. The practical examples and direct queries to illustrate the functionality are excellent. The introduction of pg_stat_recovery as a consolidated, atomic snapshot for standby health checks is a significant improvement over piecing together information from disparate functions, offering greater consistency for HA tooling. Similarly, the pg_stat_autovacuum_scores view, coupled with the new scoring mechanism, represents a 'baby step towards smarter autovacuum workers,' providing much-needed transparency into prioritization logic and offering fine-grained control via new weighting parameters. The article effectively highlights the 'why' behind these changes, explaining how they address existing pain points in monitoring and diagnostics.

Key Points

• PostgreSQL 19 introduces four new system views: pg_stat_lock, pg_stat_recovery, pg_stat_autovacuum_scores, and pg_dsm_registry_allocations.
• pg_stat_lock provides cumulative, cluster-wide statistics on lock contention, including wait counts and total wait times, specifically for locks that exceeded deadlock_timeout and were successfully acquired, as well as fastpath_exceeded counts.
• pg_stat_recovery offers a consistent, atomic snapshot of standby server recovery state, consolidating information previously scattered across multiple functions, crucial for HA tooling and health checks.
• pg_stat_autovacuum_scores exposes the calculated scores for tables based on various autovacuum thresholds (XID age, multixact age, dead tuples, etc.), enabling administrators to understand autovacuum prioritization and tune it with new autovacuum_*_score_weight parameters.
• The fastpath_exceeded counter in pg_stat_lock indicates when a backend exceeds its fast-path lock slot limit, suggesting a potential need to increase max_locks_per_transaction, especially in partition-heavy workloads.

---

📖 Source: New system views in PostgreSQL 19