Feedback-ID: id4a34324:Fastmail
Date: Wed, 8 Oct 2025 13:37:22 -0400
From: Andres Freund <andres@anarazel.de>
To: Nathan Bossart <nathandbossart@gmail.com>
Cc: pgsql-hackers@postgresql.org
Subject: Re: another autovacuum scheduling thread
Message-ID: <l7k5nkow2n4x2lodcjimxl4wqv7rdjduo3zuzjwlx3kjxty5q2@gzl4pqbm6ows>
References: <aOaAuXREwnPZVISO@nathan>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <aOaAuXREwnPZVISO@nathan>
Archived-At: 
 <https://www.postgresql.org/message-id/l7k5nkow2n4x2lodcjimxl4wqv7rdjduo3zuzjwlx3kjxty5q2%40gzl4pqbm6ows>
Precedence: bulk

Hi,

On 2025-10-08 10:18:17 -0500, Nathan Bossart wrote:
> However, we do no such prioritization of the tables within a database.  In
> fact, the ordering of the tables is effectively random.

We don't prioritize tables, but I don't think the order really is random?
Isn't it basically in the order in which the data is in pg_class? That
typically won't change from one autovacuum pass to the next...


> * Prioritizing tables based on their (M)XID age might help avoid more
> aggressive vacuums, not to mention wraparound.  Of course, there are
> scenarios where this doesn't work.  For example, the age of a table may
> have changed greatly between the time we recorded it and the time we
> process it.

> Or maybe there is another table in a different database that
> is more important from a wraparound perspective.

That seems like something no ordering within a single AV worker can address. I
think it's fine to just define that to be out of scope.


> We could complicate the patch to try to handle some of these things, but I
> maintain that even some basic, incremental scheduling improvements would be
> better than the status quo.  And we can always change it further in the
> future to handle these problems and to consider other things like bloat.

Agreed!  It doesn't take much to be better at scheduling than "order in
pg_class".


> The attached patch works by storing the maximum of the XID age and the MXID
> age in the list with the OIDs and sorting it prior to processing.

I think it may be worth trying to avoid reliably using the same order -
otherwise e.g. a corrupt index on the first scheduled table can cause
autovacuum to reliably fail on the same relation, never allowing it to
progress past that point.

Greetings,

Andres Freund