public inbox for [email protected]
help / color / mirror / Atom feedNew vacuum config to avoid anti wraparound vacuums
9+ messages / 5 participants
[nested] [flat]
* New vacuum config to avoid anti wraparound vacuums
@ 2026-04-22 20:18 Mok <[email protected]>
2026-04-23 03:44 ` Re: New vacuum config to avoid anti wraparound vacuums David Rowley <[email protected]>
0 siblings, 1 reply; 9+ messages in thread
From: Mok @ 2026-04-22 20:18 UTC (permalink / raw)
To: [email protected] <[email protected]>
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)
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: New vacuum config to avoid anti wraparound vacuums
2026-04-22 20:18 New vacuum config to avoid anti wraparound vacuums Mok <[email protected]>
@ 2026-04-23 03:44 ` David Rowley <[email protected]>
2026-04-23 13:04 ` Re: New vacuum config to avoid anti wraparound vacuums Mok <[email protected]>
0 siblings, 1 reply; 9+ messages in thread
From: David Rowley @ 2026-04-23 03:44 UTC (permalink / raw)
To: Mok <[email protected]>; +Cc: [email protected] <[email protected]>
On Thu, 23 Apr 2026 at 08:19, Mok <[email protected]> wrote:
> For example, set to 0.8 a 'standard' vacuum would be triggered when the table reached 160million with a default 200million setting.
If that's what you want, why wouldn't you set the
autovacuum_freeze_max_age to 160million?
There are some subtle differences between a "to-prevent-wraparound"
autovacuum and a normal one. Is it one of those differences that makes
you want the extra config option?
> 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
It would be good to get a bit more detail on what you think this
solves that cannot be solved by the existing GUCs and reloptions.
With any luck, PG19 should make things a bit easier to get on top of
vacuuming work during off-peak hours. If you, for some reason, wanted
to vacuum tables to get some freezing work done, just use psql to run
something along the lines of:
select 'vacuum ' || relname from pg_stat_autovacuum_scores where
schemaname <> 'pg_toast' and xid_score > 0.8 or mxid_score > 0.8;
\gexec
Depending on the desired outcomes, you may or may not want to zero
vacuum_freeze_min_age, or use vacuum freeze.
David
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: New vacuum config to avoid anti wraparound vacuums
2026-04-22 20:18 New vacuum config to avoid anti wraparound vacuums Mok <[email protected]>
2026-04-23 03:44 ` Re: New vacuum config to avoid anti wraparound vacuums David Rowley <[email protected]>
@ 2026-04-23 13:04 ` Mok <[email protected]>
2026-04-23 14:09 ` Re: New vacuum config to avoid anti wraparound vacuums David Rowley <[email protected]>
0 siblings, 1 reply; 9+ messages in thread
From: Mok @ 2026-04-23 13:04 UTC (permalink / raw)
To: David Rowley <[email protected]>; +Cc: [email protected] <[email protected]>
On Thursday, April 23rd, 2026 at 4:44 AM, David Rowley <[email protected]> wrote:
> On Thu, 23 Apr 2026 at 08:19, Mok <[email protected]> wrote:
> > For example, set to 0.8 a 'standard' vacuum would be triggered when the table reached 160million with a default 200million setting.
>
> If that's what you want, why wouldn't you set the
> autovacuum_freeze_max_age to 160million?
Because that would trigger a 'to-prevent-wraparound' vacuum, which is what this change is trying to avoid.
>
> There are some subtle differences between a "to-prevent-wraparound"
> autovacuum and a normal one. Is it one of those differences that makes
> you want the extra config option?
>
> > 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
>
> It would be good to get a bit more detail on what you think this
> solves that cannot be solved by the existing GUCs and reloptions.
The aim of this config is prevent 'anti wraparound' vacuums from occurring in the first place.
Existing settings work from the bottom up. Eg. N number of modifications + threshold is what triggers an autovacuum. These work great in terms of garbage collection, space reusing and reclaiming. However there is no guarantee that these conditions will be met before the table reaches autovacuum_freeze_max_age and a wraparound vacuum occurs.
What this change proposes is to use the actual age of the table to trigger the autovacuum that is not a wraparound one thus reducing the resource contention that occurs when one runs.
I think you mis-understood my example above. In that example an autovacuum is triggered 20million tx's before a wraparound would have occurred. Which then reduces the age of the table. Effectively greatly reducing the possibility of that table reaching 200million.
>
> With any luck, PG19 should make things a bit easier to get on top of
> vacuuming work during off-peak hours. If you, for some reason, wanted
> to vacuum tables to get some freezing work done, just use psql to run
> something along the lines of:
>
> select 'vacuum ' || relname from pg_stat_autovacuum_scores where
> schemaname <> 'pg_toast' and xid_score > 0.8 or mxid_score > 0.8;
> \gexec
>
> Depending on the desired outcomes, you may or may not want to zero
> vacuum_freeze_min_age, or use vacuum freeze.
This is a cool new view for v19. But I don't think it provides what I am trying achieve. It would be an indicator of the autovacuum daemons next targets. Using it to determine triggering a vacuum would require a job of some sort. This config change would have the database handle this for you.
>
> David
>
Gurmokh
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: New vacuum config to avoid anti wraparound vacuums
2026-04-22 20:18 New vacuum config to avoid anti wraparound vacuums Mok <[email protected]>
2026-04-23 03:44 ` Re: New vacuum config to avoid anti wraparound vacuums David Rowley <[email protected]>
2026-04-23 13:04 ` Re: New vacuum config to avoid anti wraparound vacuums Mok <[email protected]>
@ 2026-04-23 14:09 ` David Rowley <[email protected]>
2026-04-24 03:57 ` Re: New vacuum config to avoid anti wraparound vacuums wenhui qiu <[email protected]>
2026-04-24 06:15 ` Re: New vacuum config to avoid anti wraparound vacuums Mok <[email protected]>
0 siblings, 2 replies; 9+ messages in thread
From: David Rowley @ 2026-04-23 14:09 UTC (permalink / raw)
To: Mok <[email protected]>; +Cc: [email protected] <[email protected]>
On Fri, 24 Apr 2026 at 01:04, Mok <[email protected]> wrote:
>
> On Thursday, April 23rd, 2026 at 4:44 AM, David Rowley <[email protected]> wrote:
>
> > On Thu, 23 Apr 2026 at 08:19, Mok <[email protected]> wrote:
> > > For example, set to 0.8 a 'standard' vacuum would be triggered when the table reached 160million with a default 200million setting.
> >
> > If that's what you want, why wouldn't you set the
> > autovacuum_freeze_max_age to 160million?
>
> Because that would trigger a 'to-prevent-wraparound' vacuum, which is what this change is trying to avoid.
Yes, it would. Why do you want to prevent them? I believe a few people
have been alarmed in the past about the "to prevent wraparound" text
in pg_stat_activity or when they saw those words in the logs. The
default 200 million autovacuum_freeze_max_age setting triggers an
autovacuum when it's less than 10% of the way into exhausting the
transaction space for the table. What you're proposing with an
autovacuum_age_scale_factor of 0.1 sounds like it would result in an
auto-vacuum when only 1% of the transaction ID space is consumed! I
think you're under the false impression that these anti-wraparound
vacuums are bad. They're not.
There's some documentation that might be worthwhile reading in [1].
David
[1] https://www.postgresql.org/docs/18/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: New vacuum config to avoid anti wraparound vacuums
2026-04-22 20:18 New vacuum config to avoid anti wraparound vacuums Mok <[email protected]>
2026-04-23 03:44 ` Re: New vacuum config to avoid anti wraparound vacuums David Rowley <[email protected]>
2026-04-23 13:04 ` Re: New vacuum config to avoid anti wraparound vacuums Mok <[email protected]>
2026-04-23 14:09 ` Re: New vacuum config to avoid anti wraparound vacuums David Rowley <[email protected]>
@ 2026-04-24 03:57 ` wenhui qiu <[email protected]>
1 sibling, 0 replies; 9+ messages in thread
From: wenhui qiu @ 2026-04-24 03:57 UTC (permalink / raw)
To: Mok <[email protected]>; +Cc: [email protected] <[email protected]>
Hi Mok
> 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.
I think you can refer to these two parameters
https://www.postgresql.org/docs/18/runtime-config-vacuum.html#GUC-AUTOVACUUM-VACUUM-MAX-THRESHOLD
https://www.postgresql.org/docs/18/runtime-config-vacuum.html#
vacuum_freeze_table_age
If you're concerned about wraparound, you can reduce the value of
`VACUUM-VACUUM-MAX-THRESHOLD`,vacuum_freeze_table_age, Shouldn't you be
focusing on why the blocked table can't be vacuum-freeze?
Thanks
On Thu, Apr 23, 2026 at 10:10 PM David Rowley <[email protected]> wrote:
> On Fri, 24 Apr 2026 at 01:04, Mok <[email protected]> wrote:
> >
> > On Thursday, April 23rd, 2026 at 4:44 AM, David Rowley <
> [email protected]> wrote:
> >
> > > On Thu, 23 Apr 2026 at 08:19, Mok <[email protected]> wrote:
> > > > For example, set to 0.8 a 'standard' vacuum would be triggered when
> the table reached 160million with a default 200million setting.
> > >
> > > If that's what you want, why wouldn't you set the
> > > autovacuum_freeze_max_age to 160million?
> >
> > Because that would trigger a 'to-prevent-wraparound' vacuum, which is
> what this change is trying to avoid.
>
> Yes, it would. Why do you want to prevent them? I believe a few people
> have been alarmed in the past about the "to prevent wraparound" text
> in pg_stat_activity or when they saw those words in the logs. The
> default 200 million autovacuum_freeze_max_age setting triggers an
> autovacuum when it's less than 10% of the way into exhausting the
> transaction space for the table. What you're proposing with an
> autovacuum_age_scale_factor of 0.1 sounds like it would result in an
> auto-vacuum when only 1% of the transaction ID space is consumed! I
> think you're under the false impression that these anti-wraparound
> vacuums are bad. They're not.
>
> There's some documentation that might be worthwhile reading in [1].
>
> David
>
> [1]
> https://www.postgresql.org/docs/18/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND
>
>
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: New vacuum config to avoid anti wraparound vacuums
2026-04-22 20:18 New vacuum config to avoid anti wraparound vacuums Mok <[email protected]>
2026-04-23 03:44 ` Re: New vacuum config to avoid anti wraparound vacuums David Rowley <[email protected]>
2026-04-23 13:04 ` Re: New vacuum config to avoid anti wraparound vacuums Mok <[email protected]>
2026-04-23 14:09 ` Re: New vacuum config to avoid anti wraparound vacuums David Rowley <[email protected]>
@ 2026-04-24 06:15 ` Mok <[email protected]>
2026-04-24 07:39 ` Re: New vacuum config to avoid anti wraparound vacuums wenhui qiu <[email protected]>
1 sibling, 1 reply; 9+ messages in thread
From: Mok @ 2026-04-24 06:15 UTC (permalink / raw)
To: David Rowley <[email protected]>; +Cc: [email protected] <[email protected]>
On Thursday, April 23rd, 2026 at 3:10 PM, David Rowley <[email protected]> wrote:
> On Fri, 24 Apr 2026 at 01:04, Mok <[email protected]> wrote:
> >
> > On Thursday, April 23rd, 2026 at 4:44 AM, David Rowley <[email protected]> wrote:
> >
> > > On Thu, 23 Apr 2026 at 08:19, Mok <[email protected]> wrote:
> > > > For example, set to 0.8 a 'standard' vacuum would be triggered when the table reached 160million with a default 200million setting.
> > >
> > > If that's what you want, why wouldn't you set the
> > > autovacuum_freeze_max_age to 160million?
> >
> > Because that would trigger a 'to-prevent-wraparound' vacuum, which is what this change is trying to avoid.
>
> Yes, it would. Why do you want to prevent them? I believe a few people
> have been alarmed in the past about the "to prevent wraparound" text
> in pg_stat_activity or when they saw those words in the logs. The
> default 200 million autovacuum_freeze_max_age setting triggers an
> autovacuum when it's less than 10% of the way into exhausting the
> transaction space for the table. What you're proposing with an
> autovacuum_age_scale_factor of 0.1 sounds like it would result in an
> auto-vacuum when only 1% of the transaction ID space is consumed! I
> think you're under the false impression that these anti-wraparound
> vacuums are bad. They're not.
>
> There's some documentation that might be worthwhile reading in [1].
>
> David
>
> [1] https://www.postgresql.org/docs/18/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND
>
On large tables they can be quite inconvenient so avoiding them is preferable. My example of 0.1 is to test the patch if you tried it. The range for this setting is 0.1 -> 1 with the latter effectively rendering the setting moot.
Gurmokh
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: New vacuum config to avoid anti wraparound vacuums
2026-04-22 20:18 New vacuum config to avoid anti wraparound vacuums Mok <[email protected]>
2026-04-23 03:44 ` Re: New vacuum config to avoid anti wraparound vacuums David Rowley <[email protected]>
2026-04-23 13:04 ` Re: New vacuum config to avoid anti wraparound vacuums Mok <[email protected]>
2026-04-23 14:09 ` Re: New vacuum config to avoid anti wraparound vacuums David Rowley <[email protected]>
2026-04-24 06:15 ` Re: New vacuum config to avoid anti wraparound vacuums Mok <[email protected]>
@ 2026-04-24 07:39 ` wenhui qiu <[email protected]>
2026-04-28 21:21 ` Re: New vacuum config to avoid anti wraparound vacuums Gurmokh <[email protected]>
0 siblings, 1 reply; 9+ messages in thread
From: wenhui qiu @ 2026-04-24 07:39 UTC (permalink / raw)
To: Mok <[email protected]>; +Cc: David Rowley <[email protected]>; [email protected] <[email protected]>
HI Mok
On Fri, Apr 24, 2026 at 2:16 PM Mok <[email protected]> wrote:
>
>
> On Thursday, April 23rd, 2026 at 3:10 PM, David Rowley <
> [email protected]> wrote:
>
> > On Fri, 24 Apr 2026 at 01:04, Mok <[email protected]> wrote:
> > >
> > > On Thursday, April 23rd, 2026 at 4:44 AM, David Rowley <
> [email protected]> wrote:
> > >
> > > > On Thu, 23 Apr 2026 at 08:19, Mok <[email protected]> wrote:
> > > > > For example, set to 0.8 a 'standard' vacuum would be triggered
> when the table reached 160million with a default 200million setting.
> > > >
> > > > If that's what you want, why wouldn't you set the
> > > > autovacuum_freeze_max_age to 160million?
> > >
> > > Because that would trigger a 'to-prevent-wraparound' vacuum, which is
> what this change is trying to avoid.
> >
> > Yes, it would. Why do you want to prevent them? I believe a few people
> > have been alarmed in the past about the "to prevent wraparound" text
> > in pg_stat_activity or when they saw those words in the logs. The
> > default 200 million autovacuum_freeze_max_age setting triggers an
> > autovacuum when it's less than 10% of the way into exhausting the
> > transaction space for the table. What you're proposing with an
> > autovacuum_age_scale_factor of 0.1 sounds like it would result in an
> > auto-vacuum when only 1% of the transaction ID space is consumed! I
> > think you're under the false impression that these anti-wraparound
> > vacuums are bad. They're not.
> >
> > There's some documentation that might be worthwhile reading in [1].
> >
> > David
> >
> > [1]
> https://www.postgresql.org/docs/18/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND
> >
>
> > On large tables they can be quite inconvenient so avoiding them is
> preferable. My example of 0.1 is to test the patch if you tried it. The
> range for this
> > setting is 0.1 -> 1 with the latter effectively rendering the setting
> moot.
> I don't know where you got that idea from. For example have a table with 1
> billion records, autovacuum_vacuum_scale_factor = 0.01 ,
> 50+1000000000 *0.01 = 10000050 ,you can reduce
> autovacuum_vacuum_max_threshold substantially lower than 10000050 ，
> vacthresh = (float4) vac_base_thresh + vac_scale_factor * reltuples;
> if (vac_max_thresh >= 0 && vacthresh > (float4) vac_max_thresh)
> vacthresh = (float4) vac_max_thresh;
>
> There's no fundamental difference between this and your parameter
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: New vacuum config to avoid anti wraparound vacuums
2026-04-22 20:18 New vacuum config to avoid anti wraparound vacuums Mok <[email protected]>
2026-04-23 03:44 ` Re: New vacuum config to avoid anti wraparound vacuums David Rowley <[email protected]>
2026-04-23 13:04 ` Re: New vacuum config to avoid anti wraparound vacuums Mok <[email protected]>
2026-04-23 14:09 ` Re: New vacuum config to avoid anti wraparound vacuums David Rowley <[email protected]>
2026-04-24 06:15 ` Re: New vacuum config to avoid anti wraparound vacuums Mok <[email protected]>
2026-04-24 07:39 ` Re: New vacuum config to avoid anti wraparound vacuums wenhui qiu <[email protected]>
@ 2026-04-28 21:21 ` Gurmokh <[email protected]>
2026-04-29 02:29 ` Re: New vacuum config to avoid anti wraparound vacuums Nathan Bossart <[email protected]>
0 siblings, 1 reply; 9+ messages in thread
From: Gurmokh @ 2026-04-28 21:21 UTC (permalink / raw)
To: wenhui qiu <[email protected]>; +Cc: David Rowley <[email protected]>; [email protected] <[email protected]>
On Friday, April 24th, 2026 at 8:39 AM, wenhui qiu <[email protected]> wrote:
> HI Mok
>
> On Fri, Apr 24, 2026 at 2:16 PM Mok <[email protected]> wrote:
>
>> On Thursday, April 23rd, 2026 at 3:10 PM, David Rowley <[email protected]> wrote:
>>
>>> On Fri, 24 Apr 2026 at 01:04, Mok <[email protected]> wrote:
>>> >
>>> > On Thursday, April 23rd, 2026 at 4:44 AM, David Rowley <[email protected]> wrote:
>>> >
>>> > > On Thu, 23 Apr 2026 at 08:19, Mok <[email protected]> wrote:
>>> > > > For example, set to 0.8 a 'standard' vacuum would be triggered when the table reached 160million with a default 200million setting.
>>> > >
>>> > > If that's what you want, why wouldn't you set the
>>> > > autovacuum_freeze_max_age to 160million?
>>> >
>>> > Because that would trigger a 'to-prevent-wraparound' vacuum, which is what this change is trying to avoid.
>>>
>>> Yes, it would. Why do you want to prevent them? I believe a few people
>>> have been alarmed in the past about the "to prevent wraparound" text
>>> in pg_stat_activity or when they saw those words in the logs. The
>>> default 200 million autovacuum_freeze_max_age setting triggers an
>>> autovacuum when it's less than 10% of the way into exhausting the
>>> transaction space for the table. What you're proposing with an
>>> autovacuum_age_scale_factor of 0.1 sounds like it would result in an
>>> auto-vacuum when only 1% of the transaction ID space is consumed! I
>>> think you're under the false impression that these anti-wraparound
>>> vacuums are bad. They're not.
>>>
>>> There's some documentation that might be worthwhile reading in [1].
>>>
>>> David
>>>
>>> [1] https://www.postgresql.org/docs/18/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND
>>>
>>
>>> On large tables they can be quite inconvenient so avoiding them is preferable. My example of 0.1 is to test the patch if you tried it. The range for this
>>> setting is 0.1 -> 1 with the latter effectively rendering the setting moot.
>> I don't know where you got that idea from. For example have a table with 1 billion records, autovacuum_vacuum_scale_factor = 0.01 ,
>> 50+1000000000 *0.01 = 10000050 ,you can reduce autovacuum_vacuum_max_threshold substantially lower than 10000050 ，
>> vacthresh= (float4) vac_base_thresh + vac_scale_factor * reltuples;
>> if (vac_max_thresh>=0&&vacthresh> (float4) vac_max_thresh)
>> vacthresh= (float4) vac_max_thresh;
>>
>> There's no fundamental difference between this and your parameter
I think I may not have explained my reasoning correctly, I'll try again. I am suggesting another configuration parameter that can be used to trigger autovacuums.
The config parameters in [1] autovacuum_vacuum_threshold, autovacuum_vacuum_insert_threshold, autovacuum_vacuum_scale_factor, autovacuum_vacuum_insert_scale_factor and autovacuum_vacuum_max_threshold rely on regular activity to trigger autovacuums. However it is entirely plausible that these can be configured with values that are not sensitive enough and a table breaches the autovacuum_freeze_max_age triggering an aggressive vacuum to prevent wraparound before any less aggressive vacuums can be triggered.
In my experience I have seen tables that have significant activity and still not meet the criteria to trigger an autovacuum and subsequently age out. I have seen production systems slow to a grind waiting for these to complete.
What I'm suggesting here is to have a configurable parameter that represents a value as a percentage of autovacuum_freeze_max_age that would enable a table to be autovacuumed before a vacuum to prevent wraparound is triggered if none of the above conditions are met.
For example. In my patch setting the autovacuum_age_scale_factor to 0.99 would mean a table is autovacuumed when 99% of the autovacuum_freeze_max_age age is reached. In the event that none of the above conditions are met there is a 'failsafe' condition that will trigger a standard vacuum over a wraparound one.
The value I mentioned in my original email was to effectively test the patch without having to wait for the tx count to reach the required number.
The allowed values in my patch range from 0.1 to 1. Both ends being extreme examples. 0.1 likely being too small and you should be relying scale factor and thresholds. 1 effectively setting the value to be the same as the actual autovacuum_freeze_max_age and likely clashing with the same conditions that would trigger a wraparound vacuum. This setting would represent a failsafe of sorts and a sensible value would be somewhere in the range close to 1.
[1] https://www.postgresql.org/docs/18/runtime-config-vacuum.html
Gurmokh
>>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: New vacuum config to avoid anti wraparound vacuums
2026-04-22 20:18 New vacuum config to avoid anti wraparound vacuums Mok <[email protected]>
2026-04-23 03:44 ` Re: New vacuum config to avoid anti wraparound vacuums David Rowley <[email protected]>
2026-04-23 13:04 ` Re: New vacuum config to avoid anti wraparound vacuums Mok <[email protected]>
2026-04-23 14:09 ` Re: New vacuum config to avoid anti wraparound vacuums David Rowley <[email protected]>
2026-04-24 06:15 ` Re: New vacuum config to avoid anti wraparound vacuums Mok <[email protected]>
2026-04-24 07:39 ` Re: New vacuum config to avoid anti wraparound vacuums wenhui qiu <[email protected]>
2026-04-28 21:21 ` Re: New vacuum config to avoid anti wraparound vacuums Gurmokh <[email protected]>
@ 2026-04-29 02:29 ` Nathan Bossart <[email protected]>
0 siblings, 0 replies; 9+ messages in thread
From: Nathan Bossart @ 2026-04-29 02:29 UTC (permalink / raw)
To: Gurmokh <[email protected]>; +Cc: wenhui qiu <[email protected]>; David Rowley <[email protected]>; [email protected] <[email protected]>
On Tue, Apr 28, 2026 at 09:21:35PM +0000, Gurmokh wrote:
> The config parameters in [1] autovacuum_vacuum_threshold,
> autovacuum_vacuum_insert_threshold, autovacuum_vacuum_scale_factor,
> autovacuum_vacuum_insert_scale_factor and autovacuum_vacuum_max_threshold
> rely on regular activity to trigger autovacuums. However it is entirely
> plausible that these can be configured with values that are not sensitive
> enough and a table breaches the autovacuum_freeze_max_age triggering an
> aggressive vacuum to prevent wraparound before any less aggressive
> vacuums can be triggered.
>
> In my experience I have seen tables that have significant activity and
> still not meet the criteria to trigger an autovacuum and subsequently age
> out. I have seen production systems slow to a grind waiting for these to
> complete.
>
> What I'm suggesting here is to have a configurable parameter that
> represents a value as a percentage of autovacuum_freeze_max_age that
> would enable a table to be autovacuumed before a vacuum to prevent
> wraparound is triggered if none of the above conditions are met.
So your new parameter is meant to trigger non-aggressive vacuums in hopes
that it might advance relfrozenxid and avoid an upcoming aggressive vacuum.
Do I have that right?
If so, I'm not sure that such a feature will make a tremendous amount of
difference. Non-aggressive vacuums can only advance relfrozenxid (thus
preventing an imminent aggressive vacuum) if they don't skip any pages and
are able to obtain cleanup locks for the relevant buffers, but page
skipping and conditional locking seem like key features that would make a
non-aggressive autovacuum less disruptive. I think there's a good chance
that even with your parameter, a preemptive non-aggressive vacuum would be
followed by an aggressive one shortly afterwards.
Perhaps there are other reasons prioritizing a non-aggressive autovacuum
would help, but it's hard to tell from the details you've shared thus far.
Would you mind elaborating on what you are seeing that is causing your
servers to "slow to a grind"?
--
nathan
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2026-04-29 02:29 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-04-22 20:18 New vacuum config to avoid anti wraparound vacuums Mok <[email protected]>
2026-04-23 03:44 ` David Rowley <[email protected]>
2026-04-23 13:04 ` Mok <[email protected]>
2026-04-23 14:09 ` David Rowley <[email protected]>
2026-04-24 03:57 ` wenhui qiu <[email protected]>
2026-04-24 06:15 ` Mok <[email protected]>
2026-04-24 07:39 ` wenhui qiu <[email protected]>
2026-04-28 21:21 ` Gurmokh <[email protected]>
2026-04-29 02:29 ` Nathan Bossart <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox