MIME-Version: 1.0
References: 
 <CeFiI3W4yXPznxyELYARQ5H6Cfy1HJIm0Tsn5CDVINyORFVwKfIMwc5EKHYlVzAjy5vgHgfA7X7lgfA3CnD0GchBvuanglpr4xd-IoyjPWY=@protonmail.com>
In-Reply-To: 
 <CeFiI3W4yXPznxyELYARQ5H6Cfy1HJIm0Tsn5CDVINyORFVwKfIMwc5EKHYlVzAjy5vgHgfA7X7lgfA3CnD0GchBvuanglpr4xd-IoyjPWY=@protonmail.com>
From: David Rowley <dgrowleyml@gmail.com>
Date: Thu, 23 Apr 2026 15:44:42 +1200
Message-ID: 
 <CAApHDvonBh3YPGzFjg79-9RPRcnWVrzZyLR1xr05WxdY=EqjSg@mail.gmail.com>
Subject: Re: New vacuum config to avoid anti wraparound vacuums
To: Mok <gurmokh@protonmail.com>
Cc: "pgsql-hackers@lists.postgresql.org" <pgsql-hackers@lists.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Archived-At: 
 <https://www.postgresql.org/message-id/CAApHDvonBh3YPGzFjg79-9RPRcnWVrzZyLR1xr05WxdY%3DEqjSg%40mail.gmail.com>
Precedence: bulk

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