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

From 11e2d4eac5d1d8225c23c8640454a66064825ca3 Mon Sep 17 00:00:00 2001
From: Alena Rybakina <alenka.rybakina@gmail.com>
Date: Wed, 17 Jun 2026 21:40:32 +0300
Subject: [PATCH 6/9] 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       |  3 ++
 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        |  5 ++-
 src/test/regress/expected/vacuum_stats.out | 39 ++++++++++++++++++++++
 src/test/regress/sql/vacuum_stats.sql      | 26 +++++++++++++++
 10 files changed, 116 insertions(+), 5 deletions(-)

diff --git a/doc/src/sgml/system-views.sgml b/doc/src/sgml/system-views.sgml
index 8bca17f3ef..1be54673bf 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>
      <row>
       <entry role="catalog_table_entry"><para role="column_definition">
        <structfield>wraparound_failsafe</structfield> <type>integer</type>
diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c
index 5089656c18..46b7418136 100644
--- a/src/backend/access/heap/vacuumlazy.c
+++ b/src/backend/access/heap/vacuumlazy.c
@@ -624,6 +624,9 @@ accumulate_heap_vacuum_statistics(LVRelState *vacrel, PgStat_VacuumRelationCount
 	extVacStats->type = PGSTAT_EXTVAC_TABLE;
 	extVacStats->table.pages_scanned = vacrel->scanned_pages;
 	extVacStats->table.pages_removed = vacrel->removed_pages;
+	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;
 	extVacStats->common.tuples_deleted = vacrel->tuples_deleted;
 	extVacStats->table.tuples_frozen = vacrel->tuples_frozen;
 	extVacStats->table.recently_dead_tuples = vacrel->recently_dead_tuples;
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 837e78d292..a7ac037bc3 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -1573,6 +1573,9 @@ CREATE VIEW pg_stat_vacuum_tables AS
         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.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,
         S.wraparound_failsafe AS wraparound_failsafe,
         S.wal_records AS wal_records,
         S.wal_fpi AS wal_fpi,
diff --git a/src/backend/utils/activity/pgstat_vacuum.c b/src/backend/utils/activity/pgstat_vacuum.c
index 8e099f3ade..ec3d50c59c 100644
--- a/src/backend/utils/activity/pgstat_vacuum.c
+++ b/src/backend/utils/activity/pgstat_vacuum.c
@@ -76,6 +76,9 @@ pgstat_accumulate_extvac_stats_relations(PgStat_VacuumRelationCounts *dst,
 	{
 		ACCUMULATE_SUBFIELD(table, pages_scanned);
 		ACCUMULATE_SUBFIELD(table, pages_removed);
+				ACCUMULATE_SUBFIELD(table, vm_new_frozen_pages);
+		ACCUMULATE_SUBFIELD(table, vm_new_visible_pages);
+		ACCUMULATE_SUBFIELD(table, vm_new_visible_frozen_pages);
 		ACCUMULATE_SUBFIELD(table, missed_dead_pages);
 			ACCUMULATE_SUBFIELD(table, tuples_frozen);
 		ACCUMULATE_SUBFIELD(table, recently_dead_tuples);
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 7927668bf8..694fd220b0 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 12
+#define PG_STAT_GET_VACUUM_TABLES_STATS_COLS 15
 
 	Oid			relid = PG_GETARG_OID(0);
 	PgStat_VacuumRelationCounts *extvacuum;
@@ -2408,6 +2408,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);
 	values[i++] = Int32GetDatum(extvacuum->common.wraparound_failsafe_count);
 	values[i++] = Int64GetDatum(extvacuum->common.wal_records);
 	values[i++] = Int64GetDatum(extvacuum->common.wal_fpi);
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 51cd0e5ed1..d2fb1b4ec0 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,int4,int8,int8,numeric}',
-  proargmodes => '{i,o,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,wraparound_failsafe,wal_records,wal_fpi,wal_bytes}',
+  proallargtypes => '{oid,oid,int8,int8,int8,int8,int8,int8,int8,int8,int8,int8,int4,int8,int8,numeric}',
+  proargmodes => '{i,o,o,o,o,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,wraparound_failsafe,wal_records,wal_fpi,wal_bytes}',
   prosrc => 'pg_stat_get_vacuum_tables' }
 
 # oid8 related functions
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index d041359e72..bcc84dbd41 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -233,6 +233,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 467c2e1843..27a17ee0c2 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -2457,13 +2457,16 @@ pg_stat_vacuum_tables| SELECT n.nspname AS schemaname,
     s.recently_dead_tuples,
     s.missed_dead_pages,
     s.missed_dead_tuples,
+    s.vm_new_frozen_pages,
+    s.vm_new_visible_pages,
+    s.vm_new_visible_frozen_pages,
     s.wraparound_failsafe,
     s.wal_records,
     s.wal_fpi,
     s.wal_bytes
    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, wraparound_failsafe, wal_records, wal_fpi, wal_bytes)
+    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, wraparound_failsafe, wal_records, wal_fpi, wal_bytes)
   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 c3024e5fc4..c996699804 100644
--- a/src/test/regress/expected/vacuum_stats.out
+++ b/src/test/regress/expected/vacuum_stats.out
@@ -84,6 +84,45 @@ SELECT recently_dead_tuples = 0 AS recently_dead_tuples,
  t                    | 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 pg_stat_force_next_flush();
+ pg_stat_force_next_flush 
+--------------------------
+ 
+(1 row)
+
+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;
 -- WAL metrics.  A vacuum that removes tuples always emits WAL
 -- (wal_records > 0, wal_bytes > 0).  wal_fpi depends on whether a checkpoint
 -- happened recently, so it is only checked for being non-negative here; the
diff --git a/src/test/regress/sql/vacuum_stats.sql b/src/test/regress/sql/vacuum_stats.sql
index 8ce60dac77..f106b7c37b 100644
--- a/src/test/regress/sql/vacuum_stats.sql
+++ b/src/test/regress/sql/vacuum_stats.sql
@@ -55,6 +55,32 @@ SELECT recently_dead_tuples = 0 AS recently_dead_tuples,
        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 pg_stat_force_next_flush();
+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;
+
 -- WAL metrics.  A vacuum that removes tuples always emits WAL
 -- (wal_records > 0, wal_bytes > 0).  wal_fpi depends on whether a checkpoint
 -- happened recently, so it is only checked for being non-negative here; the
-- 
2.39.5 (Apple Git-154)