v30-0001-Track-table-VM-stability.patch

From 96789144424e991aab44e7c8dfad9db4a2e368e1 Mon Sep 17 00:00:00 2001
From: Alena Rybakina <lena.ribackina@yandex.ru>
Date: Sat, 28 Feb 2026 18:30:12 +0300
Subject: [PATCH v30] Track table VM stability.

Add rev_all_visible_pages and rev_all_frozen_pages counters to
pg_stat_all_tables tracking the number of times the all-visible and
all-frozen bits are cleared in the visibility map. These bits are cleared by
backend processes during regular DML operations. Hence, the counters are placed
in table statistic entry.

A high rev_all_visible_pages rate relative to DML volume indicates
that modifications are scattered across previously-clean pages rather
than concentrated on already-dirty ones, causing index-only scans to
fall back to heap fetches.  A high rev_all_frozen_pages rate indicates
that vacuum's freezing work is being frequently undone by concurrent
DML.

Authors: Alena Rybakina <lena.ribackina@yandex.ru>,
         Andrei Lepikhov <lepihov@gmail.com>,
         Andrei Zubkov <a.zubkov@postgrespro.ru>
Reviewed-by: Dilip Kumar <dilipbalaut@gmail.com>,
         Masahiko Sawada <sawada.mshk@gmail.com>,
         Ilia Evdokimov <ilya.evdokimov@tantorlabs.com>,
         Jian He <jian.universality@gmail.com>,
         Kirill Reshke <reshkekirill@gmail.com>,
         Alexander Korotkov <aekorotkov@gmail.com>,
         Jim Nasby <jnasby@upgrade.com>,
         Sami Imseih <samimseih@gmail.com>,
         Karina Litskevich <litskevichkarina@gmail.com>
---
 doc/src/sgml/monitoring.sgml                 | 32 ++++++++++++++++++++
 src/backend/access/heap/visibilitymap.c      | 10 ++++++
 src/backend/catalog/system_views.sql         |  2 ++
 src/backend/utils/activity/pgstat_relation.c |  2 ++
 src/backend/utils/adt/pgstatfuncs.c          |  6 ++++
 src/include/catalog/pg_proc.dat              | 12 +++++++-
 src/include/pgstat.h                         | 17 ++++++++++-
 src/test/regress/expected/rules.out          |  6 ++++
 8 files changed, 85 insertions(+), 2 deletions(-)

diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index cc014564c97..8ce0d0dd2cb 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -4249,6 +4249,38 @@ description | Waiting for a newly initialized WAL file to reach durable storage
       </para></entry>
      </row>
 
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>rev_all_visible_pages</structfield> <type>bigint</type>
+      </para>
+      <para>
+       Number of times the all-visible bit in the
+       <link linkend="storage-vm">visibility map</link> was cleared for a
+       page of this table.  The all-visible bit is cleared by backend
+       processes when they modify a heap page that was previously marked
+       all-visible, for example during an <command>INSERT</command>,
+       <command>UPDATE</command>, or <command>DELETE</command>.
+       A high rate of change in this counter means that index-only scans
+       on this table may frequently need to fall back to heap fetches,
+       and that vacuum must re-do visibility map work on those pages.
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>rev_all_frozen_pages</structfield> <type>bigint</type>
+      </para>
+      <para>
+       Number of times the all-frozen bit in the
+       <link linkend="storage-vm">visibility map</link> was cleared for a
+       page of this table.  The all-frozen bit is cleared by backend
+       processes when they modify a heap page that was previously marked
+       all-frozen.  A high value compared to the number of vacuum cycles
+       indicates that DML activity is frequently undoing the freezing work
+       performed by vacuum.
+      </para></entry>
+     </row>
+
      <row>
       <entry role="catalog_table_entry"><para role="column_definition">
        <structfield>last_vacuum</structfield> <type>timestamp with time zone</type>
diff --git a/src/backend/access/heap/visibilitymap.c b/src/backend/access/heap/visibilitymap.c
index e21b96281a6..9ea7a068ef0 100644
--- a/src/backend/access/heap/visibilitymap.c
+++ b/src/backend/access/heap/visibilitymap.c
@@ -92,6 +92,7 @@
 #include "access/xloginsert.h"
 #include "access/xlogutils.h"
 #include "miscadmin.h"
