Subject: New vacuum config to avoid anti wraparound vacuums

From: Mok <[email-masked]>
Date: Wed, 22 Apr 2026 20:18:55 +0000

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

---

From: David Rowley <[email-masked]>
Date: Thu, 23 Apr 2026 15:44:42 +1200

> 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?

> [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

---

From: Mok <[email-masked]>
Date: Thu, 23 Apr 2026 13:04:35 +0000

> autovacuum_freeze_max_age to 160million?

Because that would trigger a 'to-prevent-wraparound' vacuum, which is what this change is trying to avoid. 

> 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.

> 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. 

Gurmokh

---

From: David Rowley <[email-masked]>
Date: Fri, 24 Apr 2026 02:09:57 +1200

> 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

---

From: wenhui qiu <[email-masked]>
Date: Fri, 24 Apr 2026 11:57:40 +0800

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-masked]> wrote:

> > > > For example, set to 0.8 a 'standard' vacuum would be triggered when
> the table reached 160million with a default 200million setting.
> > >
> [1]
> https://www.postgresql.org/docs/18/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND
>
>
>

---

From: Mok <[email-masked]>
Date: Fri, 24 Apr 2026 06:15:48 +0000

> [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

---

From: wenhui qiu <[email-masked]>
Date: Fri, 24 Apr 2026 15:39:00 +0800

HI Mok

On Fri, Apr 24, 2026 at 2:16 PM Mok <[email-masked]> wrote:

> > > > > For example, set to 0.8 a 'standard' vacuum would be triggered
> when the table reached 160million with a default 200million setting.
> > > >
> > 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
>

---

From: Gurmokh <[email-masked]>
Date: Tue, 28 Apr 2026 21:21:35 +0000

On Friday, April 24th, 2026 at 8:39 AM, wenhui qiu <[email-masked]> wrote:

>>> 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;
>>

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

---

From: Nathan Bossart <[email-masked]>
Date: Tue, 28 Apr 2026 21:29:12 -0500

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