v29-0002-Machinery-for-grabbing-extended-vacuum-statistics.patch

From f78253895ef7e489e59389d60bc596b7cf42a19e Mon Sep 17 00:00:00 2001
From: Alena Rybakina <lena.ribackina@yandex.ru>
Date: Mon, 2 Mar 2026 23:09:32 +0300
Subject: [PATCH 2/3] Machinery for grabbing extended vacuum statistics.

Vacuum statistics are stored separately from regular relation and
database statistics. Dedicated PGSTAT_KIND_VACUUM_RELATION and
PGSTAT_KIND_VACUUM_DB entries in the cumulative statistics system
allocate memory for vacuum-specific metrics. Statistics are gathered
separately for tables and indexes according to vacuum phases. The
ExtVacReport union and type field distinguish PGSTAT_EXTVAC_TABLE vs
PGSTAT_EXTVAC_INDEX. Heap vacuum stats are sent to the cumulative
statistics system after vacuum has processed the indexes. Database
vacuum statistics aggregate per-table and per-index statistics within
the database.

Common for tables, indexes, and database: total_blks_hit, total_blks_read
and total_blks_dirtied are the number of hit, miss and dirtied pages
in shared buffers during a vacuum operation. total_blks_dirtied counts
only pages dirtied by this vacuum. blk_read_time and blk_write_time
track access and flush time for buffer pages; blk_write_time can stay
zero if no flushes occurred. total_time is wall-clock time from start
to finish, including idle time (I/O and lock waits). delay_time is
total vacuum sleep time in vacuum delay points.

Both table and index report tuples_deleted (tuples removed by the vacuum),
pages_removed (pages by which relation storage was reduced) and
pages_deleted (freed pages; file size may remain unchanged). These are
independent of WAL and buffer stats and are not summed at the database
level.

Table only: pages_frozen (pages marked all-frozen in the visibility map),
pages_all_visible (pages marked all-visible in the visibility map),
wraparound_failsafe_count (number of urgent anti-wraparound vacuums).

Table and database share wraparound_failsafe (count of urgent anti-wraparound
cleanups). Database only: errors (number of error-level errors caught
during vacuum).

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/vacuumlazy.c  | 140 ++++++++++++++++++++++++++
 src/backend/commands/vacuum.c         |   4 +
 src/backend/commands/vacuumparallel.c |   8 ++
 src/include/commands/vacuum.h         |  28 ++++++
 src/include/pgstat.h                  |  58 +++++++++++
 5 files changed, 238 insertions(+)

diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c
index 82c5b28e0ad..04b087e2a5c 100644
--- a/src/backend/access/heap/vacuumlazy.c
+++ b/src/backend/access/heap/vacuumlazy.c
@@ -282,6 +282,8 @@ typedef struct LVRelState
 	/* Error reporting state */
 	char	   *dbname;
 	char	   *relnamespace;
+	Oid			reloid;
+	Oid			indoid;
 	char	   *relname;
 	char	   *indname;		/* Current index name */
 	BlockNumber blkno;			/* used only for heap operations */
@@ -402,6 +404,15 @@ typedef struct LVRelState
 	 * been permanently disabled.
 	 */
 	BlockNumber eager_scan_remaining_fails;
+
+	int32		wraparound_failsafe_count;	/* # of emergency vacuums for
+											 * anti-wraparound */
+
+	/*
+	 * We need to accumulate index statistics for later subtraction from heap
+	 * stats.
+	 */
+	PgStat_VacuumRelationCounts extVacReportIdx;
 } LVRelState;
 
 
@@ -488,6 +499,107 @@ static void restore_vacuum_error_info(LVRelState *vacrel,
 									  const LVSavedErrInfo *saved_vacrel);
 
 
