Horton

Re: another autovacuum scheduling thread

Nathan Bossart <nathandbossart@gmail.com>

View thread
From: Nathan Bossart <nathandbossart@gmail.com>
To: David Rowley <dgrowleyml@gmail.com>
Cc: Robert Haas <robertmhaas@gmail.com>, Jeremy Schneider <schneider@ardentperf.com>, Sami Imseih <samimseih@gmail.com>, pgsql-hackers@postgresql.org
Date: 2025-10-22T18:58:17Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Add rudimentary table prioritization to autovacuum.

  2. Trigger more frequent autovacuums with relallfrozen

  3. Harden nbtree page deletion.

  4. Check for interrupts inside the nbtree page deletion code.

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