Re: another autovacuum scheduling thread

> On Fri, 7 Nov 2025 at 11:21, Sami Imseih <samimseih(at)gmail(dot)com> wrote:
> > Also, I am thinking about another sorting strategy based on average
> > autovacuum/autoanalyze time per table. The idea is to sort ascending by
> > the greater of the two averages, so workers process quicker tables first
> > instead of all workers potentially getting hung on the slowest tables.
> > We can calculate the average now that v18 includes total_autovacuum_time
> > and total_autoanalyze time.
> >
> > The way I see it, regardless of prioritization, a few large tables may
> > still monopolize autovacuum workers. But at least this way, the quick tables
> > get a chance to get processed first. Will this be an idea worth testing out?
>
> This sounds like a terrible idea to me. It'll mean any table that
> starts taking longer due to autovacuum neglect will have its priority
> dropped for next time which will result in further neglect.

yes, that is a possibility, but I am not sure how we can actually
avoid these scenarios. The flip side is we are giving a chance
for the eligible fast tables to get more of a chance to get vacuumed,
rather than be backed because workers are all occupied on the
larger tables.

> vacuum_cost_limit is too low, then the tables in need of vacuum the
> most could end up last in the queue. I also don't see how you'd handle
> the fact that analyze is likely to be faster than vacuum. Tables that
> only need an analyze would just come last with no regard for how
> outdated the statistics are?

In the "doanalyze" case only, we will look at the average autoanalyze count,
which will push these types of tables to the front of the queue, not the last.

> I'm confused at why we'd have set up our autovacuum trigger points as
> they are today because we think those are good times to do a
> vacuum/analyze, but then prioritise on something completely different.
> Surely if we think 20% dead tuples is worth a vacuum, we must
> therefore think that 40% dead tuples are even more worthwhile?!

Sure, but thresholds alone don't indicate anything about the how quick
the table can be vacuumed, # of indexes, per table a/v settings, etc.
The average a/v time is a good proxy to determine this.

What I am suggesting here is we think beyond thresholds for
prioritization, and to give a chance for more eligible tables to get
autovacuumed rather than workers being saturated on some
of the slowest-to-vacuum tables.

--
Sami Imseih
Amazon Web Services (AWS)