0004-Extended-vacuum-statistics-visibility-map-page-trans.patch

From 7acaea3543f0276bce73ac5fd9208744035ea2a5 Mon Sep 17 00:00:00 2001
From: Alena Rybakina <alenka.rybakina@gmail.com>
Date: Tue, 16 Jun 2026 10:48:17 +0300
Subject: [PATCH 4/8] Extended vacuum statistics: visibility-map page
 transitions for tables

Expose the counters that track how the vacuum updated the visibility map of
the table, with documentation and regression coverage:

  vm_new_frozen_pages          pages newly marked all-frozen in the VM
  vm_new_visible_pages         pages newly marked all-visible in the VM
  vm_new_visible_frozen_pages  pages newly marked all-visible and all-frozen
---
 doc/src/sgml/system-views.sgml             | 24 ++++++++++++++++
 src/backend/access/heap/vacuumlazy.c       |  3 ++
 src/backend/catalog/system_views.sql       |  5 +++-
 src/backend/utils/activity/pgstat_vacuum.c |  3 ++
 src/backend/utils/adt/pgstatfuncs.c        |  5 +++-
 src/include/catalog/pg_proc.dat            |  6 ++--
 src/include/pgstat.h                       |  7 +++++
 src/test/regress/expected/rules.out        |  7 +++--
 src/test/regress/expected/vacuum_stats.out | 33 ++++++++++++++++++++++
 src/test/regress/sql/vacuum_stats.sql      | 25 ++++++++++++++++
 10 files changed, 111 insertions(+), 7 deletions(-)

diff --git a/doc/src/sgml/system-views.sgml b/doc/src/sgml/system-views.sgml
index b96c653929..704d9e85eb 100644
--- a/doc/src/sgml/system-views.sgml
+++ b/doc/src/sgml/system-views.sgml
@@ -5892,6 +5892,30 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx
        Number of dead tuples that the vacuum could not remove because it failed to acquire a cleanup lock on their page.
       </para></entry>
      </row>
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>vm_new_frozen_pages</structfield> <type>bigint</type>
+      </para>
+      <para>
+       Number of heap pages newly marked all-frozen in the visibility map by the vacuum.
+      </para></entry>
+     </row>
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>vm_new_visible_pages</structfield> <type>bigint</type>
+      </para>
+      <para>
+       Number of heap pages newly marked all-visible in the visibility map by the vacuum.
+      </para></entry>
+     </row>
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>vm_new_visible_frozen_pages</structfield> <type>bigint</type>
+      </para>
+      <para>
+       Number of heap pages newly marked both all-visible and all-frozen in the visibility map by the vacuum.
+      </para></entry>
+     </row>
     </tbody>
    </tgroup>
   </table>
diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c
index 7abc83dbfd..91b6d9a9d9 100644
--- a/src/backend/access/heap/vacuumlazy.c
+++ b/src/backend/access/heap/vacuumlazy.c
@@ -633,6 +633,9 @@ accumulate_heap_vacuum_statistics(LVRelState *vacrel,
 	extVacStats->table.recently_dead_tuples = vacrel->recently_dead_tuples;
 	extVacStats->table.missed_dead_pages = vacrel->missed_dead_pages;
 	extVacStats->table.missed_dead_tuples = vacrel->missed_dead_tuples;
+	extVacStats->table.vm_new_frozen_pages = vacrel->new_all_frozen_pages;
+	extVacStats->table.vm_new_visible_pages = vacrel->new_all_visible_pages;
+	extVacStats->table.vm_new_visible_frozen_pages = vacrel->new_all_visible_all_frozen_pages;
 }
 
 /*
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 6f697ab390..21fa841f5a 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -1572,7 +1572,10 @@ CREATE VIEW pg_stat_vacuum_tables AS
         S.tuples_frozen AS tuples_frozen,
         S.recently_dead_tuples AS recently_dead_tuples,
         S.missed_dead_pages AS missed_dead_pages,
-        S.missed_dead_tuples AS missed_dead_tuples
+        S.missed_dead_tuples AS missed_dead_tuples,
+        S.vm_new_frozen_pages AS vm_new_frozen_pages,
+        S.vm_new_visible_pages AS vm_new_visible_pages,
+        S.vm_new_visible_frozen_pages AS vm_new_visible_frozen_pages
 
     FROM pg_class C JOIN
             pg_namespace N ON N.oid = C.relnamespace,
diff --git a/src/backend/utils/activity/pgstat_vacuum.c b/src/backend/utils/activity/pgstat_vacuum.c
index 8cc505ea86..4f6cbfd540 100644
--- a/src/backend/utils/activity/pgstat_vacuum.c
+++ b/src/backend/utils/activity/pgstat_vacuum.c
@@ -56,6 +56,9 @@ pgstat_accumulate_extvac_stats_relations(PgStat_VacuumRelationCounts *dst,
 		ACCUMULATE_SUBFIELD(table, recently_dead_tuples);
 		ACCUMULATE_SUBFIELD(table, missed_dead_pages);
 		ACCUMULATE_SUBFIELD(table, missed_dead_tuples);
+		ACCUMULATE_SUBFIELD(table, vm_new_frozen_pages);
+		ACCUMULATE_SUBFIELD(table, vm_new_visible_pages);
+		ACCUMULATE_SUBFIELD(table, vm_new_visible_frozen_pages);
 	}
 	else if (dst->type == PGSTAT_EXTVAC_INDEX)
 	{
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 742f4974d5..3acf0a7391 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -2374,7 +2374,7 @@ pg_stat_have_stats(PG_FUNCTION_ARGS)
 Datum
 pg_stat_get_vacuum_tables(PG_FUNCTION_ARGS)
 {
-#define PG_STAT_GET_VACUUM_TABLES_STATS_COLS 8
+#define PG_STAT_GET_VACUUM_TABLES_STATS_COLS 11
 
 	Oid			relid = PG_GETARG_OID(0);
 	PgStat_VacuumRelationCounts *extvacuum;
@@ -2407,6 +2407,9 @@ pg_stat_get_vacuum_tables(PG_FUNCTION_ARGS)
 	values[i++] = Int64GetDatum(extvacuum->table.recently_dead_tuples);
 	values[i++] = Int64GetDatum(extvacuum->table.missed_dead_pages);
 	values[i++] = Int64GetDatum(extvacuum->table.missed_dead_tuples);
+	values[i++] = Int64GetDatum(extvacuum->table.vm_new_frozen_pages);
+	values[i++] = Int64GetDatum(extvacuum->table.vm_new_visible_pages);
+	values[i++] = Int64GetDatum(extvacuum->table.vm_new_visible_frozen_pages);
 
 	Assert(i == PG_STAT_GET_VACUUM_TABLES_STATS_COLS);
 
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 6d683413a4..7d87b03239 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -12643,9 +12643,9 @@
   proname => 'pg_stat_get_vacuum_tables', prorows => 1000, provolatile => 's', prorettype => 'record', proisstrict => 'f',
   proretset => 't',
   proargtypes => 'oid',
-  proallargtypes => '{oid,oid,int8,int8,int8,int8,int8,int8,int8}',
-  proargmodes => '{i,o,o,o,o,o,o,o,o}',
-  proargnames => '{reloid,relid,pages_scanned,pages_removed,tuples_deleted,tuples_frozen,recently_dead_tuples,missed_dead_pages,missed_dead_tuples}',
+  proallargtypes => '{oid,oid,int8,int8,int8,int8,int8,int8,int8,int8,int8,int8}',
+  proargmodes => '{i,o,o,o,o,o,o,o,o,o,o,o}',
+  proargnames => '{reloid,relid,pages_scanned,pages_removed,tuples_deleted,tuples_frozen,recently_dead_tuples,missed_dead_pages,missed_dead_tuples,vm_new_frozen_pages,vm_new_visible_pages,vm_new_visible_frozen_pages}',
   prosrc => 'pg_stat_get_vacuum_tables' }
 
 # oid8 related functions
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index bdcf758441..f2bdef1463 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -222,6 +222,13 @@ typedef struct PgStat_VacuumRelationCounts
 			int64		missed_dead_tuples; /* tuples not pruned by vacuum due
 											 * to failure to get a cleanup
 											 * lock */
