0008-Extended-vacuum-statistics-per-relation-buffer-acces.patch

From 690556053f3abce9b91abed7ff29e309dfbc8ed6 Mon Sep 17 00:00:00 2001
From: Alena Rybakina <alenka.rybakina@gmail.com>
Date: Wed, 17 Jun 2026 21:49:18 +0300
Subject: [PATCH 8/9] Extended vacuum statistics: per-relation buffer access
 for tables and indexes

Expose the per-relation buffer access counters in pg_stat_vacuum_tables and
pg_stat_vacuum_indexes, with documentation and regression coverage:

  rel_blks_read  this relation's blocks read from disk by the vacuum
  rel_blks_hit   this relation's blocks found in shared buffers

Unlike total_blks_*, which count all shared-buffer access during the vacuum,
these are restricted to the target relation's own blocks.
---
 doc/src/sgml/system-views.sgml             | 32 ++++++++++++++++++++++
 src/backend/access/heap/vacuumlazy.c       | 28 +++++++++++++++++++
 src/backend/catalog/system_views.sql       |  4 +++
 src/backend/utils/activity/pgstat_vacuum.c |  3 ++
 src/backend/utils/adt/pgstatfuncs.c        |  9 ++++--
 src/include/catalog/pg_proc.dat            | 12 ++++----
 src/include/pgstat.h                       |  4 +++
 src/test/regress/expected/rules.out        |  8 ++++--
 src/test/regress/expected/vacuum_stats.out | 18 ++++++++++--
 src/test/regress/sql/vacuum_stats.sql      |  8 ++++++
 10 files changed, 113 insertions(+), 13 deletions(-)

diff --git a/doc/src/sgml/system-views.sgml b/doc/src/sgml/system-views.sgml
index f37a3b64c1..2a93896d5d 100644
--- a/doc/src/sgml/system-views.sgml
+++ b/doc/src/sgml/system-views.sgml
@@ -5948,6 +5948,22 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx
        Number of shared buffer blocks written out by the vacuum.
       </para></entry>
      </row>
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>rel_blks_read</structfield> <type>bigint</type>
+      </para>
+      <para>
+       Number of this relation's blocks read from disk by the vacuum.
+      </para></entry>
+     </row>
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>rel_blks_hit</structfield> <type>bigint</type>
+      </para>
+      <para>
+       Number of this relation's blocks found in shared buffers by the vacuum.
+      </para></entry>
+     </row>
      <row>
       <entry role="catalog_table_entry"><para role="column_definition">
        <structfield>wraparound_failsafe</structfield> <type>integer</type>
@@ -6103,6 +6119,22 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx
        Number of shared buffer blocks written out while vacuuming the index.
       </para></entry>
      </row>
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>rel_blks_read</structfield> <type>bigint</type>
+      </para>
+      <para>
+       Number of blocks of this index read from disk by the vacuum.
+      </para></entry>
+     </row>
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>rel_blks_hit</structfield> <type>bigint</type>
+      </para>
+      <para>
+       Number of block hits within this index during the vacuum.
+      </para></entry>
+     </row>
      <row>
       <entry role="catalog_table_entry"><para role="column_definition">
        <structfield>wal_records</structfield> <type>bigint</type>
diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c
index 1d6db2a91d..683bfcf9e8 100644
--- a/src/backend/access/heap/vacuumlazy.c
+++ b/src/backend/access/heap/vacuumlazy.c
@@ -519,6 +519,19 @@ extvac_stats_start(Relation rel, LVExtStatCounters * counters)
 
 	counters->walusage = pgWalUsage;
 	counters->bufusage = pgBufferUsage;
+	counters->blocks_fetched = 0;
+	counters->blocks_hit = 0;
+
+	if (!rel->pgstat_info || !pgstat_track_counts)
+
+		/*
+		 * if something goes wrong or user doesn't want to track a database
+		 * activity - just suppress it.
+		 */
+		return;
+
+	counters->blocks_fetched = rel->pgstat_info->counts.blocks_fetched;
+	counters->blocks_hit = rel->pgstat_info->counts.blocks_hit;
 }
 
 /* ----------
@@ -556,6 +569,19 @@ extvac_stats_end(Relation rel, LVExtStatCounters * counters,
 	report->wal_records += walusage.wal_records;
 	report->wal_fpi += walusage.wal_fpi;
 	report->wal_bytes += walusage.wal_bytes;
+
+	if (!rel->pgstat_info || !pgstat_track_counts)
+
+		/*
+		 * if something goes wrong or an user doesn't want to track a database
+		 * activity - just suppress it.
+		 */
+		return;
+
+	report->blks_fetched +=
+		rel->pgstat_info->counts.blocks_fetched - counters->blocks_fetched;
+	report->blks_hit +=
+		rel->pgstat_info->counts.blocks_hit - counters->blocks_hit;
 }
 
 void
