Re: New GUC autovacuum_max_threshold ?

On Thu, Apr 25, 2024 at 4:57 PM Frédéric Yhuel
<frederic(dot)yhuel(at)dalibo(dot)com> wrote:
> Now I have just read Robert's new message, and I understand his point.
> But is there a real problem with triggering analyze after every 500000
> (or more) modifications in the table anyway?

It depends on the situation, but even on a laptop, you can do that
number of modifications in one second. You could easily have a
moderate large number of tables that hit that threshold every minute,
and thus get auto-analyzed every minute when an autovacuum worker is
launched in that database. Now, in some situations, that could be a
good thing, because I suspect it's not very hard to construct a
workload where constantly analyzing all of your busy tables is
necessary to maintain query performance. But in general I think what
would happen with such a low threshold is that you'd end up with
autovacuum spending an awful lot of its available resources on useless
analyze operations, which would waste I/O and CPU time, and more
importantly, interfere with its ability to get vacuums done.

To put it another way, suppose my tables contain 10 million tuples
each, which is not particularly large. The analyze scale factor is
10%, so currently I'd analyze after a million table modifications.
Your proposal drops that to half a million, so I'm going to start
analyzing 20 times more often. If you start doing ANYTHING to a
database twenty times more often, it can cause a problem. Twenty times
more selects, twenty times more checkpoints, twenty times more
vacuuming, whatever. It's just a lot of resources to spend on
something if that thing isn't actually necessary.

--
Robert Haas
EDB: http://www.enterprisedb.com