Date: Wed, 8 Oct 2025 17:30:30 -0700
From: Jeremy Schneider <schneider@ardentperf.com>
To: David Rowley <dgrowleyml@gmail.com>
Cc: Sami Imseih <samimseih@gmail.com>, Nathan Bossart
 <nathandbossart@gmail.com>, pgsql-hackers@postgresql.org
Subject: Re: another autovacuum scheduling thread
Message-ID: <20251008173030.2a9322db@ardentperf.com>
In-Reply-To: <20251008172727.3befd129@ardentperf.com>
References: <aOaAuXREwnPZVISO@nathan>
	<CAA5RZ0ucgx6VgVCUYryZY33StMtHSpoND_7wEFjGCVCSjouUsA@mail.gmail.com>
	<20251008164057.6bceb9ed@ardentperf.com>
	<CAApHDvo3maZsrZVq=Bur=Z6Gtse4asSEgHU0HzBhhcTfM-AfeA@mail.gmail.com>
	<20251008172727.3befd129@ardentperf.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/20251008173030.2a9322db%40ardentperf.com>
Precedence: bulk

On Wed, 8 Oct 2025 17:27:27 -0700
Jeremy Schneider <schneider@ardentperf.com> wrote:

> On Thu, 9 Oct 2025 12:59:23 +1300
> David Rowley <dgrowleyml@gmail.com> wrote:
> 
> > I believe that is methodology for processing work applies much
> > better in scenarios where there's no new work continually arriving
> > and there's no adverse effects from giving a lower priority to
> > certain portions of the work. I don't think you can apply that so
> > easily to autovacuum as there are scenarios where the work can pile
> > up faster than it can be handled.  Also, smaller tables can bloat
> > in terms of growth proportional to the original table size much
> > more quickly than larger tables and that could have huge
> > consequences for queries to small tables which are not indexed
> > sufficiently to handle being becoming bloated and large.
> 
> I'm arguing that it works well with autovacuum. Not saying there
> aren't going to be certain workloads that it's suboptimal for. We're
> talking about sorting by (M)XID age. As the clock continues to move
> forward any table that doesn't get processed naturally moves up the
> queue for the next autovac run. I think the concerns are minimal here
> and this would be a good change in general.

Hmm, doesn't work quite like that if the full queue needs to be
processed before the next iteration ~ but at steady state these small
tables are going to get processed at the same rate whether they were
top of bottom of the queue right?

And in non-steady-state conditions, this seems like a better order than
pg_class ordering?

-Jeremy