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

From bd7cbd4450512aaf640156e977faa28e5095d33a Mon Sep 17 00:00:00 2001
From: Alena Rybakina <a.rybakina@postgrespro.ru>
Date: Mon, 16 Mar 2026 14:55:45 +0300
Subject: [PATCH] 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          |  4 +-
 src/backend/utils/activity/pgstat_relation.c  |  2 +
 src/backend/utils/adt/pgstatfuncs.c           |  6 ++
 src/include/catalog/pg_proc.dat               | 10 +++
 src/include/pgstat.h                          | 17 ++++-
 .../expected/vacuum-extending-freeze.out      | 50 +++++++++++++
 src/test/isolation/isolation_schedule         |  1 +
 .../specs/vacuum-extending-freeze.spec        | 73 +++++++++++++++++++
 src/test/regress/expected/rules.out           | 12 ++-
 11 files changed, 212 insertions(+), 5 deletions(-)
 create mode 100644 src/test/isolation/expected/vacuum-extending-freeze.out
 create mode 100644 src/test/isolation/specs/vacuum-extending-freeze.spec

diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 9c5c6dc490f..0b27558686e 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -4258,6 +4258,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>visible_pages_cleared</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
+       pages of this table.  The all-visible bit of a heap page is
+       cleared whenever a backend process modifies a page that was
+       previously marked all-visible by <command>VACUUM</command>.  The
+       page must then be processed again by <command>VACUUM</command> on a
+       subsequent run.  A high rate of change in this counter means that
+       <command>VACUUM</command> has to repeatedly re-process pages of this
+       table.
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>frozen_pages_cleared</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
+       pages of this table.  The all-frozen bit of a heap page is cleared
+       whenever a backend process modifies a page that was previously
+       marked all-frozen by <command>VACUUM</command>.  The page must then
+       be processed again by <command>VACUUM</command> on the next freeze
+       run for this table.
+      </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..7b3ab6244d0 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] & ((flags & VISIBILITYMAP_ALL_VISIBLE) << mapOffset))
+			pgstat_count_visible_pages_cleared(rel);
+		if (map[mapByte] & ((flags & VISIBILITYMAP_ALL_FROZEN) << mapOffset))
+			pgstat_count_frozen_pages_cleared(rel);
+
 		map[mapByte] &= ~mask;
 
 		MarkBufferDirty(vmbuf);
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 90d48bc9c80..9ff013ac797 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -741,7 +741,9 @@ CREATE VIEW pg_stat_all_tables AS
             pg_stat_get_total_autovacuum_time(C.oid) AS total_autovacuum_time,
             pg_stat_get_total_analyze_time(C.oid) AS total_analyze_time,
             pg_stat_get_total_autoanalyze_time(C.oid) AS total_autoanalyze_time,
-            pg_stat_get_stat_reset_time(C.oid) AS stats_reset
+            pg_stat_get_stat_reset_time(C.oid) AS stats_reset,
+            pg_stat_get_visible_pages_cleared(C.oid) AS visible_pages_cleared,
+            pg_stat_get_frozen_pages_cleared(C.oid) AS frozen_pages_cleared
     FROM pg_class C LEFT JOIN
          pg_index I ON C.oid = I.indrelid
          LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
diff --git a/src/backend/utils/activity/pgstat_relation.c b/src/backend/utils/activity/pgstat_relation.c
index bc8c43b96aa..78936aca82e 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->visible_pages_cleared += lstats->counts.visible_pages_cleared;
+	tabentry->frozen_pages_cleared += lstats->counts.frozen_pages_cleared;
 
 	/* 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..d50b7233c0e 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_visible_pages_cleared */
+PG_STAT_GET_RELENTRY_INT64(visible_pages_cleared)
+
+/* pg_stat_get_frozen_pages_cleared */
+PG_STAT_GET_RELENTRY_INT64(frozen_pages_cleared)
+
 #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..b52e463e63f 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -12833,4 +12833,14 @@
   proname => 'hashoid8extended', prorettype => 'int8',
   proargtypes => 'oid8 int8', prosrc => 'hashoid8extended' },
 