+#include "pgstat.h"
 #include "port/pg_bitutils.h"
 #include "storage/bufmgr.h"
 #include "storage/smgr.h"
@@ -163,6 +164,15 @@ visibilitymap_clear(Relation rel, BlockNumber heapBlk, Buffer vmbuf, uint8 flags
 
 	if (map[mapByte] & mask)
 	{
+		/*
+		 * Track how often all-visible or all-frozen bits are cleared in the
+		 * visibility map.
+		 */
+		if (map[mapByte] >> mapOffset & flags & VISIBILITYMAP_ALL_VISIBLE)
+			pgstat_count_vm_rev_all_visible(rel);
+		if (map[mapByte] >> mapOffset & flags & VISIBILITYMAP_ALL_FROZEN)
+			pgstat_count_vm_rev_all_frozen(rel);
+
 		map[mapByte] &= ~mask;
 
 		MarkBufferDirty(vmbuf);
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 339c016e510..1eaf79fdb4e 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -729,6 +729,8 @@ CREATE VIEW pg_stat_all_tables AS
             pg_stat_get_dead_tuples(C.oid) AS n_dead_tup,
             pg_stat_get_mod_since_analyze(C.oid) AS n_mod_since_analyze,
             pg_stat_get_ins_since_vacuum(C.oid) AS n_ins_since_vacuum,
+            pg_stat_get_rev_all_visible_pages(C.oid) AS rev_all_visible_pages,
+            pg_stat_get_rev_all_frozen_pages(C.oid) AS rev_all_frozen_pages,
             pg_stat_get_last_vacuum_time(C.oid) as last_vacuum,
             pg_stat_get_last_autovacuum_time(C.oid) as last_autovacuum,
             pg_stat_get_last_analyze_time(C.oid) as last_analyze,
diff --git a/src/backend/utils/activity/pgstat_relation.c b/src/backend/utils/activity/pgstat_relation.c
index bc8c43b96aa..bb26e97898d 100644
--- a/src/backend/utils/activity/pgstat_relation.c
+++ b/src/backend/utils/activity/pgstat_relation.c
@@ -879,6 +879,8 @@ pgstat_relation_flush_cb(PgStat_EntryRef *entry_ref, bool nowait)
 
 	tabentry->blocks_fetched += lstats->counts.blocks_fetched;
 	tabentry->blocks_hit += lstats->counts.blocks_hit;
+	tabentry->rev_all_visible_pages += lstats->counts.rev_all_visible_pages;
+	tabentry->rev_all_frozen_pages += lstats->counts.rev_all_frozen_pages;
 
 	/* Clamp live_tuples in case of negative delta_live_tuples */
 	tabentry->live_tuples = Max(tabentry->live_tuples, 0);
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 5ac022274a7..6d7c4cc1ed2 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -107,6 +107,12 @@ PG_STAT_GET_RELENTRY_INT64(tuples_updated)
 /* pg_stat_get_vacuum_count */
 PG_STAT_GET_RELENTRY_INT64(vacuum_count)
 
+/* pg_stat_get_rev_all_frozen_pages */
+PG_STAT_GET_RELENTRY_INT64(rev_all_frozen_pages)
+
+/* pg_stat_get_rev_all_visible_pages */
+PG_STAT_GET_RELENTRY_INT64(rev_all_visible_pages)
+
 #define PG_STAT_GET_RELENTRY_FLOAT8(stat)						\
 Datum															\
 CppConcat(pg_stat_get_,stat)(PG_FUNCTION_ARGS)					\
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 361e2cfffeb..252eab079d6 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -12831,6 +12831,16 @@
   prosrc => 'hashoid8' },
 { oid => '8281', descr => 'hash',
   proname => 'hashoid8extended', prorettype => 'int8',
-  proargtypes => 'oid8 int8', prosrc => 'hashoid8extended' },
+  proargtypes => 'oid8 int8',   prosrc => 'hashoid8extended' },
 