@@ -685,6 +711,8 @@ extvac_accumulate_idx_report(PgStat_VacuumRelationCounts * dst,
 	dst->common.total_blks_hit += src->common.total_blks_hit;
 	dst->common.total_blks_dirtied += src->common.total_blks_dirtied;
 	dst->common.total_blks_written += src->common.total_blks_written;
+	dst->common.blks_fetched += src->common.blks_fetched;
+	dst->common.blks_hit += src->common.blks_hit;
 	dst->common.wal_records += src->common.wal_records;
 	dst->common.wal_fpi += src->common.wal_fpi;
 	dst->common.wal_bytes += src->common.wal_bytes;
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 1ee74f9273..d0a58f221c 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -1580,6 +1580,8 @@ CREATE VIEW pg_stat_vacuum_tables AS
         S.total_blks_hit AS total_blks_hit,
         S.total_blks_dirtied AS total_blks_dirtied,
         S.total_blks_written AS total_blks_written,
+        S.rel_blks_read AS rel_blks_read,
+        S.rel_blks_hit AS rel_blks_hit,
         S.wraparound_failsafe AS wraparound_failsafe,
         S.wal_records AS wal_records,
         S.wal_fpi AS wal_fpi,
@@ -1605,6 +1607,8 @@ CREATE VIEW pg_stat_vacuum_indexes AS
             S.total_blks_hit AS total_blks_hit,
             S.total_blks_dirtied AS total_blks_dirtied,
             S.total_blks_written AS total_blks_written,
+            S.rel_blks_read AS rel_blks_read,
+            S.rel_blks_hit AS rel_blks_hit,
             S.wal_records AS wal_records,
             S.wal_fpi AS wal_fpi,
             S.wal_bytes AS wal_bytes
diff --git a/src/backend/utils/activity/pgstat_vacuum.c b/src/backend/utils/activity/pgstat_vacuum.c
index 2bb44da510..b1c747f255 100644
--- a/src/backend/utils/activity/pgstat_vacuum.c
+++ b/src/backend/utils/activity/pgstat_vacuum.c
@@ -43,6 +43,9 @@ pgstat_accumulate_common(PgStat_CommonCounts *dst, const PgStat_CommonCounts *sr
 	ACCUMULATE_FIELD(total_blks_dirtied);
 	ACCUMULATE_FIELD(total_blks_written);
 
+	ACCUMULATE_FIELD(blks_fetched);
+	ACCUMULATE_FIELD(blks_hit);
+
 	ACCUMULATE_FIELD(wal_records);
 	ACCUMULATE_FIELD(wal_fpi);
 	ACCUMULATE_FIELD(wal_bytes);
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index eeca140354..7473ae1eb6 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 19
+#define PG_STAT_GET_VACUUM_TABLES_STATS_COLS 21
 
 	Oid			relid = PG_GETARG_OID(0);
 	PgStat_VacuumRelationCounts *extvacuum;
@@ -2415,6 +2415,8 @@ pg_stat_get_vacuum_tables(PG_FUNCTION_ARGS)
 	values[i++] = Int64GetDatum(extvacuum->common.total_blks_hit);
 	values[i++] = Int64GetDatum(extvacuum->common.total_blks_dirtied);
 	values[i++] = Int64GetDatum(extvacuum->common.total_blks_written);
+	values[i++] = Int64GetDatum(extvacuum->common.blks_fetched - extvacuum->common.blks_hit);
+	values[i++] = Int64GetDatum(extvacuum->common.blks_hit);
 	values[i++] = Int32GetDatum(extvacuum->common.wraparound_failsafe_count);
 	values[i++] = Int64GetDatum(extvacuum->common.wal_records);
 	values[i++] = Int64GetDatum(extvacuum->common.wal_fpi);
@@ -2436,7 +2438,7 @@ pg_stat_get_vacuum_tables(PG_FUNCTION_ARGS)
 Datum
 pg_stat_get_vacuum_indexes(PG_FUNCTION_ARGS)
 {
-#define PG_STAT_GET_VACUUM_INDEX_STATS_COLS 10
+#define PG_STAT_GET_VACUUM_INDEX_STATS_COLS 12
 
 	Oid			relid = PG_GETARG_OID(0);
 	PgStat_VacuumRelationCounts *extvacuum;
@@ -2470,6 +2472,9 @@ pg_stat_get_vacuum_indexes(PG_FUNCTION_ARGS)
 	values[i++] = Int64GetDatum(extvacuum->common.total_blks_dirtied);
 	values[i++] = Int64GetDatum(extvacuum->common.total_blks_written);
 
+	values[i++] = Int64GetDatum(extvacuum->common.blks_fetched - extvacuum->common.blks_hit);
+	values[i++] = Int64GetDatum(extvacuum->common.blks_hit);
+
 	values[i++] = Int64GetDatum(extvacuum->common.wal_records);
 	values[i++] = Int64GetDatum(extvacuum->common.wal_fpi);
 	snprintf(buf, sizeof buf, UINT64_FORMAT, extvacuum->common.wal_bytes);
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 718ba80a3a..743ce9dfd6 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,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,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,total_blks_read,total_blks_hit,total_blks_dirtied,total_blks_written,wraparound_failsafe,wal_records,wal_fpi,wal_bytes}',
+  proallargtypes => '{oid,oid,int8,int8,int8,int8,int8,int8,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,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,total_blks_read,total_blks_hit,total_blks_dirtied,total_blks_written,rel_blks_read,rel_blks_hit,wraparound_failsafe,wal_records,wal_fpi,wal_bytes}',
   prosrc => 'pg_stat_get_vacuum_tables' }
 
 # oid8 related functions
@@ -12718,9 +12718,9 @@
   proname => 'pg_stat_get_vacuum_indexes', prorows => 1000, provolatile => 's', prorettype => 'record', proisstrict => 'f',
   proretset => 't',
   proargtypes => 'oid',
-  proallargtypes => '{oid,oid,int8,int8,int8,int8,int8,int8,int8,int8,numeric}',
-  proargmodes => '{i,o,o,o,o,o,o,o,o,o,o}',
-  proargnames => '{reloid,relid,pages_deleted,tuples_deleted,total_blks_read,total_blks_hit,total_blks_dirtied,total_blks_written,wal_records,wal_fpi,wal_bytes}',
+  proallargtypes => '{oid,oid,int8,int8,int8,int8,int8,int8,int8,int8,int8,int8,numeric}',
+  proargmodes => '{i,o,o,o,o,o,o,o,o,o,o,o,o}',
+  proargnames => '{reloid,relid,pages_deleted,tuples_deleted,total_blks_read,total_blks_hit,total_blks_dirtied,total_blks_written,rel_blks_read,rel_blks_hit,wal_records,wal_fpi,wal_bytes}',
   prosrc => 'pg_stat_get_vacuum_indexes' },
 { oid => '8005',
   descr => 'pg_stat_get_vacuum_database returns vacuum stats values for database',
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 92f8b9b8ec..18fa9ac5ba 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -181,6 +181,10 @@ typedef struct PgStat_CommonCounts
 	int64		total_blks_dirtied;
 	int64		total_blks_written;
 
+	/* heap blocks */
+	int64		blks_fetched;
+	int64		blks_hit;
+
 	/* WAL */
 	int64		wal_records;
 	int64		wal_fpi;
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 2b879192aa..c650cfd664 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -2446,6 +2446,8 @@ pg_stat_vacuum_indexes| SELECT c.oid AS relid,
     s.total_blks_hit,
     s.total_blks_dirtied,
     s.total_blks_written,
+    s.rel_blks_read,
+    s.rel_blks_hit,
     s.wal_records,
     s.wal_fpi,
     s.wal_bytes
@@ -2453,7 +2455,7 @@ pg_stat_vacuum_indexes| SELECT c.oid AS relid,
      JOIN pg_index x ON ((c.oid = x.indrelid)))
      JOIN pg_class i ON ((i.oid = x.indexrelid)))
      LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))),
-    LATERAL pg_stat_get_vacuum_indexes(i.oid) s(relid, pages_deleted, tuples_deleted, total_blks_read, total_blks_hit, total_blks_dirtied, total_blks_written, wal_records, wal_fpi, wal_bytes)
+    LATERAL pg_stat_get_vacuum_indexes(i.oid) s(relid, pages_deleted, tuples_deleted, total_blks_read, total_blks_hit, total_blks_dirtied, total_blks_written, rel_blks_read, rel_blks_hit, wal_records, wal_fpi, wal_bytes)
   WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char", 'm'::"char"]));
 pg_stat_vacuum_tables| SELECT n.nspname AS schemaname,
     c.relname,
@@ -2472,13 +2474,15 @@ pg_stat_vacuum_tables| SELECT n.nspname AS schemaname,
     s.total_blks_hit,
     s.total_blks_dirtied,
     s.total_blks_written,
+    s.rel_blks_read,
+    s.rel_blks_hit,
     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, vm_new_frozen_pages, vm_new_visible_pages, vm_new_visible_frozen_pages, total_blks_read, total_blks_hit, total_blks_dirtied, total_blks_written, 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, total_blks_read, total_blks_hit, total_blks_dirtied, total_blks_written, rel_blks_read, rel_blks_hit, 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 87f49cb76c..3d823ef7b6 100644
