PostgreSQL 19新增四个系统视图，增强监控能力

While writing about the monitoring improvements in PostgreSQL 19 and preparing my new talk on Postgres observability for PostgreSQL Conference Europe in October, I noticed the system views got their own section in this release. The last time they were similarly highlighted was in PG13 and PG14. So I decided the system views need a blog of their own to go through what has changed. PostgreSQL 19 adds four new views, pg_stat_lock, pg_stat_recovery, pg_stat_autovacuum_scores, and pg_dsm_registry_allocations, each deserving more than the one-line mention they got in my monitoring blog, so here is the tour.

Disclaimer: PostgreSQL 19 is still in beta as I write this and this area has already seen columns renamed mid-cycle; things can still change or get reverted before GA. The release notes will be the final word.

pg_stat_lock

Locks are a special interest of mine 😀 Last year, I spoke at 16 conferences with a talk called "Anatomy of Table-Level Locks in PostgreSQL". If you're interested, some of those talks were recorded and are available on YouTube. So, you can imagine how excited I was to see a new locks view in PostgreSQL 19.

Until now your options for understanding lock contention were pg_locks (a snapshot of right now, no history) and log_lock_waits output (history, but you have to parse logs to aggregate the logs yourself, though PostgreSQL 19 now turns it on by default, a change I covered in my monitoring post).

PostgreSQL 19 adds pg_stat_lock (a patch by Bertrand Drouvot §): cumulative, cluster-wide lock statistics with one row per lock type (locktype). The name locktype might be a little confusing. It is not the lock mode (such as ACCESS EXCLUSIVE or ROW SHARE) but the kind of lockable object (showing what was being locked) and there are 12 of them: relation, transactionid, tuple, extend, page, object, advisory, virtualxid, spectoken, applytransaction, frozenid, userlock.

The view itself is a thin wrapper around the new function pg_stat_get_lock().

The newly introduced pg_stat_get_lock() function does not have a docs page, since like most of the pg_stat_get_* functions, it only exists to back the view, but it's there if you want to query it directly.

Table 1: pg_stat_lock view

One important detail: waits and wait_time only count locks that were successfully acquired after waiting longer than deadlock_timeout (1 second by default), so pg_stat_lock isn't a counter of every lock wait.

Let's run some queries, I have two psql sessions to demo the lock contention. The first session takes a lock on the demo table and holds it by keeping the transaction open:

1-- session 12BEGIN;3  LOCK TABLE demo;4SELECT pg_sleep(2.5);  -- hold the lock for 2.5 seconds5COMMIT;

The second session tries to lock the same table and cannot acquire the lock (until session 1 commits):

1-- session 22  LOCK TABLE demo;   -- waits here until session 1 commits

After the 2.5 seconds, session 1 commits and session 2 finally gets its lock. Since that wait lasted longer than the default deadlock_timeout of 1s and ended in a successful acquisition, it gets counted. To see that, let’s query the view:

Before starting the demo, I reset the lock statistics with pg_stat_reset_shared('lock'), so the numbers below come from only this scenario.

pg_stat_reset_shared() resets cluster-wide statistics for a given target, such as 'wal', 'io' or 'bgwriter'. The 'lock' target is new in PostgreSQL 19, added together with the pg_stat_lock view.

a

1SELECT waits, wait_time2FROM pg_stat_lock3WHERE locktype ='relation';

1waits | wait_time2  -------+-----------3      1 |  2201.7834  (1 row)

This result shows that session 2 had to wait 2.2 seconds (out of 2.5 second session 1 hoarded the table) to acquire the lock. The 0.3 second difference reflects session 2 asking for the lock, a moment after session 1 took it.

On a real system, we won’t be chasing a single wait, so we’ll have to query lock types together with waits:

1SELECT locktype, waits, wait_time,2        round(wait_time::numeric/NULLIF(waits, 0), 1) AS avg_wait_ms3FROM pg_stat_lock4WHERE waits >05ORDERBY wait_time DESC;

1locktype | waits | wait_time | avg_wait_ms2  ----------+-------+-----------+-------------3  relation |     1 |  2201.783 |      2201.84  (1 row)

Query insight

pg_stat_lock view always returns all 12 lock types, WHERE waits > 0 filters out those without recorded contention. We then order by total wait time and calculate the average wait per lock type. A high total wait time can point to a frequently contended lock type, while a high average can reveal fewer but longer stalls.

fastpath_exceeded

I thought the fastpath_exceeded counter in the pg_stat_lock view was worth digging into, so I gave it its own section 🙂 For speed, each backend keeps a small set of fast-path slots for the most common locks that rarely conflict with anything. When a query needs more locks than the slot can hold, the extras fall back to the slower shared lock table and this counter ticks (every time, no deadlock_timeout threshold here).

fastpath_exceeded can be particularly interesting for partition-heavy workloads, where a single query may need to lock many relations.

If the fastpath_exceeded counter grows on your partition-heavy workload, that's a direct hint to raise max_locks_per_transaction. Starting from PostgreSQL 18, the fast-path slot count derives from it (before that it was fixed at 16; Christophe Pettus has an excellent write-up of that era). PostgreSQL 19 doubled the default max_locks_per_transaction from 64 to 128 (Heikki Linnakangas §).

To make the demo easy, let's create a partitioned table with more partitions than the fast-path slots can cover. I chose 140 partitions, more than the new default 128. (Postgres's own regression test uses the same trick, creating max_locks_per_transaction + 10 partitions.)

1CREATE TABLE part_demo (id int) PARTITIONBYRANGE (id);23  DO $$4BEGIN5FOR i IN1..140 LOOP6EXECUTE format(7'CREATE TABLE part_demo_%s PARTITION OF part_demo8        FOR VALUES FROM (%s) TO (%s)',9        i, (i-1)*1000, i*1000);10END LOOP;11END $$;

Then reset the counters again for a clean read, and scan the table once; a plain SELECT count(*) has to lock the parent and every partition:

1SELECT pg_stat_reset_shared('lock');2SELECTcount(*) FROM part_demo;

Now, let’s query our view:

1SELECT locktype, fastpath_exceeded2FROM pg_stat_lock3WHERE fastpath_exceeded >0;

1locktype | fastpath_exceeded2  ----------+-------------------3  relation |               4224  (1 row)

The count exceeds 140 because Postgres counts every over-limit lock acquisition attempt, and partitions can be locked during both planning and execution. The exact number may vary between runs; what matters is that it is non-zero, this workload spills out of the fast path.

pg_stat_recovery

If you have ever built a standby health check, you have probably used some or all of these functions to check the standby state: pg_is_in_recovery(), pg_last_wal_replay_lsn(), pg_last_xact_replay_timestamp(), pg_get_wal_replay_pause_state(). Each of these functions reads the shared recovery state under its own lock, at a slightly different moment. Even if you wrap them in a single view, which is what we DBAs used to do, the values are not guaranteed to be consistent with each other because replay keeps advancing between the calls.

pg_stat_recovery (Xuneng Zhou §, with a fix by Shinya Kato §) assembles all this information in one row, read as a single atomic snapshot, so all fields are consistent with each other.

Table 2: pg_stat_recovery view

1SELECT last_replayed_end_lsn, last_replayed_tli,2        recovery_last_xact_time, pause_state, promote_triggered3FROM pg_stat_recovery;

1last_replayed_end_lsn | last_replayed_tli |    recovery_last_xact_time    | pause_state | promote_triggered2  -----------------------+-------------------+-------------------------------+-------------+-------------------3  0/03001E20            |                 1 | 2026-08-31 15:43:27.762621+02 | not paused  | f4  (1 row)

💡

The pg_stat_recovery view also exposes information that previously had no SQL interface: the start LSN of the last replayed record, the replay timelines, the end position of the record currently being replayed, and whether a promotion has been triggered. Previously, SQL could only tell you when a promotion had completed through pg_is_in_recovery() returning false, not that one was already underway.

A few practical notes:

• The view returns no rows on a primary, so you’ll need to query it on a standby.
• You need the pg_read_all_stats privilege to see the data.
• The old functions are still there, this is purely additive.

If you maintain HA tooling, this is a good time to update it. If you run health checks every few seconds, getting a consistent view of the recovery state with one query instead of several is a small but nice win.

