PostgreSQL 19 Beta introduces several new system views that improve observability, monitoring, logical replication visibility, storage management insights, recovery monitoring, and maintenance progress tracking.
These views provide database administrators and developers with deeper visibility into PostgreSQL internals without requiring direct access to low-level system functions.
In this article, we will explore the following new PostgreSQL 19 views:
- pg_dsm_registry_allocations
- pg_publication_sequences
- pg_stat_autovacuum_scores
- pg_stat_lock
- pg_stat_progress_data_checksums
- pg_stat_progress_repack
- pg_stat_recovery
1. pg_dsm_registry_allocations
The pg_dsm_registry_allocations view displays allocations currently registered in PostgreSQL's Dynamic Shared Memory (DSM) registry.
Dynamic Shared Memory is used by PostgreSQL extensions and background workers to share data between processes.
This view helps administrators identify DSM allocations currently active inside the server.
View Definition
SELECT *
FROM pg_views
WHERE viewname = 'pg_dsm_registry_allocations';
Output:
schemaname | viewname | viewowner | definition
------------+-----------------------------+-----------+----------------------------------------------------------------------------------------------
pg_catalog | pg_dsm_registry_allocations | cybrosys | SELECT name, +
| | | type, +
| | | size +
| | | FROM pg_get_dsm_registry_allocations() pg_get_dsm_registry_allocations(name, type, size);
(1 row)
By default when you check the view, it should be empty.
select * from pg_dsm_registry_allocations ;
Result :
name | type | size
------+------+------
(0 rows)
Generating Data
Create the extension for allocating dynamic shared memory.
create extension pg_prewarm ;
Check the result from the view.
select * from pg_dsm_registry_allocations ;
Output :
name | type | size
-------------+---------+------
autoprewarm | segment | 44
(1 row)
The pg_prewarm extension creates an autoprewarm worker that stores information in Dynamic Shared Memory.
The view exposes:
- Allocation name (autoprewarm)
- Allocation type (segment)
- Memory size (44 bytes)
This allows administrators to understand which extensions are consuming DSM resources.
2. pg_publication_sequences
Logical replication traditionally focused on table data.
PostgreSQL 19 introduces pg_publication_sequences, allowing administrators to inspect which sequences belong to a publication.
This is especially useful because applications frequently depend on sequence values generated by SERIAL and IDENTITY columns.
View Definition
SELECT *
FROM pg_views
WHERE viewname = 'pg_publication_sequences';
Result :
schemaname | viewname | viewowner | definition
------------+--------------------------+-----------+-------------------------------------------------------------------------
pg_catalog | pg_publication_sequences | cybrosys | SELECT p.pubname, +
| | | n.nspname AS schemaname, +
| | | c.relname AS sequencename +
| | | FROM pg_publication p, +
| | | LATERAL pg_get_publication_sequences((p.pubname)::text) gps(relid),+
| | | (pg_class c +
| | | JOIN pg_namespace n ON ((n.oid = c.relnamespace))) +
| | | WHERE (c.oid = gps.relid);
(1 row)
Initially this view should be empty.
SELECT * FROM pg_publication;
Result :
oid | pubname | pubowner | puballtables | puballsequences | pubinsert | pubupdate | pubdelete | pubtruncate | pubviaroot | pubgencols
-----+---------+----------+--------------+-----------------+-----------+-----------+-----------+-------------+------------+------------
(0 rows)
Generating Data
Create a publication that includes all sequences.
CREATE PUBLICATION pubseq FOR ALL SEQUENCES;
Verify publication:
SELECT * FROM pg_publication;
Output:
oid | pubname | pubowner | puballtables | puballsequences | pubinsert | pubupdate | pubdelete | pubtruncate | pubviaroot | pubgencols
-------+---------+----------+--------------+-----------------+-----------+-----------+-----------+-------------+------------+------------
32866 | pubseq | 10 | f | t | t | t | t | t | f | n
(1 row)
Now query:
SELECT * FROM pg_publication_sequences;
Result :
pubname | schemaname | sequencename
---------+------------+--------------
pubseq | public | test_seq
(1 row)
The view provides visibility into sequence replication.
Benefits include:
- Verifying sequence replication setup
- Troubleshooting logical replication
This removes the need to manually inspect system catalogs.
3. pg_stat_autovacuum_scores
One of the most significant additions in PostgreSQL 19 is the pg_stat_autovacuum_scores view.
Previously, administrators could see whether an autovacuum had run, but determining why a table was selected required internal calculations.
This view exposes PostgreSQL's autovacuum scoring mechanism.
View Definition
SELECT *
FROM pg_views
WHERE viewname = 'pg_stat_autovacuum_scores';
Result :
schemaname | viewname | viewowner | definition
------------+---------------------------+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------
------------------
pg_catalog | pg_stat_autovacuum_scores | cybrosys | SELECT s.oid AS relid,
+
| | | n.nspname AS schemaname,
+
| | | c.relname,
+
| | | s.score,
+
| | | s.xid_score,
+
| | | s.mxid_score,
+
| | | s.vacuum_score,
+
| | | s.vacuum_insert_score,
+
| | | s.analyze_score,
+
| | | s.do_vacuum,
+
| | | s.do_analyze,
+
| | | s.for_wraparound
+
| | | FROM ((pg_stat_get_autovacuum_scores() s(oid, score, xid_score, mxid_score, vacuum_score, vacuum_insert_score, analyze_score, do_vacuum, do_analyze
, for_wraparound)+
| | | JOIN pg_class c ON ((c.oid = s.oid)))
+
| | | LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)));
(1 row)
Query Example
SELECT *
FROM pg_stat_autovacuum_scores
LIMIT 1;
Output:
relid | schemaname | relname | score | xid_score | mxid_score | vacuum_score | vacuum_insert_score | analyze_score | do_vacuum | do_analyze | for_wraparound
-------+------------+-----------+---------+-----------+------------+--------------+---------------------+---------------+-----------+------------+----------------
24583 | public | test_lock | 2.5e-08 | 2.5e-08 | 0 | 0 | 0 | 0 | f | f | f
(1 row)
The view reveals how PostgreSQL internally decides whether a table should be vacuumed or analyzed.
This helps:
- Diagnose autovacuum behavior
- Predict future vacuum activity
- Troubleshoot table bloat
- Investigate transaction wraparound risks
For DBAs, this is one of the most useful monitoring additions in PostgreSQL 19.
4. pg_stat_lock
Lock contention is a common source of performance problems.
PostgreSQL 19 introduces pg_stat_lock, which provides aggregated lock wait statistics.
Instead of analyzing individual lock records, administrators can quickly identify which lock types are causing waits.
View Definition
SELECT *
FROM pg_views
WHERE viewname = 'pg_stat_lock';
Result :
schemaname | viewname | viewowner | definition
------------+--------------+-----------+-------------------------------------------------------------------------------------------
pg_catalog | pg_stat_lock | cybrosys | SELECT locktype, +
| | | waits, +
| | | wait_time, +
| | | fastpath_exceeded, +
| | | stats_reset +
| | | FROM pg_stat_get_lock() l(locktype, waits, wait_time, fastpath_exceeded, stats_reset);
(1 row)
Query Example
SELECT * FROM pg_stat_lock;
Output:
locktype | waits | wait_time | fastpath_exceeded | stats_reset
------------------+-------+-----------+-------------------+----------------------------------
relation | 1 | 9638 | 0 | 2026-06-08 08:55:01.784101+05:30
extend | 0 | 0 | 0 | 2026-06-08 08:55:01.784101+05:30
frozenid | 0 | 0 | 0 | 2026-06-08 08:55:01.784101+05:30
page | 0 | 0 | 0 | 2026-06-08 08:55:01.784101+05:30
tuple | 0 | 0 | 0 | 2026-06-08 08:55:01.784101+05:30
transactionid | 0 | 0 | 0 | 2026-06-08 08:55:01.784101+05:30
virtualxid | 0 | 0 | 0 | 2026-06-08 08:55:01.784101+05:30
spectoken | 0 | 0 | 0 | 2026-06-08 08:55:01.784101+05:30
object | 0 | 0 | 0 | 2026-06-08 08:55:01.784101+05:30
userlock | 0 | 0 | 0 | 2026-06-08 08:55:01.784101+05:30
advisory | 0 | 0 | 0 | 2026-06-08 08:55:01.784101+05:30
applytransaction | 0 | 0 | 0 | 2026-06-08 08:55:01.784101+05:30
(12 rows)
This view helps identify:
- Lock bottlenecks
- Frequently contested lock types
- Excessive lock wait times
- Fast-path lock overflows
It is especially useful when diagnosing concurrency issues in production environments.
5. Pg_stat_progress_data_checksums
View definition
select * from pg_views where viewname = 'pg_stat_progress_data_checksums';
PostgreSQL 19 allows enabling and disabling data checksums while the server is running.
The pg_stat_progress_data_checksums view tracks the progress of these operations.
This is similar to existing progress views for VACUUM and CREATE INDEX.
Monitoring Setup
Terminal 1
Connect to PostgreSQL:
\c postgres
Run:
SELECT * FROM pg_stat_progress_data_checksums;
Enable automatic refresh:
\watch
Terminal 2
Disable checksums:
SELECT pg_disable_data_checksums();
Enable checksums:
SELECT pg_enable_data_checksums();
Result from terminal 1
Tuesday 09 June 2026 07:55:14 PM (every 2s)
pid | datid | datname | phase | databases_total | databases_done | relations_total | relations_done | blocks_total | blocks_done
---------+-------+----------+----------+-----------------+----------------+-----------------+----------------+--------------+-------------
1656880 | 0 | | enabling | 4 | 0 | | | |
1656881 | 5 | postgres | enabling | 0 | 0 | 304 | 9 | 46729 | 10751
(2 rows)
Tuesday 09 June 2026 07:55:16 PM (every 2s)
pid | datid | datname | phase | databases_total | databases_done | relations_total | relations_done | blocks_total | blocks_done
---------+-------+----------+----------+-----------------+----------------+-----------------+----------------+--------------+-------------
1656880 | 0 | | enabling | 4 | 0 | | | |
1656881 | 5 | postgres | enabling | 0 | 0 | 304 | 12 | 46781 | 25166
(2 rows)
Tuesday 09 June 2026 07:55:18 PM (every 2s)
pid | datid | datname | phase | databases_total | databases_done | relations_total | relations_done | blocks_total | blocks_done
---------+-------+----------+----------+-----------------+----------------+-----------------+----------------+--------------+-------------
1656880 | 0 | | enabling | 4 | 0 | | | |
1656881 | 5 | postgres | enabling | 0 | 0 | 304 | 12 | 46781 | 43855
(2 rows)
Tuesday 09 June 2026 07:55:20 PM (every 2s)
pid | datid | datname | phase | databases_total | databases_done | relations_total | relations_done | blocks_total | blocks_done
---------+-------+----------+----------+-----------------+----------------+-----------------+----------------+--------------+-------------
1656880 | 0 | | enabling | 4 | 0 | | | |
1656881 | 5 | postgres | enabling | 0 | 0 | 304 | 15 | 46778 | 20217
(2 rows)
Tuesday 09 June 2026 07:55:22 PM (every 2s)
pid | datid | datname | phase | databases_total | databases_done | relations_total | relations_done | blocks_total | blocks_done
---------+-------+----------+----------+-----------------+----------------+-----------------+----------------+--------------+-------------
1656880 | 0 | | enabling | 4 | 0 | | | |
1656881 | 5 | postgres | enabling | 0 | 0 | 304 | 15 | 46778 | 22265
(2 rows)
Tuesday 09 June 2026 07:55:24 PM (every 2s)
pid | datid | datname | phase | databases_total | databases_done | relations_total | relations_done | blocks_total | blocks_done
---------+-------+---------+--------------------+-----------------+----------------+-----------------+----------------+--------------+-------------
1656880 | 0 | | waiting on barrier | 4 | 4 | | | |
(1 row)
The view shows:
- Current operation phase
- Database-level progress
- Relation-level progress
- Block-level progress
Administrators can accurately estimate completion time for checksum operations.
6. pg_stat_progress_repack
View definition
select * from pg_views where viewname = 'pg_stat_progress_repack';
PostgreSQL 19 introduces a dedicated progress view for table repacking operations.
This allows administrators to track REPACK activities in real time.
Monitoring Setup
Terminal 1
SELECT * FROM pg_stat_progress_repack;
Enable refresh:
\watch
Terminal 2
Execute:
REPACK table_name;
Result from terminal 1
Tuesday 09 June 2026 07:58:19 PM (every 2s)
pid | datid | datname | relid | command | phase | repack_index_relid | heap_tuples_scanned | heap_tuples_inserted | heap_tuples_updated | heap_tuples_deleted | heap_blks_total | heap_blks_scanned | index_rebuild_count
---------+-------+----------+-------+---------+-------------------+--------------------+---------------------+----------------------+---------------------+---------------------+-----------------+-------------------+---------------------
1655509 | 5 | postgres | 32876 | REPACK | seq scanning heap | 0 | 3156851 | 3156851 | 0 | 0 | 46729 | 29504 | 2
Detailed progress:
- heap_tuples_scanned : 3156851
- heap_tuples_inserted : 3156851
- heap_blks_total : 46729
- heap_blks_scanned : 29504
- index_rebuild_count : 2
Explanation
The view provides visibility into:
- Repack progress
- Table scan progress
- Index rebuild progress
- Cleanup stages
This is extremely useful for large tables where repacking can take significant time.
7. Pg_stat_recovery
View definition
select * from pg_views where viewname = 'pg_stat_recovery';
Result :
schemaname | viewname | viewowner | definition
------------+------------------+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------
pg_catalog | pg_stat_recovery | cybrosys | SELECT promote_triggered,
+
| | | last_replayed_read_lsn,
+
| | | last_replayed_end_lsn,
+
| | | last_replayed_tli,
+
| | | replay_end_lsn,
+
| | | replay_end_tli,
+
| | | recovery_last_xact_time,
+
| | | current_chunk_start_time,
+
| | | pause_state
+
| | | FROM pg_stat_get_recovery() s(promote_triggered, last_replayed_read_lsn, last_replayed_end_lsn, last_replayed_tli, replay_end_lsn, replay_end_tli, recovery_
last_xact_time, current_chunk_start_time, pause_state)+
| | | WHERE (promote_triggered IS NOT NULL);
(1 row)
The pg_stat_recovery view provides detailed recovery and WAL replay statistics for standby servers.
Before PostgreSQL 19, monitoring recovery often required multiple functions.
Now all important recovery information is available in a single view.
Verify Primary Server
SELECT pg_is_in_recovery();
Output:
f
No rows appear:
SELECT * FROM pg_stat_recovery;
This is expected because recovery statistics exist only on standby servers.
Configure Replication
Create replication user:
CREATE ROLE replicator
WITH REPLICATION LOGIN
PASSWORD 'replica123';
Verify configuration:
SHOW wal_level;
SHOW max_wal_senders;
SHOW hot_standby;
Expected values:
wal_level = replica
max_wal_senders = 10
hot_standby = on
Update pg_hba.conf
Locate:
SHOW hba_file;
Result :
hba_file
----------------------------------------------------
/home/cybrosys/june_6/postgres/pg_data/pg_hba.conf
(1 row)
Edit the file.
sudo nano /home/cybrosys/june_6/postgres/pg_data/pg_hba.conf
Add the configuration like this
host replication replicator 0.0.0.0/0 md5
Restart PostgreSQL.
bin/pg_ctl -D pg_data -l logfile -o “-p 5440” restart
Create Standby
Generate base backup:
bin/pg_basebackup \
-h localhost \
-p 5440 \
-U replicator \
-D standby \
-P -R
Restart primary:
bin/pg_ctl -D pg_data/ -l logfile -o "-p 5440" restart
Start standby:
bin/pg_ctl -D standby/ -l logfile_standby -o "-p 5441" start
Verify Recovery Mode
Connect to standby:
SELECT pg_is_in_recovery();
Output:
pg_is_in_recovery
-------------------
t
(1 row)
Query recovery statistics:
select * from pg_stat_recovery;
Result :
promote_triggered | last_replayed_read_lsn | last_replayed_end_lsn | last_replayed_tli | replay_end_lsn | replay_end_tli | recovery_last_xact_time | current_chunk_start_time | pause_state
-------------------+------------------------+-----------------------+-------------------+----------------+----------------+-------------------------+----------------------------------+-------------
f | 1/90000160 | 1/90000198 | 1 | 1/90000198 | 1 | | 2026-06-09 21:03:47.161934+05:30 | not paused
(1 row)
This view helps administrators monitor:
- WAL replay progress
- Replication lag
- Standby recovery activity
- Promotion events
- Recovery pause status
It provides a centralized source of recovery information and simplifies standby monitoring.
PostgreSQL 19 Beta introduces several valuable monitoring and observability views that expose internal PostgreSQL behavior more clearly than previous versions.
These additions significantly improve PostgreSQL observability and provide administrators with more direct insight into replication, maintenance, recovery, locking, storage management, and autovacuum behavior.
For DBAs, PostgreSQL 19 offers a substantial improvement in monitoring capabilities, reducing the need for manual catalog inspection and custom monitoring queries.