+{ oid => '8002',
+  descr => 'statistics: number of times the all-visible pages in the visibility map were cleared for pages of this table',
+  proname => 'pg_stat_get_visible_pages_cleared', provolatile => 's',
+  proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
+  prosrc => 'pg_stat_get_visible_pages_cleared' },
+{ oid => '8003',
+  descr => 'statistics: number of times the all-frozen pages in the visibility map were cleared for pages of this table',
+  proname => 'pg_stat_get_frozen_pages_cleared', provolatile => 's',
+  proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
+  prosrc => 'pg_stat_get_frozen_pages_cleared' },
 ]
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 216b93492ba..8116d0959de 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 visible_pages_cleared;
+	PgStat_Counter frozen_pages_cleared;
 } 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 visible_pages_cleared;
+	PgStat_Counter frozen_pages_cleared;
 
 	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_visible_pages_cleared(rel)						\
+	do {															\
+		if (pgstat_should_count_relation(rel))						\
+			(rel)->pgstat_info->counts.visible_pages_cleared++;	\
+	} while (0)
+#define pgstat_count_frozen_pages_cleared(rel)						\
+	do {															\
+		if (pgstat_should_count_relation(rel))						\
+			(rel)->pgstat_info->counts.frozen_pages_cleared++;	\
+	} 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/isolation/expected/vacuum-extending-freeze.out b/src/test/isolation/expected/vacuum-extending-freeze.out
new file mode 100644
index 00000000000..58b51570e5e
--- /dev/null
+++ b/src/test/isolation/expected/vacuum-extending-freeze.out
@@ -0,0 +1,50 @@
+Parsed test spec with 2 sessions
+
+starting permutation: s1_initial_vacuum s2_vacuum s1_get_set_vm_flags_stats s1_update_table s1_get_cleared_vm_flags_stats
+pg_stat_force_next_flush
+------------------------
+                        
+(1 row)
+
+step s1_initial_vacuum: 
+    SELECT pg_stat_force_next_flush();
+
+pg_stat_force_next_flush
+------------------------
+                        
+(1 row)
+
+step s2_vacuum: 
+    VACUUM vestat;
+
+step s1_get_set_vm_flags_stats: 
+    SELECT relallfrozen > 0 AS relallfrozen_pos,
+           relallvisible > 0 AS relallvisible_pos
+    FROM pg_class c
+    WHERE c.relname = 'vestat';
+
+relallfrozen_pos|relallvisible_pos
+----------------+-----------------
+t               |t                
+(1 row)
+
+step s1_update_table: 
+    UPDATE vestat SET x = x + 1001;
+    SELECT pg_stat_force_next_flush();
+
+pg_stat_force_next_flush
+------------------------
+                        
+(1 row)
+
+step s1_get_cleared_vm_flags_stats: 
+    SELECT visible_pages_cleared > 0 AS visible_pages_cleared,
+           frozen_pages_cleared > 0 AS frozen_pages_cleared
+    FROM pg_stat_all_tables
+    WHERE relname = 'vestat';
+
+visible_pages_cleared|frozen_pages_cleared
+---------------------+--------------------
+t                    |t                   
+(1 row)
+
diff --git a/src/test/isolation/isolation_schedule b/src/test/isolation/isolation_schedule
index 4e466580cd4..81e68f85d88 100644
--- a/src/test/isolation/isolation_schedule
+++ b/src/test/isolation/isolation_schedule
@@ -124,3 +124,4 @@ test: serializable-parallel-2
 test: serializable-parallel-3
 test: matview-write-skew
 test: lock-nowait
