Date: Mon, 23 Mar 2026 10:22:07 -0500
From: Nathan Bossart <nathandbossart@gmail.com>
To: Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>
Cc: Sami Imseih <samimseih@gmail.com>, David Rowley <dgrowleyml@gmail.com>,
	Greg Burd <greg@burd.me>, Robert Haas <robertmhaas@gmail.com>,
	Robert Treat <rob@xzilla.net>,
	Jeremy Schneider <schneider@ardentperf.com>,
	pgsql-hackers <pgsql-hackers@postgresql.org>
Subject: Re: another autovacuum scheduling thread
Message-ID: <acFan6knPZljm1Ay@nathan>
References: <abrOO8GCgkhMXvcq@nathan>
 <bff7dc5a-f839-449b-b56a-aab36cd3e39d@app.fastmail.com>
 <CAApHDvpnR5zKb7LSB0idj4RPsV+=Dhtk+c5H0cZ4evN2hFDwCQ@mail.gmail.com>
 <3ca1e398-c787-47e9-9afc-8e298b94dac0@app.fastmail.com>
 <abwbDTu6OO6Izp4o@nathan>
 <CAA5RZ0s7k6yve1Bq1OY+aXU3ifc_fvOiYkpE2VxwrUcHr2AtTg@mail.gmail.com>
 <CAApHDvpvkKYB6z-i1kiO9iePF0fz18-CaS9DZEWnSx1eO0RGFQ@mail.gmail.com>
 <CAA5RZ0tPvWLVXhDMC3t3LhwTM-OnWHSvPo7tnZzOphR=HB4kgQ@mail.gmail.com>
 <ab2uoj5yU8CVUJ_H@nathan>
 <CALj2ACV0HboeYiT1Z-=rb509WL3N0H9LsWRPqc_xUMEM-TM85g@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <CALj2ACV0HboeYiT1Z-=rb509WL3N0H9LsWRPqc_xUMEM-TM85g@mail.gmail.com>
Archived-At: <https://www.postgresql.org/message-id/acFan6knPZljm1Ay%40nathan>
Precedence: bulk

On Sat, Mar 21, 2026 at 05:08:56PM -0700, Bharath Rupireddy wrote:
> Hi Nathan, Thank you for working on this feature. I'm late to this
> thread. I read the patch, and here are some general thoughts. I
> haven't read the full thread, so some of these thoughts may be
> repetitive - thanks for bearing with me.

Thanks for looking.

> 1/ The Autovacuum Prioritization section in the docs is a good start
> for explaining the usage aspects of the new scoring system. However,
> IMHO, adding a couple of production-like scenarios showing how these
> scores need to be adjusted and used would be a good addition to this
> section.

IMO these scores generally shouldn't need adjusting outside of cases where
the DBA wants to revert to the previous scheduling strategy or there's some
other unique scenario where the prioritization isn't working for them.  I
think it'd be hard to give any general guidance, at least at this point
since we have no field experience yet.

> 2/ Any plans to extend the new scoring system to the table level
> (i.e., reloptions)? I think it would help in situations where there
> are huge tables that need to be prioritized for vacuum over others, so
> setting the scoring high for those tables would allow the next
> autovacuum to pick them up first.

I thought about that, but decided to leave those out because 1) the scoring
parameters are meant to be global and affect workers' prioritization of all
tables and 2) there are already a number of parameters that can be adjusted
to affect the score.  If users find that they really need reloptions for
some reason, we can always add them later.  Removing them seems more
difficult.

> 3/ Any plans to add tests to demo how each of these parameters could
> help in various situations - when the system needs freezing to be
> prioritized for avoiding XID wraparound over cleaning up dead tuples,
> and when it needs analyze to be prioritized to get correct plans,
> etc.? If needed, we could add elog(DEBUGX) messages to emit the reason
> or effectiveness of these new scores so that we can verify them in
> tests.

I hadn't planned on doing any additional tests/demonstrations here.  And
I'd really like to avoid burying all this information in DEBUG log
messages.  IMO we ought to eventually be exposing this stuff in system
views and the like, as has been discussed elsewhere.

> 4/ Is adding a reason (such as how each of these scores influenced the
> autovacuum to pick this table) to vacuum progress reporting a good
> idea? This helps answer some of the why and how questions when the
> autovacuum is in progress.

Yeah, adding that in addition to a system view, etc. could be nice.  I'm a
little hesitant to start making big additions to the patch at this point,
but I can give it a whirl if folks think something like this should be
added for v19.

-- 
nathan