0001-age-based-vacuum.patch

From a9e3a45bd3859fc3b89e25291c07fc56a29a4759 Mon Sep 17 00:00:00 2001
From: gurmokh <gurmokh@gmail.com>
Date: Tue, 21 Apr 2026 08:29:37 +0100
Subject: [PATCH] age based vacuum

---
 src/backend/access/common/reloptions.c | 11 +++++
 src/backend/access/heap/vacuumlazy.c   |  9 +++-
 src/backend/commands/vacuum.c          |  1 +
 src/backend/postmaster/autovacuum.c    | 67 ++++++++++++++++++++++----
 src/include/commands/vacuum.h          |  1 +
 src/include/postmaster/autovacuum.h    |  1 +
 src/include/utils/rel.h                |  1 +
 7 files changed, 80 insertions(+), 11 deletions(-)

diff --git a/src/backend/access/common/reloptions.c b/src/backend/access/common/reloptions.c
index 50747c16396..d834d6d8f84 100644
--- a/src/backend/access/common/reloptions.c
+++ b/src/backend/access/common/reloptions.c
@@ -388,6 +388,15 @@ static relopt_int intRelOpts[] =
 
 static relopt_real realRelOpts[] =
 {
+	{
+		{
+			"autovacuum_age_scale_factor",
+			"Age as a percentage of autovacuum_max_freeze_age",
+			RELOPT_KIND_HEAP | RELOPT_KIND_TOAST,
+			ShareUpdateExclusiveLock
+		},
+		-1, 0.0, 100.0
+	},
 	{
 		{
 			"autovacuum_vacuum_cost_delay",
@@ -1894,6 +1903,8 @@ default_reloptions(Datum reloptions, bool validate, relopt_kind kind)
 		offsetof(StdRdOptions, autovacuum) + offsetof(AutoVacOpts, multixact_freeze_max_age)},
 		{"autovacuum_multixact_freeze_table_age", RELOPT_TYPE_INT,
 		offsetof(StdRdOptions, autovacuum) + offsetof(AutoVacOpts, multixact_freeze_table_age)},
+		{"autovacuum_age_scale_factor", RELOPT_TYPE_REAL,
+		offsetof(StdRdOptions, autovacuum) + offsetof(AutoVacOpts, autovacuum_age_scale_factor)},
 		{"log_autovacuum_min_duration", RELOPT_TYPE_INT,
 		offsetof(StdRdOptions, autovacuum) + offsetof(AutoVacOpts, log_min_duration)},
 		{"toast_tuple_target", RELOPT_TYPE_INT,
diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c
index 8fbaf126756..f2f94266f16 100644
--- a/src/backend/access/heap/vacuumlazy.c
+++ b/src/backend/access/heap/vacuumlazy.c
@@ -995,7 +995,14 @@ heap_vacuum_rel(Relation rel, VacuumParams *params,
 				 * implies aggressive.  Produce distinct output for the corner
 				 * case all the same, just in case.
 				 */
-				if (vacrel->aggressive)
+				if (params->is_age_based)
+				{
+					if (vacrel->aggressive)
+						msgfmt = _("automatic aggressive vacuum (age-based proactive) of table \"%s.%s.%s\": index scans: %d\n");
+					else
+						msgfmt = _("automatic vacuum (age-based proactive) of table \"%s.%s.%s\": index scans: %d\n");
+				}
+				else if (vacrel->aggressive)
 					msgfmt = _("automatic aggressive vacuum to prevent wraparound of table \"%s.%s.%s\": index scans: %d\n");
 				else
 					msgfmt = _("automatic vacuum to prevent wraparound of table \"%s.%s.%s\": index scans: %d\n");
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index be863db81cb..40ead6af182 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -418,6 +418,7 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel)
 
 	/* user-invoked vacuum is never "for wraparound" */
 	params.is_wraparound = false;
+	params.is_age_based = false;
 
 	/* user-invoked vacuum uses VACOPT_VERBOSE instead of log_min_duration */
 	params.log_min_duration = -1;
diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c
index 66620fb4755..f732a3f94e8 100644
--- a/src/backend/postmaster/autovacuum.c
+++ b/src/backend/postmaster/autovacuum.c
@@ -129,6 +129,7 @@ int			autovacuum_anl_thresh;
 double		autovacuum_anl_scale;
 int			autovacuum_freeze_max_age;
 int			autovacuum_multixact_freeze_max_age;
+double		autovacuum_age_scale;
 
 double		autovacuum_vac_cost_delay;
 int			autovacuum_vac_cost_limit;
@@ -336,13 +337,14 @@ static autovac_table *table_recheck_autovac(Oid relid, HTAB *table_toast_map,
 static void recheck_relation_needs_vacanalyze(Oid relid, AutoVacOpts *avopts,
 											  Form_pg_class classForm,
 											  int effective_multixact_freeze_max_age,
-											  bool *dovacuum, bool *doanalyze, bool *wraparound);
+											  bool *dovacuum, bool *doanalyze,
+											  bool *wraparound, bool *is_age_based);
 static void relation_needs_vacanalyze(Oid relid, AutoVacOpts *relopts,
 									  Form_pg_class classForm,
 									  PgStat_StatTabEntry *tabentry,
 									  int effective_multixact_freeze_max_age,
-									  bool *dovacuum, bool *doanalyze, bool *wraparound);
-
+									  bool *dovacuum, bool *doanalyze,
+									  bool *wraparound, bool *is_age_based);
 static void autovacuum_do_vac_analyze(autovac_table *tab,
 									  BufferAccessStrategy bstrategy);
 static AutoVacOpts *extract_autovac_opts(HeapTuple tup,
@@ -2000,6 +2002,8 @@ do_autovacuum(void)
 		bool		dovacuum;
 		bool		doanalyze;
 		bool		wraparound;
+		bool		age_based;
+
 
 		if (classForm->relkind != RELKIND_RELATION &&
 			classForm->relkind != RELKIND_MATVIEW)
@@ -2040,7 +2044,8 @@ do_autovacuum(void)
 		/* Check if it needs vacuum or analyze */
 		relation_needs_vacanalyze(relid, relopts, classForm, tabentry,
 								  effective_multixact_freeze_max_age,
-								  &dovacuum, &doanalyze, &wraparound);
+								  &dovacuum, &doanalyze, &wraparound,
+								  &age_based);
 
 		/* Relations that need work are added to table_oids */
 		if (dovacuum || doanalyze)
@@ -2100,6 +2105,7 @@ do_autovacuum(void)
 		bool		dovacuum;
 		bool		doanalyze;
 		bool		wraparound;
+		bool		age_based;
 
 		/*
 		 * We cannot safely process other backends' temp tables, so skip 'em.
@@ -2132,7 +2138,8 @@ do_autovacuum(void)
 
 		relation_needs_vacanalyze(relid, relopts, classForm, tabentry,
 								  effective_multixact_freeze_max_age,
-								  &dovacuum, &doanalyze, &wraparound);
+								  &dovacuum, &doanalyze, &wraparound,
+								  &age_based);
 
 		/* ignore analyze for toast tables */
 		if (dovacuum)
@@ -2756,6 +2763,7 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map,
 	bool		doanalyze;
 	autovac_table *tab = NULL;
 	bool		wraparound;
+	bool		age_based;
 	AutoVacOpts *avopts;
 	bool		free_avopts = false;
 
@@ -2785,7 +2793,8 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map,
 
 	recheck_relation_needs_vacanalyze(relid, avopts, classForm,
 									  effective_multixact_freeze_max_age,
-									  &dovacuum, &doanalyze, &wraparound);
+									  &dovacuum, &doanalyze, &wraparound,
+									  &age_based);
 
 	/* OK, it needs something done */
 	if (doanalyze || dovacuum)
@@ -2857,6 +2866,7 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map,
 		tab->at_params.multixact_freeze_min_age = multixact_freeze_min_age;
 		tab->at_params.multixact_freeze_table_age = multixact_freeze_table_age;
 		tab->at_params.is_wraparound = wraparound;
+		tab->at_params.is_age_based = age_based;
 		tab->at_params.log_min_duration = log_min_duration;
 		tab->at_params.toast_parent = InvalidOid;
 
@@ -2903,7 +2913,8 @@ recheck_relation_needs_vacanalyze(Oid relid,
 								  int effective_multixact_freeze_max_age,
 								  bool *dovacuum,
 								  bool *doanalyze,
-								  bool *wraparound)
+								  bool *wraparound,
+								  bool *is_age_based)
 {
 	PgStat_StatTabEntry *tabentry;
 
@@ -2913,7 +2924,8 @@ recheck_relation_needs_vacanalyze(Oid relid,
 
 	relation_needs_vacanalyze(relid, avopts, classForm, tabentry,
 							  effective_multixact_freeze_max_age,
-							  dovacuum, doanalyze, wraparound);
+							  dovacuum, doanalyze, wraparound,
+							  is_age_based);
 
 	/* Release tabentry to avoid leakage */
 	if (tabentry)
@@ -2972,9 +2984,11 @@ relation_needs_vacanalyze(Oid relid,
  /* output params below */
 						  bool *dovacuum,
 						  bool *doanalyze,
-						  bool *wraparound)
+						  bool *wraparound,
+						  bool *is_age_based)
 {
 	bool		force_vacuum;
+	bool		force_vacuum_age = false;
 	bool		av_enabled;
 
 	/* constants from reloptions or GUC variables */
@@ -2984,7 +2998,9 @@ relation_needs_vacanalyze(Oid relid,
 				anl_base_thresh;
 	float4		vac_scale_factor,
 				vac_ins_scale_factor,
-				anl_scale_factor;
+				anl_scale_factor,
+				age_scale_factor;
+
 
 	/* thresholds calculated from above constants */
 	float4		vacthresh,
@@ -3017,6 +3033,10 @@ relation_needs_vacanalyze(Oid relid,
 		? relopts->vacuum_scale_factor
 		: autovacuum_vac_scale;
 
+	age_scale_factor = (relopts && relopts->autovacuum_age_scale_factor >= 0)
+		? relopts->autovacuum_age_scale_factor
+		: autovacuum_age_scale;
+
 	vac_base_thresh = (relopts && relopts->vacuum_threshold >= 0)
 		? relopts->vacuum_threshold
 		: autovacuum_vac_thresh;
@@ -3069,8 +3089,35 @@ relation_needs_vacanalyze(Oid relid,
 			multiForceLimit -= FirstMultiXactId;
 		force_vacuum = MultiXactIdIsValid(relminmxid) &&
 			MultiXactIdPrecedes(relminmxid, multiForceLimit);
+
+		/*
+		 * +	 * Proactively vacuum if the table's XID age exceeds +	 *
+		 * age_scale_factor * freeze_max_age.  This triggers vacuum before the
+		 * +	 * hard wraparound anti-freeze limit is reached. +
+		 */
+		if (!force_vacuum && age_scale_factor > 0 &&
+			TransactionIdIsNormal(relfrozenxid))
+		{
+			int			age_thresh = (int) (age_scale_factor * freeze_max_age);
+			TransactionId ageForceLimit = recentXid - age_thresh;
+			int			xid_age;
+
+			if (ageForceLimit < FirstNormalTransactionId)
+				ageForceLimit -= FirstNormalTransactionId;
+			force_vacuum_age = TransactionIdPrecedes(relfrozenxid, ageForceLimit);
+			if (force_vacuum_age)
+				force_vacuum = true;
+
+			xid_age = (int32) (recentXid - relfrozenxid);
+			elog(DEBUG3, "%s: age-based check: xid age %d, threshold %d (%.1f%% of freeze_max_age %d)%s",
+				 NameStr(classForm->relname),
+				 xid_age, age_thresh,
+				 (double) age_scale_factor * 100.0, freeze_max_age,
+				 force_vacuum_age ? " -- triggering proactive vacuum" : "");
+		}
 	}
 	*wraparound = force_vacuum;
+	*is_age_based = force_vacuum_age;
 
 	/* User disabled it in pg_class.reloptions?  (But ignore if at risk) */
 	if (!av_enabled && !force_vacuum)
diff --git a/src/include/commands/vacuum.h b/src/include/commands/vacuum.h
index bc37a80dc74..95d4adabcc6 100644
--- a/src/include/commands/vacuum.h
+++ b/src/include/commands/vacuum.h
@@ -224,6 +224,7 @@ typedef struct VacuumParams
 	int			multixact_freeze_table_age; /* multixact age at which to scan
 											 * whole table */
 	bool		is_wraparound;	/* force a for-wraparound vacuum */
+	bool		is_age_based;
 	int			log_min_duration;	/* minimum execution threshold in ms at
 									 * which autovacuum is logged, -1 to use
 									 * default */
diff --git a/src/include/postmaster/autovacuum.h b/src/include/postmaster/autovacuum.h
index e8135f41a1c..a2c3a9f4f82 100644
--- a/src/include/postmaster/autovacuum.h
+++ b/src/include/postmaster/autovacuum.h
@@ -43,6 +43,7 @@ extern PGDLLIMPORT int autovacuum_freeze_max_age;
 extern PGDLLIMPORT int autovacuum_multixact_freeze_max_age;
 extern PGDLLIMPORT double autovacuum_vac_cost_delay;
 extern PGDLLIMPORT int autovacuum_vac_cost_limit;
+extern PGDLLIMPORT double autovacuum_age_scale;
 
 /* autovacuum launcher PID, only valid when worker is shutting down */
 extern PGDLLIMPORT int AutovacuumLauncherPid;
diff --git a/src/include/utils/rel.h b/src/include/utils/rel.h
index b552359915f..8d1e5dfd37f 100644
--- a/src/include/utils/rel.h
+++ b/src/include/utils/rel.h
@@ -327,6 +327,7 @@ typedef struct AutoVacOpts
 	float8		vacuum_scale_factor;
 	float8		vacuum_ins_scale_factor;
 	float8		analyze_scale_factor;
+	float8		autovacuum_age_scale_factor;
 } AutoVacOpts;
 
 /* StdRdOptions->vacuum_index_cleanup values */
-- 
2.50.1 (Apple Git-155)