pg_stat_autovacuum_scores

I will cover two changes in this section: a new autovacuum behaviour and a view (pg_stat_autovacuum_scores) to watch it.

First, the behavior. PostgreSQL 19 changes how autovacuum decides what to work on first. Before PostgreSQL 19, autovacuum processed tables in the order it found them in pg_class. Now, each worker calculates a score for every table based on how close it is to (or how far past) its autovacuum thresholds: XID age, multixact age, dead tuples, inserts and analyze staleness. The highest score wins, so tables needing attention most are processed first.

Five new autovacuum_*_score_weight parameters (all defaulting to 1.0) let you set weights for the components. Setting all of them to 0.0 restores the pre-19 ordering. Nathan Bossart’s commit calls this "a baby step towards smarter autovacuum workers".

The second change surfaces these stats: pg_stat_autovacuum_scores (Sami Imseih §) exposes these scores per table (including TOAST tables and system catalogs) in the current database, revealing what tables autovacuum prioritizes.

Table 3: pg_stat_autovacuum_scores view

Let’s run a few queries to see it working. I created a 1000 row table, analyzed it and deleted 400 rows:

1CREATE TABLE av_demo AS2SELECT g AS id, md5(g::text) AS payload FROM generate_series(1, 1000) g;3  ANALYZE av_demo;4DELETEFROM av_demo WHERE id <=400;

Then asked the pg_stat_autovacuum_scores view what autovacuum thinks of my tables:

1SELECT relname, round(score::numeric,2) AS score,2        do_vacuum, do_analyze, for_wraparound3FROM pg_stat_autovacuum_scores4WHERE schemaname ='public'AND (do_vacuum OR do_analyze)5ORDERBY score DESC;

1relname | score | do_vacuum | do_analyze | for_wraparound2  ---------+-------+-----------+------------+----------------3  av_demo |  2.67 | t         | t          | f4  (1 row)

Where does 2.67 come from? Autovacuum decides when to act using thresholds computed from the table's row count. For analyze, the default is 50 + 0.1 × rows → for our 1000 row table, that's 150. We changed 400 rows, so the analyze score is 400 / 150 = 2.67.

The deletes crossed the vacuum threshold too: 50 + 0.2 × 1000 = 250, giving 400 / 250 = 1.60. Since the overall score is the highest component, av_demo gets 2.67 and qualifies for both vacuum and analyze.

for_wraparound derives from a different calculation entirely, not row changes, but age. As in, how far the table's oldest unfrozen transaction ID has drifted toward autovacuum_freeze_max_age (200 million transactions by default). Our freshly created table is nowhere near that threshold, so it is false.

pg_stat_autovacuum_scores computes scores from current statistics while autovacuum workers score the tables whenever they wake up. The two moments can differ, so treat the view as a strong hint of what autovacuum will prioritize, not a guarantee.

pg_dsm_registry_allocations

A smaller one: extensions increasingly allocate shared memory through the DSM registry, which spares them from needing shared_preload_libraries (and a restart) just to get shared memory. Until now that memory wasn't visible from SQL.

What is the DSM registry?

DSM stands for dynamic shared memory: shared memory created at runtime, unlike the main shared memory area, which is allocated once at server start (which is why extensions needing shared state traditionally required shared_preload_libraries and a restart). The DSM registry, added in PostgreSQL 17 (Nathan Bossart §), lets backends create, find and attach to these shared memory segments by name. This means extensions get shared state with a plain CREATE EXTENSION, no restart. More in the docs: Requesting Shared Memory After Startup.

This new pg_dsm_registry_allocations view (Florents Tselai §, extended by Nathan Bossart §) lists each registry entry with its name, type (segment, area or hash) and size. A NULL size means the entry failed to initialize.

Table 4: pg_dsm_registry_allocations view

Outro

Thanks for reading this far! I hope the new system views excite you as much as they do me. We’re all counting down the days to PostgreSQL 19 and I’m already wishing everyone happy upgrades!

I will be talking about PostgreSQL 19 observability at PostgreSQL Conference Europe in October, and several of the topics I covered here will make an appearance. Come say hi if you plan to be in Valencia! 👋