public inbox for [email protected]  

help / color / mirror / Atom feed

From: Laurenz Albe <[email protected]>
To: Frédéric Yhuel <[email protected]>
To: Robert Haas <[email protected]>
To: Nathan Bossart <[email protected]>
Cc: Melanie Plageman <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Cc: David Rowley <[email protected]>
Subject: Re: New GUC autovacuum_max_threshold ?
Date: Fri, 26 Apr 2024 10:18:00 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
	<CAAKRu_bevP35okEsQuT6dr5jbvD79Gva_-YO_ciBSchT4GDHSg@mail.gmail.com>
	<20240424195747.GA861150@nathanxps13>
	<CA+TgmoY4BENJyYcnU2eLFYZ73MZOb72WymNBH7vug6DtA+CZZw@mail.gmail.com>
	<[email protected]>
	<[email protected]>

On Fri, 2024-04-26 at 09:35 +0200, Frédéric Yhuel wrote:
> 
> Le 26/04/2024 à 04:24, Laurenz Albe a écrit :
> > On Thu, 2024-04-25 at 14:33 -0400, Robert Haas wrote:
> > > I believe that the underlying problem here can be summarized in this
> > > way: just because I'm OK with 2MB of bloat in my 10MB table doesn't
> > > mean that I'm OK with 2TB of bloat in my 10TB table. One reason for
> > > this is simply that I can afford to waste 2MB much more easily than I
> > > can afford to waste 2TB -- and that applies both on disk and in
> > > memory.
> > 
> > I don't find that convincing.  Why are 2TB of wasted space in a 10TB
> > table worse than 2TB of wasted space in 100 tables of 100GB each?
> 
> Good point, but another way of summarizing the problem would be that the 
> autovacuum_*_scale_factor parameters work well as long as we have a more 
> or less evenly distributed access pattern in the table.
> 
> Suppose my very large table gets updated only for its 1% most recent 
> rows. We probably want to decrease autovacuum_analyze_scale_factor and 
> autovacuum_vacuum_scale_factor for this one.
> 
> Partitioning would be a good solution, but IMHO postgres should be able 
> to handle this case anyway, ideally without per-table configuration.

I agree that you may well want autovacuum and autoanalyze treat your large
table differently from your small tables.

But I am reluctant to accept even more autovacuum GUCs.  It's not like
we don't have enough of them, rather the opposite.  You can slap on more
GUCs to treat more special cases, but we will never reach the goal of
having a default that will make everybody happy.

I believe that the defaults should work well in moderately sized databases
with moderate usage characteristics.  If you have large tables or a high
number of transactions per second, you can be expected to make the effort
and adjust the settings for your case.  Adding more GUCs makes life *harder*
for the users who are trying to understand and configure how autovacuum works.

Yours,
Laurenz Albe

view thread (9+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
  Subject: Re: New GUC autovacuum_max_threshold ?
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox