Date: Thu, 23 Apr 2026 13:04:35 +0000
To: David Rowley <dgrowleyml@gmail.com>
From: Mok <gurmokh@protonmail.com>
Cc: "pgsql-hackers@lists.postgresql.org" <pgsql-hackers@lists.postgresql.org>
Subject: Re: New vacuum config to avoid anti wraparound vacuums
Message-ID: 
 <AkM6NiLDE4pBr-PL1M79iydoh3enMO-UruUI2Xd3n3xOysAjGbm-lYz34DrLZcKbtRehBYM8lHRY6zVYZZkqL8vcM55LEt8i0lKVGwWUX54=@protonmail.com>
In-Reply-To: 
 <CAApHDvonBh3YPGzFjg79-9RPRcnWVrzZyLR1xr05WxdY=EqjSg@mail.gmail.com>
References: 
 <CeFiI3W4yXPznxyELYARQ5H6Cfy1HJIm0Tsn5CDVINyORFVwKfIMwc5EKHYlVzAjy5vgHgfA7X7lgfA3CnD0GchBvuanglpr4xd-IoyjPWY=@protonmail.com>
 <CAApHDvonBh3YPGzFjg79-9RPRcnWVrzZyLR1xr05WxdY=EqjSg@mail.gmail.com>
Feedback-ID: 4918997:user:proton
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/AkM6NiLDE4pBr-PL1M79iydoh3enMO-UruUI2Xd3n3xOysAjGbm-lYz34DrLZcKbtRehBYM8lHRY6zVYZZkqL8vcM55LEt8i0lKVGwWUX54%3D%40protonmail.com>
Precedence: bulk


On Thursday, April 23rd, 2026 at 4:44 AM, David Rowley <dgrowleyml@gmail.co=
m> 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 200million setting.
>=20
> 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.=20


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

>=20
> > 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.
>=20
> > The log entry for the event would appear like:
> >
> > [56957] LOG:  automatic vacuum (age-based proactive) of table "postgres=
.public.atable": index scans: 0
>=20
> 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.=20
Existing settings work from the bottom up. Eg. N number of modifications + =
threshold is what triggers an autovacuum. These work great in terms of garb=
age collection, space reusing and reclaiming. However there is no guarantee=
 that these conditions will be met before the table reaches autovacuum_free=
ze_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 cont=
ention that occurs when one runs.  =20

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 possibi=
lity of that table reaching 200million.


>=20
> 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:
>=20
> select 'vacuum ' || relname from pg_stat_autovacuum_scores where
> schemaname <> 'pg_toast' and xid_score > 0.8 or mxid_score > 0.8;
> \gexec
>=20
> 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 tr=
ying achieve. It would be an indicator of the autovacuum daemons next targe=
ts. Using it to determine triggering a vacuum would require a job of some s=
ort. This config change would have the database handle this for you.=20


>=20
> David
>=20


Gurmokh