Thanks for raising this topic! I agree that autovacuum scheduling
could be improved.
> * 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. 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.
One risk I see with this approach is that we will end up autovacuuming
tables that also take the longest time to complete, which could cause
smaller, quick-to-process tables to be neglected.
It’s not always the case that the oldest tables in terms of (M)XID age
are also the most expensive to vacuum, but that is often more true
than not.
Not saying that the current approach, which is as you mention is
random, is any better, however this approach will likely increase
the behavior of large tables saturating workers.
But I also do see the merit of this approach when we know we are
in failsafe territory, because I would want my oldest aged tables to be
a/v'd first.
--
Sami Imseih
Amazon Web Services (AWS)