+/* Extended vacuum statistics functions */
+
+/*
+ * extvac_stats_start - Save cut-off values before start of relation processing.
+ */
+static void
+extvac_stats_start(Relation rel, LVExtStatCounters * counters)
+{
+	memset(counters, 0, sizeof(LVExtStatCounters));
+	counters->starttime = GetCurrentTimestamp();
+	counters->walusage = pgWalUsage;
+	counters->bufusage = pgBufferUsage;
+	counters->VacuumDelayTime = VacuumDelayTime;
+	counters->blocks_fetched = 0;
+	counters->blocks_hit = 0;
+
+	if (rel->pgstat_info && pgstat_track_counts)
+	{
+		counters->blocks_fetched = rel->pgstat_info->counts.blocks_fetched;
+		counters->blocks_hit = rel->pgstat_info->counts.blocks_hit;
+	}
+}
+
+/*
+ * extvac_stats_end - Finish extended vacuum statistic gathering and form report.
+ */
+static void
+extvac_stats_end(Relation rel, LVExtStatCounters * counters,
+				 PgStat_CommonCounts * report)
+{
+	WalUsage	walusage;
+	BufferUsage bufusage;
+	TimestampTz endtime;
+	long		secs;
+	int			usecs;
+
+	memset(report, 0, sizeof(PgStat_CommonCounts));
+	memset(&walusage, 0, sizeof(WalUsage));
+	WalUsageAccumDiff(&walusage, &pgWalUsage, &counters->walusage);
+	memset(&bufusage, 0, sizeof(BufferUsage));
+	BufferUsageAccumDiff(&bufusage, &pgBufferUsage, &counters->bufusage);
+	endtime = GetCurrentTimestamp();
+	TimestampDifference(counters->starttime, endtime, &secs, &usecs);
+
+	report->total_blks_read = bufusage.local_blks_read + bufusage.shared_blks_read;
+	report->total_blks_hit = bufusage.local_blks_hit + bufusage.shared_blks_hit;
+	report->total_blks_dirtied = bufusage.local_blks_dirtied + bufusage.shared_blks_dirtied;
+	report->total_blks_written = bufusage.shared_blks_written;
+	report->wal_records = walusage.wal_records;
+	report->wal_fpi = walusage.wal_fpi;
+	report->wal_bytes = walusage.wal_bytes;
+	report->blk_read_time = INSTR_TIME_GET_MILLISEC(bufusage.local_blk_read_time) +
+		INSTR_TIME_GET_MILLISEC(bufusage.shared_blk_read_time);
+	report->blk_write_time = INSTR_TIME_GET_MILLISEC(bufusage.local_blk_write_time) +
+		INSTR_TIME_GET_MILLISEC(bufusage.shared_blk_write_time);
+	report->delay_time = VacuumDelayTime - counters->VacuumDelayTime;
+	report->total_time = secs * 1000.0 + usecs / 1000.0;
+
+	if (rel->pgstat_info && pgstat_track_counts)
+	{
+		report->blks_fetched = rel->pgstat_info->counts.blocks_fetched - counters->blocks_fetched;
+		report->blks_hit = rel->pgstat_info->counts.blocks_hit - counters->blocks_hit;
+	}
+}
+
+/*
+ * extvac_stats_start_idx - Start extended vacuum statistic gathering for index.
+ */
+void
+extvac_stats_start_idx(Relation rel, IndexBulkDeleteResult *stats,
+					   LVExtStatCountersIdx * counters)
+{
+	extvac_stats_start(rel, &counters->common);
+	counters->pages_deleted = 0;
+	counters->tuples_removed = 0;
+
+	if (stats != NULL)
+	{
+		counters->tuples_removed = stats->tuples_removed;
+		counters->pages_deleted = stats->pages_deleted;
+	}
+}
+
+
+/*
+ * extvac_stats_end_idx - Finish extended vacuum statistic gathering for index.
+ */
+void
+extvac_stats_end_idx(Relation rel, IndexBulkDeleteResult *stats,
+					 LVExtStatCountersIdx * counters, PgStat_VacuumRelationCounts * report)
+{
+	memset(report, 0, sizeof(PgStat_VacuumRelationCounts));
+	extvac_stats_end(rel, &counters->common, &report->common);
+	report->type = PGSTAT_EXTVAC_INDEX;
+
+	if (stats != NULL)
+	{
+		report->common.tuples_deleted = stats->tuples_removed - counters->tuples_removed;
+		report->index.pages_deleted = stats->pages_deleted - counters->pages_deleted;
+	}
+}
 
 /*
  * Helper to set up the eager scanning state for vacuuming a single relation.
@@ -646,7 +758,10 @@ heap_vacuum_rel(Relation rel, const VacuumParams params,
 	ErrorContextCallback errcallback;
 	char	  **indnames = NULL;
 	Size		dead_items_max_bytes = 0;
+	LVExtStatCounters extVacCounters;
+	PgStat_VacuumRelationCounts extVacReport;
 
+	memset(&extVacReport, 0, sizeof(extVacReport));
 	verbose = (params.options & VACOPT_VERBOSE) != 0;
 	instrument = (verbose || (AmAutoVacuumWorkerProcess() &&
 							  params.log_vacuum_min_duration >= 0));
@@ -663,6 +778,8 @@ heap_vacuum_rel(Relation rel, const VacuumParams params,
 	/* Used for instrumentation and stats report */
 	starttime = GetCurrentTimestamp();
 
