PG19 Hacktober: 31 Days of New Features: New Monitoring Features in PostgreSQL 19 – Part 2: WAIT FOR, New System Views and Autovacuum

In Part 1 we covered logging, WAL and I/O: log_lock_waits on by default, per-process log levels, wal_fpi_bytes and the new COPY wait events. In Part 2 we move to replication visibility, three new system views, and a smarter autovacuum, and finish with a peek at what is already on master for PostgreSQL 20.

4. WAIT FOR: read your writes on async replicas

Here is a classic problem. You write on the primary, then read the data straight back from a replica that has not caught up yet, and get a stale result.

The old fixes all have a cost:

• synchronous_commit = remote_apply: Every commit pays for it.
• Application-side polling on pg_last_wal_replay_lsn().
• Reading from the primary instead, which defeats the point of having replicas.

PostgreSQL 19 adds a WAIT FOR command that blocks on the standby until it has seen your write, and then lets you run the read.

-- on the primary
UPDATE movie SET genre = 'Dramatic' WHERE genre = 'Drama';
SELECT pg_current_wal_insert_lsn();
-- 0/0306EE20

-- on the standby
WAIT FOR LSN '0/0306EE20';
-- status: success
SELECT * FROM movie WHERE genre = 'Drama';
-- (0 rows)

The write on the primary stays asynchronous. Only the read that needs the guarantee waits.

Three stages, three modes, three wait events

A write travels through three stages on the standby, and each one maps to a WAIT FOR mode and to a wait event you can see in pg_stat_activity:

Stage	What it means	WAIT FOR mode	Wait event
1. Written	In the standby’s WAL files (may still sit in OS buffers)	standby_write	WaitForWalWrite
2. Flushed	Durable on the standby’s disk; survives a standby crash	standby_flush	WaitForWalFlush
3. Replayed	Applied to data pages; queries on the standby can see it	standby_replay (default)	WaitForWalReplay

A plain reader on the standby only sees the change after stage 3. Pick the guarantee you need: standby_write returns as soon as the record is written, standby_flush once it is durable, and standby_replay once a query can see it.

Options go in a WITH clause:

WAIT FOR LSN '0/0306EE20'
  WITH (MODE 'standby_flush', TIMEOUT '100ms', NO_THROW);

Besides the three standby modes, there is also primary_flush. TIMEOUT bounds how long you are willing to wait, and NO_THROW makes a timeout return a status instead of raising an error.

From a monitoring point of view, the new WaitFor* wait events matter too: a session parked in WaitForWalReplay is now visibly waiting on replication, not mysteriously idle.

5. New system views

pg_stat_lock: lock waits, finally with history

Before this view, you had two options for lock waits: pg_locks (a snapshot of now, with no history) or parsing log_lock_waits output yourself. pg_stat_lock Keeps cumulative statistics.

• One row per lock type, meaning the kind of lockable object (relation, transactionid, tuple, extend, advisory, and so on), not the lock mode.
• Columns include waits, wait_time (in ms), fastpath_exceeded, and stats_reset.
• Reset it with pg_stat_reset_shared('lock').
• Important: it only counts locks acquired after waiting longer than deadlock_timeout. It is not a counter of every lock wait.

That last point ties it to the log_lock_waits change from Part 1: both focus on the waits that are long enough to matter.

session 1: BEGIN; LOCK TABLE demo;
   SELECT pg_sleep(2.5); COMMIT;

session 2: LOCK TABLE demo;  -- waits
SELECT locktype, waits, wait_time,
      round(wait_time::numeric / NULLIF(waits, 0), 1) AS avg_wait_ms
FROM pg_stat_lock
WHERE waits > 0
ORDER BY wait_time DESC;
locktype | waits | wait_time | avg_wait_ms
----------+-------+-----------+-------------
relation |     1 |  2201.783 |      2201.8

pg_stat_recovery: standby state in one snapshot

To understand a standby you used to call four separate functions:

• pg_is_in_recovery()
• pg_last_wal_replay_lsn()
• pg_last_xact_replay_timestamp()
• pg_get_wal_replay_pause_state()

Each one reads shared recovery state under its own lock, so the values were never guaranteed to be consistent with each other. pg_stat_recovery (contributed by Xuneng Zhou and Shinya Kato) replaces that with one row, read as a single atomic snapshot, with all fields consistent.

