Re: New vacuum config to avoid anti wraparound vacuums - Mailing list pgsql-hackers

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