+	extvac_stats_start(rel, &extVacCounters);
+
 	pgstat_progress_start_command(PROGRESS_COMMAND_VACUUM,
 								  RelationGetRelid(rel));
 	if (AmAutoVacuumWorkerProcess())
@@ -690,7 +807,9 @@ heap_vacuum_rel(Relation rel, const VacuumParams params,
 	vacrel->dbname = get_database_name(MyDatabaseId);
 	vacrel->relnamespace = get_namespace_name(RelationGetNamespace(rel));
 	vacrel->relname = pstrdup(RelationGetRelationName(rel));
+	vacrel->reloid = RelationGetRelid(rel);
 	vacrel->indname = NULL;
+	memset(&vacrel->extVacReportIdx, 0, sizeof(vacrel->extVacReportIdx));
 	vacrel->phase = VACUUM_ERRCB_PHASE_UNKNOWN;
 	vacrel->verbose = verbose;
 	errcallback.callback = vacuum_error_callback;
@@ -801,6 +920,9 @@ heap_vacuum_rel(Relation rel, const VacuumParams params,
 	vacrel->rel_pages = orig_rel_pages = RelationGetNumberOfBlocks(rel);
 	vacrel->vistest = GlobalVisTestFor(rel);
 
+	/* Initialize wraparound failsafe count for extended vacuum stats */
+	vacrel->wraparound_failsafe_count = 0;
+
 	/* Initialize state used to track oldest extant XID/MXID */
 	vacrel->NewRelfrozenXid = vacrel->cutoffs.OldestXmin;
 	vacrel->NewRelminMxid = vacrel->cutoffs.OldestMxact;
@@ -977,6 +1099,7 @@ heap_vacuum_rel(Relation rel, const VacuumParams params,
 						 vacrel->recently_dead_tuples +
 						 vacrel->missed_dead_tuples,
 						 starttime);
+
 	pgstat_progress_end_command();
 
 	if (instrument)
@@ -3018,6 +3141,7 @@ lazy_check_wraparound_failsafe(LVRelState *vacrel)
 		int64		progress_val[3] = {0, 0, PROGRESS_VACUUM_MODE_FAILSAFE};
 
 		VacuumFailsafeActive = true;
+		vacrel->wraparound_failsafe_count++;
 
 		/*
 		 * Abandon use of a buffer access strategy to allow use of all of
@@ -3129,6 +3253,10 @@ lazy_vacuum_one_index(Relation indrel, IndexBulkDeleteResult *istat,
 {
 	IndexVacuumInfo ivinfo;
 	LVSavedErrInfo saved_err_info;
+	LVExtStatCountersIdx extVacCounters;
+	PgStat_VacuumRelationCounts extVacReport;
+
+	extvac_stats_start_idx(indrel, istat, &extVacCounters);
 
 	ivinfo.index = indrel;
 	ivinfo.heaprel = vacrel->rel;
@@ -3147,6 +3275,7 @@ lazy_vacuum_one_index(Relation indrel, IndexBulkDeleteResult *istat,
 	 */
 	Assert(vacrel->indname == NULL);
 	vacrel->indname = pstrdup(RelationGetRelationName(indrel));
+	vacrel->indoid = RelationGetRelid(indrel);
 	update_vacuum_error_info(vacrel, &saved_err_info,
 							 VACUUM_ERRCB_PHASE_VACUUM_INDEX,
 							 InvalidBlockNumber, InvalidOffsetNumber);
@@ -3155,6 +3284,9 @@ lazy_vacuum_one_index(Relation indrel, IndexBulkDeleteResult *istat,
 	istat = vac_bulkdel_one_index(&ivinfo, istat, vacrel->dead_items,
 								  vacrel->dead_items_info);
 
+	memset(&extVacReport, 0, sizeof(extVacReport));
+	extvac_stats_end_idx(indrel, istat, &extVacCounters, &extVacReport);
+
 	/* Revert to the previous phase information for error traceback */
 	restore_vacuum_error_info(vacrel, &saved_err_info);
 	pfree(vacrel->indname);
@@ -3179,6 +3311,10 @@ lazy_cleanup_one_index(Relation indrel, IndexBulkDeleteResult *istat,
 {
 	IndexVacuumInfo ivinfo;
 	LVSavedErrInfo saved_err_info;
+	LVExtStatCountersIdx extVacCounters;
+	PgStat_VacuumRelationCounts extVacReport;
+
+	extvac_stats_start_idx(indrel, istat, &extVacCounters);
 
 	ivinfo.index = indrel;
 	ivinfo.heaprel = vacrel->rel;
@@ -3198,12 +3334,16 @@ lazy_cleanup_one_index(Relation indrel, IndexBulkDeleteResult *istat,
 	 */
 	Assert(vacrel->indname == NULL);
 	vacrel->indname = pstrdup(RelationGetRelationName(indrel));
+	vacrel->indoid = RelationGetRelid(indrel);
 	update_vacuum_error_info(vacrel, &saved_err_info,
 							 VACUUM_ERRCB_PHASE_INDEX_CLEANUP,
 							 InvalidBlockNumber, InvalidOffsetNumber);
 
 	istat = vac_cleanup_one_index(&ivinfo, istat);
 
+	memset(&extVacReport, 0, sizeof(extVacReport));
+	extvac_stats_end_idx(indrel, istat, &extVacCounters, &extVacReport);
+
 	/* Revert to the previous phase information for error traceback */
 	restore_vacuum_error_info(vacrel, &saved_err_info);
 	pfree(vacrel->indname);
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index 62c1ebdfd9b..faeab06d2bc 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -118,6 +118,9 @@ pg_atomic_uint32 *VacuumSharedCostBalance = NULL;
 pg_atomic_uint32 *VacuumActiveNWorkers = NULL;
 int			VacuumCostBalanceLocal = 0;
 
+/* Cumulative storage to report total vacuum delay time (msec). */
+double		VacuumDelayTime = 0;
+
 /* non-export function prototypes */
 static List *expand_vacuum_rel(VacuumRelation *vrel,
 							   MemoryContext vac_context, int options);
@@ -2541,6 +2544,7 @@ vacuum_delay_point(bool is_analyze)
 			exit(1);
 
 		VacuumCostBalance = 0;
+		VacuumDelayTime += msec;
 
 		/*
 		 * Balance and update limit values for autovacuum workers. We must do
diff --git a/src/backend/commands/vacuumparallel.c b/src/backend/commands/vacuumparallel.c
index 279108ca89f..7a85c644749 100644
--- a/src/backend/commands/vacuumparallel.c
+++ b/src/backend/commands/vacuumparallel.c
@@ -869,6 +869,8 @@ parallel_vacuum_process_one_index(ParallelVacuumState *pvs, Relation indrel,
 	IndexBulkDeleteResult *istat = NULL;
 	IndexBulkDeleteResult *istat_res;
 	IndexVacuumInfo ivinfo;
+	LVExtStatCountersIdx extVacCounters;
+	PgStat_VacuumRelationCounts extVacReport;
 
 	/*
 	 * Update the pointer to the corresponding bulk-deletion result if someone
@@ -877,6 +879,8 @@ parallel_vacuum_process_one_index(ParallelVacuumState *pvs, Relation indrel,
 	if (indstats->istat_updated)
 		istat = &(indstats->istat);
 
+	extvac_stats_start_idx(indrel, istat, &extVacCounters);
+
 	ivinfo.index = indrel;
 	ivinfo.heaprel = pvs->heaprel;
 	ivinfo.analyze_only = false;
@@ -905,6 +909,9 @@ parallel_vacuum_process_one_index(ParallelVacuumState *pvs, Relation indrel,
 				 RelationGetRelationName(indrel));
 	}
 
+	memset(&extVacReport, 0, sizeof(extVacReport));
+	extvac_stats_end_idx(indrel, istat_res, &extVacCounters, &extVacReport);
+
 	/*
 	 * Copy the index bulk-deletion result returned from ambulkdelete and
 	 * amvacuumcleanup to the DSM segment if it's the first cycle because they
@@ -1055,6 +1062,7 @@ parallel_vacuum_main(dsm_segment *seg, shm_toc *toc)
 	/* Set cost-based vacuum delay */
 	VacuumUpdateCosts();
 	VacuumCostBalance = 0;
+	VacuumDelayTime = 0;
 	VacuumCostBalanceLocal = 0;
 	VacuumSharedCostBalance = &(shared->cost_balance);
 	VacuumActiveNWorkers = &(shared->active_nworkers);
diff --git a/src/include/commands/vacuum.h b/src/include/commands/vacuum.h
index e885a4b9c77..c50ce51e9da 100644
--- a/src/include/commands/vacuum.h
+++ b/src/include/commands/vacuum.h
@@ -25,6 +25,7 @@
 #include "storage/buf.h"
 #include "storage/lock.h"
 #include "utils/relcache.h"
+#include "pgstat.h"
 
 /*
  * Flags for amparallelvacuumoptions to control the participation of bulkdelete
@@ -333,6 +334,33 @@ extern PGDLLIMPORT pg_atomic_uint32 *VacuumSharedCostBalance;
 extern PGDLLIMPORT pg_atomic_uint32 *VacuumActiveNWorkers;
 extern PGDLLIMPORT int VacuumCostBalanceLocal;
 
+/* Cumulative storage to report total vacuum delay time (msec). */
+extern PGDLLIMPORT double VacuumDelayTime;
+
+/* Counters for extended vacuum statistics gathering */
+typedef struct LVExtStatCounters
+{
+	TimestampTz starttime;
+	WalUsage	walusage;
+	BufferUsage bufusage;
+	double		VacuumDelayTime;
+	PgStat_Counter blocks_fetched;
+	PgStat_Counter blocks_hit;
+} LVExtStatCounters;
+
+typedef struct LVExtStatCountersIdx
+{
+	LVExtStatCounters common;
+	int64		pages_deleted;
+	int64		tuples_removed;
+} LVExtStatCountersIdx;
+
+extern void extvac_stats_start_idx(Relation rel, IndexBulkDeleteResult *stats,
+								   LVExtStatCountersIdx *counters);
+extern void extvac_stats_end_idx(Relation rel, IndexBulkDeleteResult *stats,
+								 LVExtStatCountersIdx *counters,
+								 PgStat_VacuumRelationCounts *report);
+
 extern PGDLLIMPORT bool VacuumFailsafeActive;
 extern PGDLLIMPORT double vacuum_cost_delay;
 extern PGDLLIMPORT int vacuum_cost_limit;
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 02fbb8480dd..7fe8e5468b8 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -92,6 +92,63 @@ typedef struct PgStat_FunctionCounts
 /*
  * Working state needed to accumulate per-function-call timing statistics.
  */
+/*
+ * Type of entry: table (heap), index, or database aggregate.
+ */
+typedef enum ExtVacReportType
+{
+	PGSTAT_EXTVAC_INVALID = 0,
+	PGSTAT_EXTVAC_TABLE = 1,
+	PGSTAT_EXTVAC_INDEX = 2,
+	PGSTAT_EXTVAC_DB = 3,
+}			ExtVacReportType;
+
+typedef struct PgStat_CommonCounts
+{
+	int64		total_blks_read;
+	int64		total_blks_hit;
+	int64		total_blks_dirtied;
+	int64		total_blks_written;
+	int64		blks_fetched;
+	int64		blks_hit;
+	int64		wal_records;
+	int64		wal_fpi;
+	uint64		wal_bytes;
+	double		blk_read_time;
+	double		blk_write_time;
+	double		delay_time;
+	double		total_time;
+	int32		wraparound_failsafe_count;
+	int32		interrupts_count;
+	int64		tuples_deleted;
+}			PgStat_CommonCounts;
+
+typedef struct PgStat_VacuumRelationCounts
+{
+	PgStat_CommonCounts common;
+	ExtVacReportType type;
+	union
+	{
+		struct
+		{
+			int64		tuples_frozen;
+			int64		recently_dead_tuples;
+			int64		missed_dead_tuples;
+			int64		pages_scanned;
+			int64		pages_removed;
+			int64		vm_new_frozen_pages;
+			int64		vm_new_visible_pages;
+			int64		vm_new_visible_frozen_pages;
+			int64		missed_dead_pages;
+			int64		index_vacuum_count;
+		}			table;
+		struct
+		{
+			int64		pages_deleted;
+		}			index;
+	};
+}			PgStat_VacuumRelationCounts;
+
 typedef struct PgStat_FunctionCallUsage
 {
 	/* Link to function's hashtable entry (must still be there at exit!) */
@@ -680,6 +737,7 @@ extern void pgstat_unlink_relation(Relation rel);
 extern void pgstat_report_vacuum(Relation rel, PgStat_Counter livetuples,
 								 PgStat_Counter deadtuples,
 								 TimestampTz starttime);
+
 extern void pgstat_report_analyze(Relation rel,
 								  PgStat_Counter livetuples, PgStat_Counter deadtuples,
 								  bool resetcounter, TimestampTz starttime);
-- 
2.39.5 (Apple Git-154)