A few practical notes:

• Query it on a standby.
• You need the pg_read_all_stats privilege to see the data.
• The old functions are still there, so existing tooling keeps working.

on a standby
SELECT * FROM pg_stat_recovery;

If your replication-lag dashboard stitches several of those functions together, this is the view to migrate to.

pg_stat_autovacuum_scores: what autovacuum will do next

This one needs a bit of background, so first the autovacuum change it reports on.

6. Autovacuum now has priorities

Before PostgreSQL 19, autovacuum workers processed tables in the order they found them in pg_class. A table one step from wraparound could wait behind a hundred barely-dirty ones.

Now, thanks to work by Nathan Bossart, each worker scores every table by how close it is to (or how far past) its vacuum, insert, analyze, XID-age and multixact-age thresholds. The highest score goes first, so the tables that need attention most get it first. Nathan describes it as a baby step towards smarter autovacuum workers.

Tuning: Five autovacuum_*_score_weight settings all defaulting to 1.0 weigh the components. Setting all of them to 0.0 restores the old pg_class order.

This is also where wraparound monitoring gets easier: tables approaching XID or multixact limits now score high instead of waiting in line.

Seeing the scores

The new pg_stat_autovacuum_scores view (contributed by Sami Imseih) shows what autovacuum will do next, table by table. For example, a 1000-row table that was ANALYZEd and then had 400 rows deleted:

SELECT relname, round(score::numeric, 2) AS score,
      do_vacuum, do_analyze, for_wraparound
FROM pg_stat_autovacuum_scores
WHERE schemaname = 'public' AND (do_vacuum OR do_analyze)
ORDER BY score DESC;
relname | score | do_vacuum | do_analyze | for_wraparound
---------+-------+-----------+------------+----------------
av_demo |  2.67 | t         | t          | f

The score is the highest component:

• Analyze: 400 changed rows ÷ analyze threshold (50 + 0.1 × 1000 = 150) = 2.67
• Vacuum: 400 ÷ 250 = 1.60

A component above its weight (default 1.0) fires, so this table is due for both. The highest component, analyze at 2.67, becomes the score.

• 50 — The default minimum number of changed rows required before PostgreSQL considers running ANALYZE. This comes from autovacuum_analyze_threshold.
• 0.1 — The default fraction of the table’s row count that must change. This comes from autovacuum_analyze_scale_factor (10%).
• 1000 — The original number of rows in the table.
• 400 — The number of rows that were deleted or otherwise changed since the last ANALYZE.

Caveat: treat it as a strong hint, not a guarantee. The view scores from current statistics, while workers score when they wake up.

7. Already on master for PostgreSQL 20

The monitoring story does not stop at 19. A few related improvements are already committed for PostgreSQL 20:

Area	Feature	Notes
Locks	Backend-level lock stats	pg_stat_get_backend_lock(pid): pg_stat_lock per session, joins with pg_stat_activity (Drouvot, 30 Jun)
Vacuum	Per-index progress	pg_stat_progress_vacuum gains current_index_relid, index_blks_total, index_blks_done (Rupireddy, 2 Oct)
WAL	pg_stat_io.hits for WAL	Pages served from wal_buffers count as hits under object wal: a sizing signal (Rupireddy, 16 Oct)
Logging	log_statement_max_length	Caps statement text in the log so huge literals stop flooding it (Jim Jones, Kirill Gavrilov, 3 Jul)

Note that these are on master and could still change before release.

Upgrade checklist

Before you move to PostgreSQL 19, a short list drawn from both parts:

• Check deadlock_timeout, since it now also controls what lock waits reach your log. Plan alerts for “still waiting for lock”.
• Update config validators and UIs for the type: level syntax of log_min_messages.
• Add wal_fpi_bytes to your WAL dashboards.
• Consider WAIT FOR instead of remote_apply or application-side LSN polling for read-your-writes.
• Point lag dashboards at pg_stat_recovery (with pg_read_all_stats).
• Review the autovacuum_*_score_weight settings, and watch pg_stat_autovacuum_scores.

Wrapping up

Individually, these are small changes. Together they close long-standing gaps: lock waits with history, standby state you can trust, autovacuum you can see and reason about, and fewer “why does this look idle?” moments. Stay tuned for more from the PG19Hacktober series.