Re: another autovacuum scheduling thread

On Thu, Oct 23, 2025 at 08:34:49AM +1300, David Rowley wrote:
> On Thu, 23 Oct 2025 at 07:58, Nathan Bossart <nathandbossart(at)gmail(dot)com> wrote:
>> 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
>
> This does start to put the score > 1 before the table reaches
> autovacuum_freeze_max_age. I don't think that's great as the score of
> 1.0 was meant to represent that the table now requires some autovacuum
> work.

My thinking was that this formula would only be used once the table reaches
autovacuum_freeze_max_age. If the age is less than that, we'd do something
else, such as dividing the age by the *_max_age setting.

> The main reason I was trying to keep the score scaling with the
> percentage over the given threshold that the table is was that I had
> imagined we could use the score number to start reducing the sleep
> time between autovacuum_vacuum_cost_limit when the highest scoring
> table persists in being high for too long. I was considering this to
> fix the misconfigured autovacuum problem that so many people have. If
> we scaled it the way similar to the query above, the score would look
> high even before it reaches the limit. This is the reason I was
> scaling the score linear with the autovacuum_freeze_max_age with the
> version I sent and only scaling exponentially after the failsafe age.
> I wanted to talk about the "reducing the cost delay" feature
> separately so as not to load up this thread and widen the scope for
> varying opinions, but in its most trivial form, the
> vacuum_cost_limit() code could be adjusted to only sleep for
> autovacuum_vacuum_cost_delay / <the table's score>.

I see.

> I think the one I proposed in [1] does this quite well. The table
> remains eligible to be autovacuumed with any score >= 1.0, and there's
> still a huge window of time to freeze a table once it's over
> autovacuum_freeze_max_age before there are issues and the exponential
> scaling once over failsafe age should ensure that the table is top of
> the list for when the failsafe code kicks in and removes the cost
> limit.

Yeah. I'll update the patch with that formula.

--
nathan