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