+test: vacuum-extending-freeze
diff --git a/src/test/isolation/specs/vacuum-extending-freeze.spec b/src/test/isolation/specs/vacuum-extending-freeze.spec
new file mode 100644
index 00000000000..b8f8c177595
--- /dev/null
+++ b/src/test/isolation/specs/vacuum-extending-freeze.spec
@@ -0,0 +1,73 @@
+# In short, this test validates the correctness and stability of cumulative
+# vacuum statistics accounting around freezing, visibility, and revision
+# tracking across VACUUM and backend operations.
+
+setup
+{
+    CREATE TABLE vestat (x int)
+        WITH (autovacuum_enabled = off, fillfactor = 70);
+
+    INSERT INTO vestat
+        SELECT i FROM generate_series(1, 5000) AS g(i);
+
+    ANALYZE vestat;
+
+    -- Ensure stats are flushed before starting the scenario
+    SELECT pg_stat_force_next_flush();
+}
+
+teardown
+{
+    DROP TABLE IF EXISTS vestat;
+    RESET vacuum_freeze_min_age;
+    RESET vacuum_freeze_table_age;
+
+}
+
+session s1
+
+step s1_initial_vacuum
+{
+    SELECT pg_stat_force_next_flush();
+}
+
+step s1_get_set_vm_flags_stats
+{
+    SELECT relallfrozen > 0 AS relallfrozen_pos,
+           relallvisible > 0 AS relallvisible_pos
+    FROM pg_class c
+    WHERE c.relname = 'vestat';
+}
+
+step s1_get_cleared_vm_flags_stats
+{
+    SELECT visible_pages_cleared > 0 AS visible_pages_cleared,
+           frozen_pages_cleared > 0 AS frozen_pages_cleared
+    FROM pg_stat_all_tables
+    WHERE relname = 'vestat';
+}
+
+session s2
+setup
+{
+    -- Configure aggressive freezing vacuum behavior
+    SET vacuum_freeze_min_age = 0;
+    SET vacuum_freeze_table_age = 0;
+}
+step s2_vacuum
+{
+    VACUUM vestat;
+}
+
+step s1_update_table
+{
+    UPDATE vestat SET x = x + 1001;
+    SELECT pg_stat_force_next_flush();
+}
+
+permutation
+    s1_initial_vacuum
+    s2_vacuum
+    s1_get_set_vm_flags_stats
+    s1_update_table
+    s1_get_cleared_vm_flags_stats
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 71d7262049e..b36b551d877 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1846,7 +1846,9 @@ pg_stat_all_tables| SELECT c.oid AS relid,
     pg_stat_get_total_autovacuum_time(c.oid) AS total_autovacuum_time,
     pg_stat_get_total_analyze_time(c.oid) AS total_analyze_time,
     pg_stat_get_total_autoanalyze_time(c.oid) AS total_autoanalyze_time,
-    pg_stat_get_stat_reset_time(c.oid) AS stats_reset
+    pg_stat_get_stat_reset_time(c.oid) AS stats_reset,
+    pg_stat_get_visible_pages_cleared(c.oid) AS visible_pages_cleared,
+    pg_stat_get_frozen_pages_cleared(c.oid) AS frozen_pages_cleared
    FROM ((pg_class c
      LEFT JOIN pg_index i ON ((c.oid = i.indrelid)))
      LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
@@ -2298,7 +2300,9 @@ pg_stat_sys_tables| SELECT relid,
     total_autovacuum_time,
     total_analyze_time,
     total_autoanalyze_time,
-    stats_reset
+    stats_reset,
+    visible_pages_cleared,
+    frozen_pages_cleared
    FROM pg_stat_all_tables
   WHERE ((schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (schemaname ~ '^pg_toast'::text));
 pg_stat_user_functions| SELECT p.oid AS funcid,
@@ -2353,7 +2357,9 @@ pg_stat_user_tables| SELECT relid,
     total_autovacuum_time,
     total_analyze_time,
     total_autoanalyze_time,
-    stats_reset
+    stats_reset,
+    visible_pages_cleared,
+    frozen_pages_cleared
    FROM pg_stat_all_tables
   WHERE ((schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (schemaname !~ '^pg_toast'::text));
 pg_stat_wal| SELECT wal_records,
-- 
2.39.5 (Apple Git-154)