Re: New vacuum config to avoid anti wraparound vacuums

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

> On Thu, 23 Apr 2026 at 08:19, Mok <gurmokh(at)protonmail(dot)com> 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