Date: Tue, 7 May 2024 16:17:02 -0500
From: Nathan Bossart <nathandbossart@gmail.com>
To: Robert Haas <robertmhaas@gmail.com>
Cc: David Rowley <dgrowleyml@gmail.com>, Joe Conway <mail@joeconway.com>,
	Michael Banck <mbanck@gmx.net>,
	Laurenz Albe <laurenz.albe@cybertec.at>,
	=?iso-8859-1?Q?Fr=E9d=E9ric?= Yhuel <frederic.yhuel@dalibo.com>,
	Melanie Plageman <melanieplageman@gmail.com>,
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: New GUC autovacuum_max_threshold ?
Message-ID: <20240507211702.GA2720371@nathanxps13>
References: <c78950c4a37a29b62d4eede6ecc403ecdcd9eeb6.camel@cybertec.at>
 <d120db91-393e-4904-83df-d936eee56db4@dalibo.com>
 <d1af9a71e17f117e514d25bba2306ee845a9fde5.camel@cybertec.at>
 <662b6941.5d0a0220.ea153.4178@mx.google.com>
 <06b361a6-094c-4d78-b4c9-b102e270d97e@joeconway.com>
 <CA+TgmoZav6Mxv6qzPEZiAknzqdQ7Cq-oS1WJ6N-sPe5QyT2tiA@mail.gmail.com>
 <7c91df80-ef1b-426a-a2b3-e11ffb31bcc7@joeconway.com>
 <CA+Tgmoa9FzNLNtD-PYptJP1ApV=a57RwqOVypb0gL5uLzLtCFQ@mail.gmail.com>
 <CAApHDvo8DWyt4CWhF=NPeRstz_78SteEuuNDfYO7cjp=7YTK4g@mail.gmail.com>
 <CA+TgmoaCQ_GCwn3V8uXc+60oSJivY6ouiAqe81Z3h1kT6zMMYQ@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <CA+TgmoaCQ_GCwn3V8uXc+60oSJivY6ouiAqe81Z3h1kT6zMMYQ@mail.gmail.com>
Archived-At: <https://www.postgresql.org/message-id/20240507211702.GA2720371%40nathanxps13>
Precedence: bulk

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@gmail.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