+{ oid => '8002',
+  descr => 'statistics: number of times the all-visible pages in the visibility map was removed for pages of table',
+  proname => 'pg_stat_get_rev_all_visible_pages', provolatile => 's',
+  proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
+  prosrc => 'pg_stat_get_rev_all_visible_pages' },
+{ oid => '8003',
+  descr => 'statistics: number of times the all-frozen pages in the visibility map was removed for pages of table',
+  proname => 'pg_stat_get_rev_all_frozen_pages', provolatile => 's',
+  proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
+  prosrc => 'pg_stat_get_rev_all_frozen_pages' },
 ]
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 216b93492ba..849eea24f29 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -159,6 +159,8 @@ typedef struct PgStat_TableCounts
 
 	PgStat_Counter blocks_fetched;
 	PgStat_Counter blocks_hit;
+	PgStat_Counter rev_all_visible_pages;
+	PgStat_Counter rev_all_frozen_pages;
 } PgStat_TableCounts;
 
 /* ----------
@@ -217,7 +219,7 @@ typedef struct PgStat_TableXactStatus
  * ------------------------------------------------------------
  */
 
-#define PGSTAT_FILE_FORMAT_ID	0x01A5BCBB
+#define PGSTAT_FILE_FORMAT_ID	0x01A5BCBC
 
 typedef struct PgStat_ArchiverStats
 {
@@ -450,6 +452,8 @@ typedef struct PgStat_StatTabEntry
 
 	PgStat_Counter blocks_fetched;
 	PgStat_Counter blocks_hit;
+	PgStat_Counter rev_all_visible_pages;
+	PgStat_Counter rev_all_frozen_pages;
 
 	TimestampTz last_vacuum_time;	/* user initiated vacuum */
 	PgStat_Counter vacuum_count;
@@ -725,6 +729,17 @@ extern void pgstat_report_analyze(Relation rel,
 		if (pgstat_should_count_relation(rel))						\
 			(rel)->pgstat_info->counts.blocks_hit++;				\
 	} while (0)
+/* count revocations of all-visible and all-frozen bits in visibility map */
+#define pgstat_count_vm_rev_all_visible(rel)						\
+	do {															\
+		if (pgstat_should_count_relation(rel))						\
+			(rel)->pgstat_info->counts.rev_all_visible_pages++;	\
+	} while (0)
+#define pgstat_count_vm_rev_all_frozen(rel)						\
+	do {															\
+		if (pgstat_should_count_relation(rel))						\
+			(rel)->pgstat_info->counts.rev_all_frozen_pages++;	\
+	} while (0)
 
 extern void pgstat_count_heap_insert(Relation rel, PgStat_Counter n);
 extern void pgstat_count_heap_update(Relation rel, bool hot, bool newpage);
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index f373ad704b6..4fb3167e99c 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1834,6 +1834,8 @@ pg_stat_all_tables| SELECT c.oid AS relid,
     pg_stat_get_dead_tuples(c.oid) AS n_dead_tup,
     pg_stat_get_mod_since_analyze(c.oid) AS n_mod_since_analyze,
     pg_stat_get_ins_since_vacuum(c.oid) AS n_ins_since_vacuum,
+    pg_stat_get_rev_all_visible_pages(c.oid) AS rev_all_visible_pages,
+    pg_stat_get_rev_all_frozen_pages(c.oid) AS rev_all_frozen_pages,
     pg_stat_get_last_vacuum_time(c.oid) AS last_vacuum,
     pg_stat_get_last_autovacuum_time(c.oid) AS last_autovacuum,
     pg_stat_get_last_analyze_time(c.oid) AS last_analyze,
@@ -2285,6 +2287,8 @@ pg_stat_sys_tables| SELECT relid,
     n_dead_tup,
     n_mod_since_analyze,
     n_ins_since_vacuum,
+    rev_all_visible_pages,
+    rev_all_frozen_pages,
     last_vacuum,
     last_autovacuum,
     last_analyze,
@@ -2340,6 +2344,8 @@ pg_stat_user_tables| SELECT relid,
     n_dead_tup,
     n_mod_since_analyze,
     n_ins_since_vacuum,
+    rev_all_visible_pages,
+    rev_all_frozen_pages,
     last_vacuum,
     last_autovacuum,
     last_analyze,
-- 
2.53.0