Welcome to the Day30 of PG18Hacktober !!
PostgreSQL 18 brings significant improvements to its system catalogs and statistics views, expanding monitoring capabilities and providing database administrators with more granular insights into database operations. This release focuses on enhancing observability across I/O operations, maintenance activities, memory management, and logical replication conflicts.
Key Changes in PG18
pg_stat_io
The pg_stat_io view will contain one row for each combination of backend type, target I/O object, and I/O context, showing cluster-wide I/O statistics. Combinations which do not make sense are omitted.
In PG17, pg_stat_io view was structured as follows:
postgres=# select * from pg_stat_io limit 1;
-[ RECORD 1 ]--+------------------------------
backend_type | client backend
object | relation
context | bulkread
reads | 3
read_time | 0
writes | 0
write_time | 0
writebacks | 0
writeback_time | 0
extends |
extend_time |
op_bytes | 8192
hits | 36
evictions | 0
reuses | 0
fsyncs |
fsync_time |
stats_reset | 2025-10-28 18:55:32.010725+00In PG18,
postgres=# select * from pg_stat_io limit 1;
-[ RECORD 1 ]--+-----------------------------
backend_type | client backend
object | relation
context | bulkread
reads | 80576
read_bytes | 10378477568
read_time | 0
writes | 107658
write_bytes | 881934336
write_time | 0
writebacks | 0
writeback_time | 0
extends |
extend_bytes |
extend_time |
hits | 180336
evictions | 1413
reuses | 1264545
fsyncs |
fsync_time |
stats_reset | 2025-10-08 06:27:49.74186+00Three new columns have been added:
read_bytes: The total size of read operations in bytes.write_bytes: The total size of write operations in bytes.extend_bytes: The total size of relation extend operations in bytes.
The op_bytes column, which always equaled BLCKSZ, has been removed.
The pg_stat_io view now tracks WAL I/O (moved from pg_stat_wal), and better distinguishes I/O object and context, including per-backend and WAL file-level stats. It Supports byte-level reporting, there are no longer page-size assumptions; and adapts better to I/O-combining behavior.
pg_stat_all_tables
The pg_stat_all_tables view will contain one row for each table in the current database (including TOAST tables), showing statistics about accesses to that specific table.
In PG17 , pg_stat_all_tables view was structured as follows:
postgres=# select * from pg_stat_all_tables where relname = 't_unicode';
-[ RECORD 1 ]-------+------------------------------
relid | 16385
schemaname | public
relname | t_unicode
seq_scan | 130
last_seq_scan | 2025-10-30 06:32:00.806496+00
seq_tup_read | 130
idx_scan |
last_idx_scan |
idx_tup_fetch |
n_tup_ins | 1000000
n_tup_upd | 0
n_tup_del | 0
n_tup_hot_upd | 0
n_tup_newpage_upd | 0
n_live_tup | 1000000
n_dead_tup | 0
n_mod_since_analyze | 0
n_ins_since_vacuum | 0
last_vacuum |
last_autovacuum | 2025-10-28 18:56:53.149875+00
last_analyze |
last_autoanalyze | 2025-10-28 18:56:53.264963+00
vacuum_count | 0
autovacuum_count | 1
analyze_count | 0
autoanalyze_count | 1In PG18, pg_stat_all_tables and its variants will report the time spent in VACUUM, ANALYZE, and their automatic variants.
postgres=# select * from pg_stat_all_tables where relname = 'collation_test';
-[ RECORD 1 ]----------+------------------------------
relid | 16701
schemaname | public
relname | collation_test
seq_scan | 12
last_seq_scan | 2025-10-29 02:14:36.048652+00
seq_tup_read | 50000000
idx_scan | 0
last_idx_scan |
idx_tup_fetch | 0
n_tup_ins | 10000000
n_tup_upd | 0
n_tup_del | 0
n_tup_hot_upd | 0
n_tup_newpage_upd | 0
n_live_tup | 9999977
n_dead_tup | 0
n_mod_since_analyze | 0
n_ins_since_vacuum | 0
last_vacuum |
last_autovacuum | 2025-10-29 02:09:43.450992+00
last_analyze |
last_autoanalyze | 2025-10-29 02:09:44.274256+00
vacuum_count | 0
autovacuum_count | 1
analyze_count | 0
autoanalyze_count | 1
total_vacuum_time | 0
total_autovacuum_time | 9127
total_analyze_time | 0
total_autoanalyze_time | 824total_vacuum_time: Total time this table has been manually vacuumed, in milliseconds (not countingVACUUM FULL).total_autovacuum_time: Total time this table has been vacuumed by the autovacuum daemon, in milliseconds.total_analyze_time: Total time this table has been manually analyzed, in milliseconds.total_autoanalyze_time: Total time this table has been analyzed by the autovacuum daemon, in milliseconds.
All these include the time spent sleeping due to cost-based delays. Also added delay time to the VACUUM and ANALYZE reports which appear in the server log, the system views pg_stat_progress_vacuum and pg_stat_progress_analyze, and the output of VACUUM and ANALYZE when in VERBOSE mode; tracking must be enabled with the server variable track_cost_delay_timing.
pg_stat_checkpointer
The pg_stat_checkpointer view will always have a single row, containing data about the checkpointer process of the cluster.
In PG17, pg_stat_checkpointer view was structured as follows:
postgres=# select * from pg_stat_checkpointer ;
-[ RECORD 1 ]-------+------------------------------
num_timed | 4
num_requested | 1
restartpoints_timed | 0
restartpoints_req | 0
restartpoints_done | 0
write_time | 4602
sync_time | 11
buffers_written | 4518
stats_reset | 2025-10-28 18:55:32.010725+00In PG18, two new columns have been added num_done and slru_written :
postgres=# select * from pg_stat_checkpointer ;
-[ RECORD 1 ]-------+-----------------------------
num_timed | 4385
num_requested | 33
num_done | 59
restartpoints_timed | 0
restartpoints_req | 0
restartpoints_done | 0
write_time | 2323501
sync_time | 20144
buffers_written | 57412
slru_written | 88
stats_reset | 2025-10-08 06:27:49.74186+00num_done: Tracks only the completed checkpoints.slru_written:Tracks the number of SLRU(Simple least recently used) buffers written during checkpoints and restartpoints.
pg_stat_database
The pg_stat_database view will contain one row for each database in the cluster, plus one for shared objects, showing database-wide statistics.
In PG17, pg_stat_database view was structured as follows:
postgres=# select * from pg_stat_database where datname='postgres';
-[ RECORD 1 ]------------+------------
datid | 5
datname | postgres
numbackends | 2
xact_commit | 210
xact_rollback | 2
blks_read | 500
blks_hit | 2031932
tup_returned | 26575
tup_fetched | 4370
tup_inserted | 1000049
tup_updated | 3
tup_deleted | 0
conflicts | 0
temp_files | 1
temp_bytes | 14000000
deadlocks | 0
checksum_failures |
checksum_last_failure |
blk_read_time | 0
blk_write_time | 0
session_time | 1255076.987
active_time | 1390.211
idle_in_transaction_time | 0
sessions | 6
sessions_abandoned | 0
sessions_fatal | 0
sessions_killed | 0
stats_reset |In PG18, two new columns were added:
postgres=# select * from pg_stat_database where datname='postgres';
-[ RECORD 1 ]--------------+--------------
datid | 5
datname | postgres
numbackends | 2
xact_commit | 54343
xact_rollback | 8351
blks_read | 2657863
blks_hit | 161871885
tup_returned | 217432182
tup_fetched | 4620415
tup_inserted | 51876113
tup_updated | 196
tup_deleted | 1115
conflicts | 0
temp_files | 43
temp_bytes | 3528189312
deadlocks | 0
checksum_failures |
checksum_last_failure |
blk_read_time | 0
blk_write_time | 0
session_time | 265255048.015
active_time | 1407387.532
idle_in_transaction_time | 9007.001
sessions | 91
sessions_abandoned | 13
sessions_fatal | 0
sessions_killed | 7
parallel_workers_to_launch | 40
parallel_workers_launched | 40
stats_reset |parallel_workers_to_launch: Tracks the number of parallel workers planned to be launched by queries on this database.parallel_workers_launched: Tracks the number of parallel workers launched by queries on this database.
pg_stat_wal
The pg_stat_wal view will always have a single row, containing data about WAL activity of the cluster.
In PG17, pg_stat_wal view tracked read/sync columns also.
postgres=# select * from pg_stat_wal;
-[ RECORD 1 ]----+------------------------------
wal_records | 1059290
wal_fpi | 548
wal_bytes | 70811376
wal_buffers_full | 7710
wal_write | 8484
wal_sync | 30
wal_write_time | 0
wal_sync_time | 0
stats_reset | 2025-10-28 18:55:32.010725+00In PG18, read/sync columns and GUC track_wal_io_timings are removed. These are the four columns removed:
-
wal_write wal_syncwal_write_timewal_sync_time
postgres=# select * from pg_stat_wal;
-[ RECORD 1 ]----+-----------------------------
wal_records | 95909089
wal_fpi | 79004
wal_bytes | 9117511678
wal_buffers_full | 926970
stats_reset | 2025-10-08 06:27:49.74186+00PostgreSQL 18 has added an equivalent of this information in pg_stat_io with more granularity as this now spreads across the backend types, IO context and IO objects. So, keeping the same information in pg_stat_wal has little benefits.
pg_class
pg_class already has relallvisible, an estimate of the number of pages in the relation marked all-visible in the visibility map in PG17
In PG17,pg_class view is structured as follows:
postgres=# select * from pg_class where relname='t_unicode';
-[ RECORD 1 ]-------+----------
oid | 16385
relname | t_unicode
relnamespace | 2200
reltype | 16387
reloftype | 0
relowner | 10
relam | 2
relfilenode | 16385
reltablespace | 0
relpages | 4425
reltuples | 1e+06
relallvisible | 4425
reltoastrelid | 16389
relhasindex | f
relisshared | f
relpersistence | p
relkind | r
relnatts | 2
relchecks | 0
relhasrules | f
relhastriggers | f
relhassubclass | f
relrowsecurity | f
relforcerowsecurity | f
relispopulated | t
relreplident | d
relispartition | f
relrewrite | 0
relfrozenxid | 739
relminmxid | 1
relacl |
reloptions |
relpartbound |In PG18 ,now relallfrozen has been added, which provides an estimate of the number of pages marked all-frozen in the visibility map.
postgres=# select * from pg_class where relname='collation_test';
-[ RECORD 1 ]-------+---------------
oid | 16701
relname | collation_test
relnamespace | 2200
reltype | 16703
reloftype | 0
relowner | 10
relam | 2
relfilenode | 16701
reltablespace | 0
relpages | 44248
reltuples | 9.999977e+06
relallvisible | 44248
relallfrozen | 0
reltoastrelid | 16706
relhasindex | t
relisshared | f
relpersistence | p
relkind | r
relnatts | 2
relchecks | 0
relhasrules | f
relhastriggers | f
relhassubclass | f
relrowsecurity | f
relforcerowsecurity | f
relispopulated | t
relreplident | d
relispartition | f
relrewrite | 0
relfrozenxid | 910
relminmxid | 1
relacl |
reloptions |
relpartbound |relallfrozen when coupled with relallvisible becomes useful for estimating the outstanding number of all-visible but not all frozen pages in the relation for the purposes of scheduling manual VACUUM’s and tuning vacuum freeze parameters.
pg_index
Prior to PG18, pg_index didn’t add TOAST table due to the fear of circularity issue. Let’s check the TOAST table and associated index :
postgres=# SELECT r.relname AS main_table,
t.relname AS toast_table,
i.relname AS toast_index
FROM pg_class r
LEFT JOIN pg_class t ON r.reltoastrelid = t.oid
LEFT JOIN pg_index d ON d.indrelid = t.oid
LEFT JOIN pg_class i ON d.indexrelid = i.oid
WHERE r.relname = 'pg_index';
-[ RECORD 1 ]---------
main_table | pg_index
toast_table |
toast_index | But PG18 adds the TOAST table to the pg_index for very large expression indexes.
postgres=# SELECT r.relname AS main_table,
t.relname AS toast_table,
i.relname AS toast_index
FROM pg_class r
LEFT JOIN pg_class t ON r.reltoastrelid = t.oid
LEFT JOIN pg_index d ON d.indrelid = t.oid
LEFT JOIN pg_class i ON d.indexrelid = i.oid
WHERE r.relname = 'pg_index';
-[ RECORD 1 ]--------------------
main_table | pg_index
toast_table | pg_toast_2610
toast_index | pg_toast_2610_indexpg_backend_memory_contexts
The view pg_backend_memory_contexts displays all the memory contexts of the server process attached to the current session.
In PG17, pg_backend_memory_contexts view is structured as follows
postgres=# select * from pg_backend_memory_contexts limit 1;
-[ RECORD 1 ]-+-----------------
name | TopMemoryContext
ident |
parent |
level | 0
total_bytes | 97792
total_nblocks | 5
free_bytes | 13544
free_chunks | 9
used_bytes | 84248In PG18, there are two columns added:
postgres=# select * from pg_backend_memory_contexts limit 1;
-[ RECORD 1 ]-+-----------------
name | TopMemoryContext
ident |
type | AllocSet
level | 1
path | {1}
total_bytes | 99456
total_nblocks | 5
free_bytes | 5808
free_chunks | 15
used_bytes | 93648type: Tracks the type of the memory context.path: Array of transient numerical identifiers to describe the memory context hierarchy. The first element is forTopMemoryContext, subsequent elements contain intermediate parents and the final element contains the identifier for the current context.
The parent column is removed because any queries which were self-joining to this view using the name and parent columns could get incorrect results due to the fact that names are not unique. In PG18, it explicitly breaks such queries so that they can be corrected and use the path column instead.
Both pg_get_process_memory_contexts() and pg_backend_memory_contexts have 1-based levels , whereas pg_log_backend_memory_contexts() was using 0-based levels. In PG18, it is changed – pg_backend_memory_contexts.level and pg_log_backend_memory_contexts() both use one-based levels.
pg_stat_subscription_stats
Till PG17, pg_stat_subscription_stats view is structured as follows:
postgres=# select * from pg_stat_subscription_stats;
subid | subname | apply_error_count | sync_error_count | stats_reset
-------+---------+-------------------+------------------+-------------
(0 rows)PostgreSQL 18 provides the additional logging information in the following conflict scenarios while applying changes:
postgres=# select * from pg_stat_subscription_stats;
subid | subname | apply_error_count | sync_error_count | confl_insert_exists | confl_update_origin_differs | confl_update_exists | confl_update_missing | confl_delete_origin_differs | confl_delete_missing | confl_multiple_unique_conflicts | stats_reset
-------+---------+-------------------+------------------+---------------------+-----------------------------+---------------------+----------------------+--
---------------------------+----------------------+---------------------------------+-------------
(0 rows)- insert_exists: Inserting a row that violates a NOT DEFERRABLE unique constraint.
- update_differ: Updating a row that was previously modified by another origin.
- update_exists: The updated row value violates a NOT DEFERRABLE unique constraint.
- update_missing: The tuple to be updated is missing.
- delete_differ: Deleting a row that was previously modified by another origin.
- delete_missing: The tuple to be deleted is missing.
For insert_exists and update_exists conflicts, the log can include the origin and commit timestamp details of the conflicting key with track_commit_timestamp. update_differ and delete_differ conflicts can only be detected when track_commit_timestamp is enabled on the subscriber.
Summary
PostgreSQL 18 delivers a major boost to database observability by enhancing its system and catalog views, offering administrators deeper, more precise visibility into internal operations. The release unifies and refines metrics across I/O, maintenance, memory, and replication layers — pg_stat_io now provides byte-level I/O tracking, pg_stat_all_tables and pg_stat_checkpointer add detailed vacuum, analyze, and checkpoint timing data, and pg_stat_database introduces parallel worker statistics. WAL monitoring is simplified as related metrics moved to pg_stat_io, while catalog updates in pg_class and pg_index improve vacuum tuning and large expression indexing. The redesigned pg_backend_memory_contexts introduces structured hierarchy tracking for clearer memory insight, and pg_stat_subscription_stats enhances replication conflict reporting. Together, these improvements make PostgreSQL 18’s monitoring layer more consistent, transparent, and administrator-friendly than ever before.
Stay tuned for the final blog of PG18Hacktober!!