New vacuum config to avoid anti wraparound vacuums

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

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

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

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]https://www.postgresql.org/docs/18/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND.

David

[1]: https://www.postgresql.org/docs/18/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND

Hi Mok

I have an idea for managing vacuums. When managing vacuums it can

sometimes be a struggle to manage the config settings for them, especially
when trying to avoid anti

wraparound vacuums. Some tables are massive and needs scale factor

settings in the fractions of percent, sometimes, with no guarantee that
enough actions cross that

threshold. Increasing the autovacuum_freeze_max_age to keep up with an

growing number of transactions across an instance is just kicking the can
down the road. Engineers run

backfills that throw off any calculations you may use to work out these

scale factors and using the same values across an instance is too simple
while managing individually

for tables can be too messy. Or you run a cron type job so you don't have

to think about it at all.
I think you can refer to these two parameters
https://www.postgresql.org/docs/18/runtime-config-vacuum.html#GUC-AUTOVACUUM-VACUUM-MAX-THRESHOLD
https://www.postgresql.org/docs/18/runtime-config-vacuum.html#
vacuum_freeze_table_age
If you're concerned about wraparound, you can reduce the value of
`VACUUM-VACUUM-MAX-THRESHOLD`,vacuum_freeze_table_age, Shouldn't you be
focusing on why the blocked table can't be vacuum-freeze?

Thanks

On Thu, Apr 23, 2026 at 10:10 PM David Rowley <dgrowleyml@gmail.com> wrote:

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

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 if you tried it. The range for this setting is 0.1 -> 1 with the latter effectively rendering the setting moot.

Gurmokh

HI Mok

On Fri, Apr 24, 2026 at 2:16 PM Mok <gurmokh@protonmail.com> wrote: