Re: another autovacuum scheduling thread - Mailing list pgsql-hackers

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