0001-Add-memory-usage-reporting-to-VACUUM-VERBOSE.patch

From af6f39420115046e5493211a5337c28ea094141a Mon Sep 17 00:00:00 2001
From: TatsuyaKawata <kawatatatsuya0913@gmail.com>
Date: Mon, 17 Nov 2025 00:12:42 +0900
Subject: [PATCH] Add memory usage reporting to VACUUM VERBOSE

---
 src/backend/access/heap/vacuumlazy.c | 46 ++++++++++++++++++++++++++++
 1 file changed, 46 insertions(+)

diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c
index deb9a3dc0d1..350101ae54a 100644
--- a/src/backend/access/heap/vacuumlazy.c
+++ b/src/backend/access/heap/vacuumlazy.c
@@ -355,6 +355,10 @@ typedef struct LVRelState
 	int64		recently_dead_tuples;	/* # dead, but not yet removable */
 	int64		missed_dead_tuples; /* # removable, but not removed */
 
+	/* Memory usage tracking for dead_items */
+	Size		peak_dead_items_bytes;	/* Peak memory used by dead_items */
+	int			dead_items_resets;	/* # of times dead_items was reset */
+
 	/* State maintained by heap_vac_scan_next_block() */
 	BlockNumber current_block;	/* last block returned */
 	BlockNumber next_unskippable_block; /* next unskippable block */
@@ -850,6 +854,19 @@ heap_vacuum_rel(Relation rel, const VacuumParams params,
 	 */
 	lazy_scan_heap(vacrel);
 
+	/*
+	 * Capture final peak memory usage before cleanup.
+	 * This is especially important for parallel vacuum where workers may have
+	 * added items that weren't tracked in the leader's peak_dead_items_bytes.
+	 */
+	if (vacrel->dead_items != NULL)
+	{
+		Size		final_bytes = TidStoreMemoryUsage(vacrel->dead_items);
+
+		if (final_bytes > vacrel->peak_dead_items_bytes)
+			vacrel->peak_dead_items_bytes = final_bytes;
+	}
+
 	/*
 	 * Free resources managed by dead_items_alloc.  This ends parallel mode in
 	 * passing when necessary.
@@ -975,6 +992,7 @@ heap_vacuum_rel(Relation rel, const VacuumParams params,
 			int64		total_blks_hit;
 			int64		total_blks_read;
 			int64		total_blks_dirtied;
+			int			vac_work_mem;
 
 			TimestampDifference(starttime, endtime, &secs_dur, &usecs_dur);
 			memset(&walusage, 0, sizeof(WalUsage));
@@ -1154,6 +1172,19 @@ heap_vacuum_rel(Relation rel, const VacuumParams params,
 							 walusage.wal_bytes,
 							 walusage.wal_fpi_bytes,
 							 walusage.wal_buffers_full);
+
+			/* Report memory usage for dead_items tracking */
+			vac_work_mem = AmAutoVacuumWorkerProcess() &&
+						autovacuum_work_mem != -1 ?
+						autovacuum_work_mem : maintenance_work_mem;
+
+			appendStringInfo(&buf,
+							_("memory usage: peak %.2f MB of %.2f MB allowed (%.1f%%), %d reset(s)\n"),
+							(double) vacrel->peak_dead_items_bytes / (1024.0 * 1024.0),
+							(double) vac_work_mem / 1024.0,
+							100.0 * vacrel->peak_dead_items_bytes / (vac_work_mem * 1024.0),
+							vacrel->dead_items_resets);
+
 			appendStringInfo(&buf, _("system usage: %s"), pg_rusage_show(&ru0));
 
 			ereport(verbose ? INFO : LOG,
@@ -3553,6 +3584,10 @@ dead_items_alloc(LVRelState *vacrel, int nworkers)
 		{
 			vacrel->dead_items = parallel_vacuum_get_dead_items(vacrel->pvs,
 																&vacrel->dead_items_info);
+
+			/* Initialize memory usage tracking */
+			vacrel->peak_dead_items_bytes = 0;
+			vacrel->dead_items_resets = 0;
 			return;
 		}
 	}
@@ -3568,6 +3603,10 @@ dead_items_alloc(LVRelState *vacrel, int nworkers)
 	vacrel->dead_items_info = dead_items_info;
 
 	vacrel->dead_items = TidStoreCreateLocal(dead_items_info->max_bytes, true);
+
+	/* Initialize memory usage tracking */
+	vacrel->peak_dead_items_bytes = 0;
+	vacrel->dead_items_resets = 0;
 }
 
 /*
@@ -3590,6 +3629,10 @@ dead_items_add(LVRelState *vacrel, BlockNumber blkno, OffsetNumber *offsets,
 	prog_val[0] = vacrel->dead_items_info->num_items;
 	prog_val[1] = TidStoreMemoryUsage(vacrel->dead_items);
 	pgstat_progress_update_multi_param(2, prog_index, prog_val);
+
+	/* Track peak memory usage */
+	if (prog_val[1] > vacrel->peak_dead_items_bytes)
+		vacrel->peak_dead_items_bytes = prog_val[1];
 }
 
 /*
@@ -3598,6 +3641,9 @@ dead_items_add(LVRelState *vacrel, BlockNumber blkno, OffsetNumber *offsets,
 static void
 dead_items_reset(LVRelState *vacrel)
 {
+	/* Increment reset counter */
+	vacrel->dead_items_resets++;
+
 	if (ParallelVacuumIsActive(vacrel))
 	{
 		parallel_vacuum_reset_dead_items(vacrel->pvs);
-- 
2.34.1