+			int64		vm_new_frozen_pages;	/* pages marked in VM as
+												 * frozen */
+			int64		vm_new_visible_pages;	/* pages marked in VM as
+												 * all-visible */
+			int64		vm_new_visible_frozen_pages;	/* pages marked in VM as
+														 * all-visible and
+														 * frozen */
 		}			table;
 		struct
 		{
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index a2b0472a2d..c30ff6c72f 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -2443,10 +2443,13 @@ pg_stat_vacuum_tables| SELECT n.nspname AS schemaname,
     s.tuples_frozen,
     s.recently_dead_tuples,
     s.missed_dead_pages,
-    s.missed_dead_tuples
+    s.missed_dead_tuples,
+    s.vm_new_frozen_pages,
+    s.vm_new_visible_pages,
+    s.vm_new_visible_frozen_pages
    FROM (pg_class c
      JOIN pg_namespace n ON ((n.oid = c.relnamespace))),
-    LATERAL pg_stat_get_vacuum_tables(c.oid) s(relid, pages_scanned, pages_removed, tuples_deleted, tuples_frozen, recently_dead_tuples, missed_dead_pages, missed_dead_tuples)
+    LATERAL pg_stat_get_vacuum_tables(c.oid) s(relid, pages_scanned, pages_removed, tuples_deleted, tuples_frozen, recently_dead_tuples, missed_dead_pages, missed_dead_tuples, vm_new_frozen_pages, vm_new_visible_pages, vm_new_visible_frozen_pages)
   WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char", 'm'::"char"]));
 pg_stat_wal| SELECT wal_records,
     wal_fpi,
diff --git a/src/test/regress/expected/vacuum_stats.out b/src/test/regress/expected/vacuum_stats.out
index f342f71c57..0b3354cfff 100644
--- a/src/test/regress/expected/vacuum_stats.out
+++ b/src/test/regress/expected/vacuum_stats.out
@@ -66,6 +66,39 @@ SELECT missed_dead_pages = 0 AS missed_dead_pages,
  t                 | t
 (1 row)
 
+-- visibility-map page transitions.  Removing the interleaved dead tuples lets
+-- VACUUM mark every heap page all-visible (vm_new_visible_pages > 0).  Whether
+-- VACUUM also freezes those pages (vm_new_frozen_pages /
+-- vm_new_visible_frozen_pages) depends on opportunistic freezing, which is not
+-- deterministic here, so those are only checked for being non-negative; the
+-- positive freeze path is covered by the dedicated VACUUM (FREEZE) scenario
+-- below.
+SELECT vm_new_frozen_pages >= 0 AS vm_new_frozen_pages,
+       vm_new_visible_pages > 0 AS vm_new_visible_pages,
+       vm_new_visible_frozen_pages >= 0 AS vm_new_visible_frozen_pages
+  FROM pg_stat_vacuum_tables WHERE relname = 'vacstat_t';
+ vm_new_frozen_pages | vm_new_visible_pages | vm_new_visible_frozen_pages 
+---------------------+----------------------+-----------------------------
+ t                   | t                    | t
+(1 row)
+
+-- freeze path: a dedicated VACUUM (FREEZE) marks freshly-loaded heap pages
+-- all-visible and all-frozen in one pass, so vm_new_visible_frozen_pages
+-- advances.  This restores the coverage of the former
+-- 053_vacuum_extending_freeze TAP test.
+CREATE TABLE vacstat_frz (x int)
+  WITH (autovacuum_enabled = off, fillfactor = 10);
+INSERT INTO vacstat_frz SELECT g FROM generate_series(1, 1000) g;
+VACUUM (FREEZE) vacstat_frz;
+SELECT vm_new_visible_pages > 0 AS vm_new_visible_pages,
+       vm_new_visible_frozen_pages > 0 AS vm_new_visible_frozen_pages
+  FROM pg_stat_vacuum_tables WHERE relname = 'vacstat_frz';
+ vm_new_visible_pages | vm_new_visible_frozen_pages 
+----------------------+-----------------------------
+ t                    | t
+(1 row)
+
+DROP TABLE vacstat_frz;
 -- per-index view: the primary key index is processed by the same VACUUM.
 -- No btree leaf empties out (interleaved deletions), so pages_deleted = 0,
 -- while every index entry for a removed heap tuple is deleted.
diff --git a/src/test/regress/sql/vacuum_stats.sql b/src/test/regress/sql/vacuum_stats.sql
index b6f0f55af0..db80ecf6a1 100644
--- a/src/test/regress/sql/vacuum_stats.sql
+++ b/src/test/regress/sql/vacuum_stats.sql
@@ -52,6 +52,31 @@ SELECT missed_dead_pages = 0 AS missed_dead_pages,
        missed_dead_tuples = 0 AS missed_dead_tuples
   FROM pg_stat_vacuum_tables WHERE relname = 'vacstat_t';
 
+-- visibility-map page transitions.  Removing the interleaved dead tuples lets
+-- VACUUM mark every heap page all-visible (vm_new_visible_pages > 0).  Whether
+-- VACUUM also freezes those pages (vm_new_frozen_pages /
+-- vm_new_visible_frozen_pages) depends on opportunistic freezing, which is not
+-- deterministic here, so those are only checked for being non-negative; the
+-- positive freeze path is covered by the dedicated VACUUM (FREEZE) scenario
+-- below.
+SELECT vm_new_frozen_pages >= 0 AS vm_new_frozen_pages,
+       vm_new_visible_pages > 0 AS vm_new_visible_pages,
+       vm_new_visible_frozen_pages >= 0 AS vm_new_visible_frozen_pages
+  FROM pg_stat_vacuum_tables WHERE relname = 'vacstat_t';
+
+-- freeze path: a dedicated VACUUM (FREEZE) marks freshly-loaded heap pages
+-- all-visible and all-frozen in one pass, so vm_new_visible_frozen_pages
+-- advances.  This restores the coverage of the former
+-- 053_vacuum_extending_freeze TAP test.
+CREATE TABLE vacstat_frz (x int)
+  WITH (autovacuum_enabled = off, fillfactor = 10);
+INSERT INTO vacstat_frz SELECT g FROM generate_series(1, 1000) g;
+VACUUM (FREEZE) vacstat_frz;
+SELECT vm_new_visible_pages > 0 AS vm_new_visible_pages,
+       vm_new_visible_frozen_pages > 0 AS vm_new_visible_frozen_pages
+  FROM pg_stat_vacuum_tables WHERE relname = 'vacstat_frz';
+DROP TABLE vacstat_frz;
+
 -- per-index view: the primary key index is processed by the same VACUUM.
 -- No btree leaf empties out (interleaved deletions), so pages_deleted = 0,
 -- while every index entry for a removed heap tuple is deleted.
-- 
2.39.5 (Apple Git-154)