Date: Wed, 22 Oct 2025 14:43:50 -0500
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
Subject: Re: another autovacuum scheduling thread
Message-ID: <aPkz9grlBAJRXrbe@nathan>
References: 
 <CAApHDvq76BBseUh2cG0=m=8r-j6HF_jrQt16Eszgsxp3bciGQw@mail.gmail.com>
 <aOffPCBoQLG5dGd8@nathan>
 <aOlC4aDoQcgW8ZpC@nathan>
 <CA+TgmoYC4ShRp8vcyrBjkefSBdFfY1fnUgCvjocN-iq55G-7bA@mail.gmail.com>
 <CAApHDvqrd=SHVUytdRj55OWnLH98Rvtzqam5zq2f4XKRZa7t9Q@mail.gmail.com>
 <aPea8dG8_lFwTsyo@nathan>
 <CAApHDvocam8_cxqO=LiSifgp0B3rs1=VWRVQiwAwz_DvOMgfVw@mail.gmail.com>
 <aPklC5V61VcTu7IP@nathan>
 <aPkpSXHB66islP3h@nathan>
 <CAApHDvqobtKMwJbhKB_c=3-TM=TgS3bcuvzcWMm3ee1c0mz9hw@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <CAApHDvqobtKMwJbhKB_c=3-TM=TgS3bcuvzcWMm3ee1c0mz9hw@mail.gmail.com>
Archived-At: <https://www.postgresql.org/message-id/aPkz9grlBAJRXrbe%40nathan>
Precedence: bulk

On Thu, Oct 23, 2025 at 08:34:49AM +1300, David Rowley wrote:
> On Thu, 23 Oct 2025 at 07:58, Nathan Bossart <nathandbossart@gmail.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