PostgreSQL 19 monitoring - new views, wait events and log controls

19’s monitoring changes are not one feature; they are a dozen small answers to questions that used to end in “you cannot see that from inside the database”. This page is organised as a runbook: the question you are trying to answer, and what 19 gives you to answer it.

“Which lock type is serialising my writers?” - pg_stat_lock

pg_locks tells you who holds what right now. New in 19, pg_stat_lock accumulates contention per lock type, cluster-wide, since the last reset:

SELECT locktype, waits, wait_time, fastpath_exceeded
FROM pg_stat_lock ORDER BY wait_time DESC;

The subtlety: waits only counts acquisitions that waited longer than deadlock_timeout - a noisy-but-fast queue never registers. It measures locks that hurt, not locks that exist. pg_stat_get_lock() is the function form.

“Is the standby promoted, paused or still replaying?” - pg_stat_recovery

At most one row, and empty unless the server is in recovery and you hold pg_read_all_stats:

SELECT promote_triggered, pause_state,
       last_replayed_end_lsn, replay_end_lsn,
       recovery_last_xact_time, current_chunk_start_time
FROM pg_stat_recovery;

pause_state reports not paused / pause requested / paused; recovery_last_xact_time is the commit time on the primary of the last replayed transaction; current_chunk_start_time marks when replay caught up with the newest WAL chunk received. Before 19 this state was spread across pg_is_in_recovery(), pg_last_wal_replay_lsn() and log parsing.

“Is the apply side of replication healthy?” - subscription and slot counters

• pg_stat_replication_slots gains mem_exceeded_count (times logical decoding exceeded logical_decoding_work_mem) and the slot-sync trio slotsync_skip_count, slotsync_last_skip, slotsync_skip_reason - the last explains why a standby skipped synchronising a failover slot, which previously failed silently.
• pg_stat_subscription_stats renames sync_error_count to sync_table_error_count and adds sync_seq_error_count for sequence sync errors, plus update_deleted counting updates ignored because the row was concurrently deleted (needs retain_dead_tuples - see replication).
• pg_stat_wal_receiver.status gains a connecting value.

“What is the session waiting on?” - new wait events

“How much WAL is full-page images?” - wal_fpi_bytes

pg_stat_wal gains wal_fpi_bytes (and pg_stat_get_backend_wal() the per-backend equivalent), and VACUUM/ANALYZE logging now reports full-page-write bytes too. Checkpoints after bulk writes are where this bites: wal_fpi_bytes / wal_bytes tells you how much of your WAL volume is page images rather than records.

SELECT wal_bytes, wal_fpi_bytes,
       round(100.0 * wal_fpi_bytes / wal_bytes, 1) AS fpi_pct
FROM pg_stat_wal;

“Who vacuumed this, and how hard?” - progress view columns

pg_stat_progress_vacuum gains started_by (including autovacuum_wraparound) and mode; pg_stat_progress_analyze gains started_by. pg_stat_autovacuum_scores shows why autovacuum picked the table it did - both are covered on the autovacuum page.

“When were these counters reset?” - stats_reset everywhere

stats_reset columns are added to pg_stat_all_tables, pg_stat_all_indexes, pg_statio_all_sequences (and their sys/user variants), pg_stat_user_functions and pg_stat_database_conflicts - so “is this counter meaningful since last night” is answerable without tracking resets yourself. pg_stat_progress_basebackup gains backup_type (full/incremental); pg_stats/pg_stats_ext gain OID columns; and pg_dsm_registry_allocations reports dynamic shared memory usage.

“Can I turn the noise down on just the noisy process?” - logging changes

• log_min_messages accepts a process-type:level syntax, so a chatty component (say, the checkpointer or a walsender) can be quieter than the rest. A value without a colon keeps the old all-types behaviour.
• log_lock_waits now defaults on - sessions waiting past deadlock_timeout log the lock automatically. That is a real behaviour change for quiet systems: more log volume by default, more signal for contention.
• log_autovacuum_min_duration now covers vacuum only; log_autoanalyze_min_duration is the separate control for autovacuum-triggered analyses.
• debug_print_raw_parse logs raw parse trees (also on at debug level 3+); remote-server messages (replication, postgres_fdw, dblink) now appear in local log format; pg_get_multixact_stats() reports multixact activity.

Wraparound warning: 100 million, not 40

The warning that a transaction-ID or multixact wraparound is approaching now fires below 100 million remaining IDs instead of 40 million - to clients and the server log. If your alerting keys on the old threshold, expect warnings to appear sooner on the same workload; if it keys on the message, nothing changes.

What changed, and in which release

Where it breaks

• Thresholds silently different on upgrade - log_lock_waits on by default means more log volume; the 100-million wraparound warning fires earlier. Both are deliberate, both surprise.
• pg_stat_lock looking empty on a busy system - sub-deadlock_timeout waits are not counted; absence of rows means no painful waits, not no contention.
• Renames breaking dashboards - sync_error_count and BUFFERPIN are gone under those names; alert on the new ones.
• pg_stat_recovery returning nothing - it returns a row only in recovery, and only for pg_read_all_stats holders; an empty result on a standby with the right privileges is itself a signal.
• Beta caveat - one column was already renamed mid-cycle; others could follow before GA.

What this is not for

• Real-time per-session state - cumulative views lag by process flush (up to about a second); pg_stat_activity remains the live view.
• Replacing external metrics - these views feed an exporter; they do not retain history across resets or restarts (stats reset on crash recovery).