Обсуждение: New vacuum config to avoid anti wraparound vacuums

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
forthem, especially when trying to avoid anti wraparound vacuums. Some tables are massive and needs scale factor
settingsin the fractions of percent, sometimes, with no guarantee that enough actions cross that threshold. Increasing
theautovacuum_freeze_max_age to keep up with an growing number of transactions across an instance is just kicking the
candown the road. Engineers run backfills that throw off any calculations you may use to work out these scale factors
andusing the same values across an instance is too simple while managing individually for tables can be too messy. Or
yourun 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
200millionsetting.  

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

Happy Postgresing

Gurmokh

On Thu, 23 Apr 2026 at 08:19, Mok <gurmokh@protonmail.com> wrote:
> For example, set to 0.8 a 'standard' vacuum would be triggered when the table reached 160million with a default
200millionsetting.
 

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

On Thursday, April 23rd, 2026 at 4:44 AM, David Rowley <dgrowleyml@gmail.com> wrote:

> On Thu, 23 Apr 2026 at 08:19, Mok <gurmokh@protonmail.com> wrote:
> > For example, set to 0.8 a 'standard' vacuum would be triggered when the table reached 160million with a default
200millionsetting. 
>
> 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
orthreshold 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.
Thesework great in terms of garbage collection, space reusing and reclaiming. However there is no guarantee that these
conditionswill 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
thusreducing 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
wraparoundwould have occurred. Which then reduces the age of the table. Effectively greatly reducing the possibility of
thattable 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
theautovacuum daemons next targets. Using it to determine triggering a vacuum would require a job of some sort. This
configchange would have the database handle this for you.  


>
> David
>


Gurmokh

On Fri, 24 Apr 2026 at 01:04, Mok <gurmokh@protonmail.com> wrote:
>
> On Thursday, April 23rd, 2026 at 4:44 AM, David Rowley <dgrowleyml@gmail.com> wrote:
>
> > On Thu, 23 Apr 2026 at 08:19, Mok <gurmokh@protonmail.com> wrote:
> > > For example, set to 0.8 a 'standard' vacuum would be triggered when the table reached 160million with a default
200millionsetting.
 
> >
> > 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 Thursday, April 23rd, 2026 at 3:10 PM, David Rowley <dgrowleyml@gmail.com> wrote:

> On Fri, 24 Apr 2026 at 01:04, Mok <gurmokh@protonmail.com> wrote:
> >
> > On Thursday, April 23rd, 2026 at 4:44 AM, David Rowley <dgrowleyml@gmail.com> wrote:
> >
> > > On Thu, 23 Apr 2026 at 08:19, Mok <gurmokh@protonmail.com> wrote:
> > > > For example, set to 0.8 a 'standard' vacuum would be triggered when the table reached 160million with a default
200millionsetting. 
> > >
> > > 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
ifyou tried it. The range for this setting is 0.1 -> 1 with the latter effectively rendering the setting moot. 

Gurmokh

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