--- a/src/test/regress/expected/vacuum_stats.out
+++ b/src/test/regress/expected/vacuum_stats.out
@@ -138,6 +138,16 @@ SELECT total_blks_read >= 0 AS total_blks_read,
  t               | t              | t                  | t
 (1 row)
 
+-- per-relation buffer access.  The heap is read through the buffer cache
+-- (rel_blks_hit > 0); rel_blks_read depends on the run-time cache state.
+SELECT rel_blks_read >= 0 AS rel_blks_read,
+       rel_blks_hit > 0 AS rel_blks_hit
+  FROM pg_stat_vacuum_tables WHERE relname = 'vacstat_t';
+ rel_blks_read | rel_blks_hit 
+---------------+--------------
+ t             | t
+(1 row)
+
 -- 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
@@ -199,13 +209,15 @@ SELECT indexrelname,
        total_blks_hit > 0 AS total_blks_hit,
        total_blks_dirtied >= 0 AS total_blks_dirtied,
        total_blks_written >= 0 AS total_blks_written,
+       rel_blks_read >= 0 AS rel_blks_read,
+       rel_blks_hit > 0 AS rel_blks_hit,
        wal_records > 0 AS wal_records,
        wal_fpi >= 0 AS wal_fpi,
        wal_bytes > 0 AS wal_bytes
   FROM pg_stat_vacuum_indexes WHERE relname = 'vacstat_t' ORDER BY indexrelname;
-  indexrelname  | pages_deleted | tuples_deleted | total_blks_read | total_blks_hit | total_blks_dirtied | total_blks_written | wal_records | wal_fpi | wal_bytes 
-----------------+---------------+----------------+-----------------+----------------+--------------------+--------------------+-------------+---------+-----------
- vacstat_t_pkey | t             | t              | t               | t              | t                  | t                  | t           | t       | t
+  indexrelname  | pages_deleted | tuples_deleted | total_blks_read | total_blks_hit | total_blks_dirtied | total_blks_written | rel_blks_read | rel_blks_hit | wal_records | wal_fpi | wal_bytes 
+----------------+---------------+----------------+-----------------+----------------+--------------------+--------------------+---------------+--------------+-------------+---------+-----------
+ vacstat_t_pkey | t             | t              | t               | t              | t                  | t                  | t             | t            | t           | t       | t
 (1 row)
 
 -- index page-deletion path: deleting a contiguous key range empties whole
diff --git a/src/test/regress/sql/vacuum_stats.sql b/src/test/regress/sql/vacuum_stats.sql
index 6e364d183c..40f7d0985d 100644
--- a/src/test/regress/sql/vacuum_stats.sql
+++ b/src/test/regress/sql/vacuum_stats.sql
@@ -92,6 +92,12 @@ SELECT total_blks_read >= 0 AS total_blks_read,
        total_blks_written >= 0 AS total_blks_written
   FROM pg_stat_vacuum_tables WHERE relname = 'vacstat_t';
 
+-- per-relation buffer access.  The heap is read through the buffer cache
+-- (rel_blks_hit > 0); rel_blks_read depends on the run-time cache state.
+SELECT rel_blks_read >= 0 AS rel_blks_read,
+       rel_blks_hit > 0 AS rel_blks_hit
+  FROM pg_stat_vacuum_tables WHERE relname = 'vacstat_t';
+
 -- 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
@@ -136,6 +142,8 @@ SELECT indexrelname,
        total_blks_hit > 0 AS total_blks_hit,
        total_blks_dirtied >= 0 AS total_blks_dirtied,
        total_blks_written >= 0 AS total_blks_written,
+       rel_blks_read >= 0 AS rel_blks_read,
+       rel_blks_hit > 0 AS rel_blks_hit,
        wal_records > 0 AS wal_records,
        wal_fpi >= 0 AS wal_fpi,
        wal_bytes > 0 AS wal_bytes
-- 
2.39.5 (Apple Git-154)