v27-0001-Introduce-new-statistics-tracking-the-number-of-time.patch

From f1efce79f0aa61c650ec7cc6f40bf6a0806d29f5 Mon Sep 17 00:00:00 2001
From: Alena Rybakina <a.rybakina@postgrespro.ru>
Date: Sat, 28 Feb 2026 18:30:12 +0300
Subject: [PATCH 1/3] Introduce new statistics tracking the number of times the
 all-visible and all-frozen bits are cleared in the visibility map
 (rev_all_visible_pages and rev_all_frozen_pages). These counters, together
 with the existing per-vacuum frozen page statistics (vm_new_frozen_pages,
 vm_new_visible_pages), help assess how aggressively vacuum is configured and
 how frequently the backend has to revoke all-frozen/all-visible bits due to
 concurrent modifications.

 Authors: Alena Rybakina <lena.ribackina@yandex.ru>,
     Andrei Lepikhov <a.lepikhov@postgrespro.ru>,
     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>

---
 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              | 12 +++++++++++-
 src/include/pgstat.h                         | 18 +++++++++++++++++-
 src/test/regress/expected/rules.out          | 12 +++++++++---
 7 files changed, 58 insertions(+), 6 deletions(-)

diff --git a/src/backend/access/heap/visibilitymap.c b/src/backend/access/heap/visibilitymap.c
index 3047bd46def..2e7c28ea307 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"
@@ -161,6 +162,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 7553f31fef0..fa102f9c270 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -727,7 +727,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_rev_all_frozen_pages(C.oid) AS rev_all_frozen_pages,
+            pg_stat_get_rev_all_visible_pages(C.oid) AS rev_all_visible_pages
     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..885d590d2b2 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_frozen_pages += lstats->counts.rev_all_frozen_pages;
+	tabentry->rev_all_visible_pages += lstats->counts.rev_all_visible_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 73ca0bb0b7f..901f3dd55a1 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -106,6 +106,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 5e5e33f64fc..961337ce282 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -12693,6 +12693,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 fff7ecc2533..072065adc90 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -156,6 +156,9 @@ 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;
 
 /* ----------
@@ -214,7 +217,7 @@ typedef struct PgStat_TableXactStatus
  * ------------------------------------------------------------
  */
 
-#define PGSTAT_FILE_FORMAT_ID	0x01A5BCBB
+#define PGSTAT_FILE_FORMAT_ID	0x01A5BCBC
 
 typedef struct PgStat_ArchiverStats
 {
@@ -463,6 +466,8 @@ typedef struct PgStat_StatTabEntry
 	PgStat_Counter total_autoanalyze_time;
 
 	TimestampTz stat_reset_time;
+	PgStat_Counter rev_all_visible_pages;
+	PgStat_Counter rev_all_frozen_pages;
 } PgStat_StatTabEntry;
 
 /* ------
@@ -722,6 +727,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 f4ee2bd7459..7d26bc1a1dc 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_rev_all_frozen_pages(c.oid) AS rev_all_frozen_pages,
+    pg_stat_get_rev_all_visible_pages(c.oid) AS rev_all_visible_pages
    FROM ((pg_class c
      LEFT JOIN pg_index i ON ((c.oid = i.indrelid)))
      LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
@@ -2268,7 +2270,9 @@ pg_stat_sys_tables| SELECT relid,
     total_autovacuum_time,
     total_analyze_time,
     total_autoanalyze_time,
-    stats_reset
+    stats_reset,
+    rev_all_frozen_pages,
+    rev_all_visible_pages
    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,
@@ -2323,7 +2327,9 @@ pg_stat_user_tables| SELECT relid,
     total_autovacuum_time,
     total_analyze_time,
     total_autoanalyze_time,
-    stats_reset
+    stats_reset,
+    rev_all_frozen_pages,
+    rev_all_visible_pages
    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)