Re: another autovacuum scheduling thread

On Wed, Oct 22, 2025 at 01:40:11PM -0500, Nathan Bossart wrote:
> On Wed, Oct 22, 2025 at 09:07:33AM +1300, David Rowley wrote:
>> However, just thinking of non-standard setting... I do wonder if it'll
>> be aggressive enough if someone did something like raise the
>> *freeze_max_age to 1 billion (it's certainly common that people raise
>> this). With a 1.6 billion vacuum_failsafe_age, a table at
>> freeze_max_age only scores in at 110. I guess there's no reason we
>> couldn't keep your calc and then scale the score further once over
>> vacuum_failsafe_age to ensure those are the highest priority. There is
>> a danger that if a table scores too low when age(relfrozenxid) >
>> vacuum_failsafe_age that autovacuum dawdles along handling bloated
>> tables while oblivious to the nearing armageddon.
>
> That's a good point. I wonder if we should try to make the wraparound
> score independent of the *_freeze_max_age parameters (once the table age
> surpasses said parameters). Else, different settings will greatly impact
> how aggressively tables are prioritized the closer they are to wraparound.
> Even if autovacuum_freeze_max_age is set to 200M, it's not critically
> important for autovacuum to pick up tables right away as soon as their age
> reaches 200M. But if the parameter is set to 2B, we _do_ want autovacuum
> to prioritize tables right away once their age reaches 2B.

I'm imagining something a bit like the following:

select xidage "age(relfrozenxid)",
power(1.001, xidage::float8 / (select min_val
from pg_settings where name = 'autovacuum_freeze_max_age')::float8)
xid_age_score from generate_series(0,2_000_000_000,100_000_000) xidage;

age(relfrozenxid) | xid_age_score
-------------------+--------------------
0 | 1
100000000 | 2.7169239322355936
200000000 | 7.38167565355452
300000000 | 20.055451243143093
400000000 | 54.48913545427955
500000000 | 148.0428361625591
600000000 | 402.22112456608977
700000000 | 1092.804199384323
800000000 | 2969.065882554825
900000000 | 8066.726152697397
1000000000 | 21916.681339054314
1100000000 | 59545.956045257895
1200000000 | 161781.8330472099
1300000000 | 439548.9340069078
1400000000 | 1194221.0181920114
1500000000 | 3244607.664704634
1600000000 | 8815352.21495106
1700000000 | 23950641.403886583
1800000000 | 65072070.82261215
1900000000 | 176795866.53808445
2000000000 | 480340920.9176516
(21 rows)

--
nathan