Date: Fri, 10 Oct 2025 12:31:13 -0500
From: Nathan Bossart <nathandbossart@gmail.com>
To: David Rowley <dgrowleyml@gmail.com>
Cc: Jeremy Schneider <schneider@ardentperf.com>,
	Sami Imseih <samimseih@gmail.com>, pgsql-hackers@postgresql.org
Subject: Re: another autovacuum scheduling thread
Message-ID: <aOlC4aDoQcgW8ZpC@nathan>
References: <aOaAuXREwnPZVISO@nathan>
 <CAA5RZ0ucgx6VgVCUYryZY33StMtHSpoND_7wEFjGCVCSjouUsA@mail.gmail.com>
 <20251008164057.6bceb9ed@ardentperf.com>
 <CAApHDvo3maZsrZVq=Bur=Z6Gtse4asSEgHU0HzBhhcTfM-AfeA@mail.gmail.com>
 <20251008172727.3befd129@ardentperf.com>
 <CAApHDvpMEs3rewULyJZ=huT8ZM1C1PboV-G7K7jfGtJSgdx-fA@mail.gmail.com>
 <20251008182520.6e05a8b8@ardentperf.com>
 <20251008184740.328d45de@ardentperf.com>
 <CAApHDvq76BBseUh2cG0=m=8r-j6HF_jrQt16Eszgsxp3bciGQw@mail.gmail.com>
 <aOffPCBoQLG5dGd8@nathan>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="DM8fxteXoufrDV0I"
Content-Disposition: inline
In-Reply-To: <aOffPCBoQLG5dGd8@nathan>
Archived-At: <https://www.postgresql.org/message-id/aOlC4aDoQcgW8ZpC%40nathan>
Precedence: bulk


--DM8fxteXoufrDV0I
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

On Thu, Oct 09, 2025 at 11:13:48AM -0500, Nathan Bossart wrote:
> On Thu, Oct 09, 2025 at 04:13:23PM +1300, David Rowley wrote:
>> I think the best way to understand it is if you look at
>> relation_needs_vacanalyze() and see how it calculates boolean values
>> for boolean output params. So, instead of calculating just a boolean
>> value it instead calculates a float4 where < 1.0 means don't do the
>> operation and anything >= 1.0 means do the operation. For example,
>> let's say a table has 600 dead rows and the scale factor and threshold
>> settings mean that autovacuum will trigger at 200 (3 times more dead
>> tuples than the trigger point). That would result in the value of 3.0
>> (600 / 200).  The priority for relfrozenxid portion is basically
>> age(relfrozenxid) / autovacuum_freeze_max_age (plus need to account
>> for mxid by doing the same for that and taking the maximum of each
>> value).  For each of those component "scores", the priority for
>> autovacuum would be the maximum of each of those.
>> 
>> Effectively, it's a method of aligning the different units of measure,
>> transactions or tuples into a single value which is calculated based
>> on the very same values that we use today to trigger autovacuums.
> 
> I like the idea of a "score" approach, but I'm worried that we'll never
> come to an agreement on the formula to use.  Perhaps we'd have more luck
> getting consensus on a multifaceted strategy if we kept it brutally simple.
> IMHO it's worth a try...

Here's a prototype of a "score" approach.  Two notes:

* I've given special priority to anti-wraparound vacuums.  I think this is
important to avoid focusing too much on bloat when wraparound is imminent.
In any case, we need a separate wraparound score in case autovacuum is
disabled.

* I didn't include the analyze threshold in the score because it doesn't
apply to TOAST tables, and therefore would artificially lower their
prioritiy.  Perhaps there is another way to deal with this.

This is very much just a prototype of the basic idea.  As-is, I think it'll
favor processing tables with lots of bloat unless we're in an
anti-wraparound scenario.  Maybe that's okay.  I'm not sure how scientific
we want to be about all of this, but I do intend to try some long-running
tests.

-- 
nathan

--DM8fxteXoufrDV0I
Content-Type: text/plain; charset=us-ascii
Content-Disposition: attachment;
	filename=v2-0001-autovacuum-scheduling-improvements.patch