public inbox for [email protected]
help / color / mirror / Atom feedFrom: Mok <[email protected]>
To: [email protected] <[email protected]>
Subject: New vacuum config to avoid anti wraparound vacuums
Date: Wed, 22 Apr 2026 20:18:55 +0000
Message-ID: <CeFiI3W4yXPznxyELYARQ5H6Cfy1HJIm0Tsn5CDVINyORFVwKfIMwc5EKHYlVzAjy5vgHgfA7X7lgfA3CnD0GchBvuanglpr4xd-IoyjPWY=@protonmail.com> (raw)
Hello hackers.
Hope you are all keeping well.
I have an idea for managing vacuums. When managing vacuums it can sometimes be a struggle to manage the config settings for them, especially when trying to avoid anti wraparound vacuums. Some tables are massive and needs scale factor settings in the fractions of percent, sometimes, with no guarantee that enough actions cross that threshold. Increasing the autovacuum_freeze_max_age to keep up with an growing number of transactions across an instance is just kicking the can down the road. Engineers run backfills that throw off any calculations you may use to work out these scale factors and using the same values across an instance is too simple while managing individually for tables can be too messy. Or you run a cron type job so you don't have to think about it at all.
So I thought a nice way to avoid anti wraparound vacuums is to use the age of the table as the trigger.
We could add, yet another, vacuum configuration. Eg autovacuum_age_scale_factor as a percentage of autovacuum_freeze_max_age.
For example, set to 0.8 a 'standard' vacuum would be triggered when the table reached 160million with a default 200million setting.
As a noddy example.
create table a ( a int) ;
create table b ( a int) ;
alter table a set ( autovacuum_freeze_max_age=100000, autovacuum_age_scale_factor=0.1) // 10% of max age
Then run some activity table b keeping a inactive to increase its age, but not trigger a vacuum using scale factor or threshold settings.
When the table reaches ~10000 age it will trigger a pre-emptive vacuum to prevent wraparound vacuum occurring.
The log entry for the event would appear like:
[56957] LOG: automatic vacuum (age-based proactive) of table "postgres.public.atable": index scans: 0
I thought I would put the idea out there. I've attached a patch file if anyone wants to try it. Its built against the REL_18_3 tag.
Happy Postgresing
Gurmokh
Attachments:
[application/octet-stream] 0001-age-based-vacuum.patch (11.3K, 2-0001-age-based-vacuum.patch)
download | inline diff:
From a9e3a45bd3859fc3b89e25291c07fc56a29a4759 Mon Sep 17 00:00:00 2001
From: gurmokh <[email protected]>
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)
view thread (9+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: New vacuum config to avoid anti wraparound vacuums
In-Reply-To: <CeFiI3W4yXPznxyELYARQ5H6Cfy1HJIm0Tsn5CDVINyORFVwKfIMwc5EKHYlVzAjy5vgHgfA7X7lgfA3CnD0GchBvuanglpr4xd-IoyjPWY=@protonmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox