Re: New GUC autovacuum_max_threshold ?

On Tue, May 07, 2024 at 10:31:00AM -0400, Robert Haas wrote:
> On Wed, May 1, 2024 at 10:03 PM David Rowley <dgrowleyml(at)gmail(dot)com> wrote:
>> I think we need at least 1a) before we can give autovacuum more work
>> to do, especially if we do something like multiply its workload by
>> 1024x, per your comment above.
>
> I guess I view it differently. It seems to me that right now, we're
> not vacuuming large tables often enough. We should fix that,
> independently of anything else. If the result is that small and medium
> sized tables get vacuumed less often, then that just means there were
> never enough resources to go around in the first place. We haven't
> taken a system that was working fine and broken it: we've just moved
> the problem from one category of tables (the big ones) to a different
> category of tables. If the user wants to solve that problem, they need
> to bump up the cost limit or add hardware. I don't see that we have
> any particular reason to believe such users will be worse off on
> average than they are today. On the other hand, users who do have a
> sufficiently high cost limit and enough hardware will be better off,
> because we'll start doing all the vacuuming work that needs to be done
> instead of only some of it.
>
> Now, if we start vacuuming any class of table whatsoever 1024x as
> often as we do today, we are going to lose. But that would still be
> true even if we did everything on your list. Large tables need to be
> vacuumed more frequently than we now do, but not THAT much more
> frequently. Any system that produces that result is just using a wrong
> algorithm, or wrong constants, or something. Even if all the necessary
> resources are available, nobody is going to thank us for vacuuming
> gigantic tables in a tight loop. The problem with such a large
> increase is not that we don't have prioritization, but that such a
> large increase is fundamentally the wrong thing to do. On the other
> hand, I think a more modest increase is the right thing to do, and I
> think it's the right thing to do whether we have prioritization or
> not.

This is about how I feel, too. In any case, I +1'd a higher default
because I think we need to be pretty conservative with these changes, at
least until we have a better prioritization strategy. While folks may opt
to set this value super low, I think that's more likely to lead to some
interesting secondary effects. If the default is high, hopefully these
secondary effects will be minimized or avoided.

--
Nathan Bossart
Amazon Web Services: https://aws.amazon.com