MIME-Version: 1.0
References: 
 <CAApHDvoM5MEHHBc0TNdrzkpq39WdEHSZhdWrtnx9zOWNXTSFGw@mail.gmail.com>
 <aP-YgrcPi0EhgR9x@nathan>
 <CAApHDvpOq09uVq7aXcuSBPAhZBTfAL-m2c4FOF2PphFe-YcnRg@mail.gmail.com>
 <aQEvm40W3aVizp5Q@nathan>
 <CAA5RZ0sdhUjVVKYbBGs1qFsYC3-Mn+as=K5v8ydVGR5iziabFQ@mail.gmail.com>
 <aQI39ln_jZ8qorLE@nathan>
 <CA+Tgmoa_5aC0w1fG7pLhev+F-GtRhQ2OzePy7t059c9JTnvjow@mail.gmail.com>
 <aQPSYD11NDoREZsg@nathan>
 <CAA5RZ0uo-nU9KqgXV5Tcf8aWWQkxsb5BDpkb-2Qfwbw-UVVaUA@mail.gmail.com>
 <CAA5RZ0tpV3PRHejTGG5-LSsqNKKV0qP=SDvurs8wj7pTk7jYJw@mail.gmail.com>
 <aQUYP1WjrEP3buQz@nathan>
 <CAApHDvqe7ee=vobWe4GVAt2gm_H6eiGNZeo_dEMptvHYAkibBA@mail.gmail.com>
 <CAApHDvoCFQxaQjUncTAtCRFAeANe2tpc-WCkJue=FaXRYOkV=Q@mail.gmail.com>
 <CAA5RZ0sw+9rEaW9taNpRZWvuLYMjRa9iibneGfB2ftNSUHT0Ww@mail.gmail.com>
In-Reply-To: 
 <CAA5RZ0sw+9rEaW9taNpRZWvuLYMjRa9iibneGfB2ftNSUHT0Ww@mail.gmail.com>
From: David Rowley <dgrowleyml@gmail.com>
Date: Fri, 7 Nov 2025 12:05:43 +1300
Message-ID: 
 <CAApHDvq_j+GVqX_ZAmvn236Mgg5OYQ6_s9kVsyoo1tJa2RJ=2w@mail.gmail.com>
Subject: Re: another autovacuum scheduling thread
To: Sami Imseih <samimseih@gmail.com>
Cc: Nathan Bossart <nathandbossart@gmail.com>,
 Robert Haas <robertmhaas@gmail.com>,
	Jeremy Schneider <schneider@ardentperf.com>, pgsql-hackers@postgresql.org
Content-Type: text/plain; charset="UTF-8"
Archived-At: 
 <https://www.postgresql.org/message-id/CAApHDvq_j%2BGVqX_ZAmvn236Mgg5OYQ6_s9kVsyoo1tJa2RJ%3D2w%40mail.gmail.com>
Precedence: bulk

On Fri, 7 Nov 2025 at 11:21, Sami Imseih <samimseih@gmail.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. If
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?

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?! I just
cannot comprehend why we'd deviate from making the priority the
percentage over the trigger point here.  If we come to the conclusion
that we want something else, then maybe our trigger point threshold
method also needs to be redefined. There certainly have been
complaints about 20% of a huge table being too much (I guess
autovacuum_vacuum_max_threshold is our answer to trying to fix that
one).

David

David

David