Re: another autovacuum scheduling thread

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