MIME-Version: 1.0
References: <aOaAuXREwnPZVISO@nathan>
 <CAA5RZ0ucgx6VgVCUYryZY33StMtHSpoND_7wEFjGCVCSjouUsA@mail.gmail.com>
 <20251008164057.6bceb9ed@ardentperf.com>
In-Reply-To: <20251008164057.6bceb9ed@ardentperf.com>
From: David Rowley <dgrowleyml@gmail.com>
Date: Thu, 9 Oct 2025 12:59:23 +1300
Message-ID: 
 <CAApHDvo3maZsrZVq=Bur=Z6Gtse4asSEgHU0HzBhhcTfM-AfeA@mail.gmail.com>
Subject: Re: another autovacuum scheduling thread
To: Jeremy Schneider <schneider@ardentperf.com>
Cc: Sami Imseih <samimseih@gmail.com>,
 Nathan Bossart <nathandbossart@gmail.com>,
	pgsql-hackers@postgresql.org
Content-Type: text/plain; charset="UTF-8"
Archived-At: 
 <https://www.postgresql.org/message-id/CAApHDvo3maZsrZVq%3DBur%3DZ6Gtse4asSEgHU0HzBhhcTfM-AfeA%40mail.gmail.com>
Precedence: bulk

On Thu, 9 Oct 2025 at 12:41, Jeremy Schneider <schneider@ardentperf.com> wrote:
> I think an approach of doing largest objects first actually might work
> really well for balancing work amongst autovacuum workers. Many years
> ago I designed a system to backup many databases with a pool of workers
> and used this same simple & naive algorithm of just reverse sorting on
> db size, and it worked remarkably well. If you have one big thing then
> you probably want someone to get started on that first. As long as
> there's a pool of workers available, as you work through the queue, you
> can actually end up with pretty optimal use of all the workers.

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.

David