Re: New GUC autovacuum_max_threshold ?

From: Nathan Bossart <nathandbossart(at)gmail(dot)com>
To: Robert Haas <robertmhaas(at)gmail(dot)com>
Cc: Melanie Plageman <melanieplageman(at)gmail(dot)com>, Frédéric Yhuel <frederic(dot)yhuel(at)dalibo(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>, David Rowley <dgrowleyml(at)gmail(dot)com>
Subject: Re: New GUC autovacuum_max_threshold ?
Date: 2024-04-25 19:21:31
Message-ID: 20240425192131.GA888056@nathanxps13
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

On Thu, Apr 25, 2024 at 02:33:05PM -0400, Robert Haas wrote:
> What does surprise me is that Frédéric suggests a default value of
> 500,000. If half a million tuples (proposed default) is 20% of your
> table (default value of autovacuum_vacuum_scale_factor) then your
> table has 2.5 million tuples. Unless those tuples are very wide, that
> table isn't even 1GB in size. I'm not aware that there's any problem
> at all with the current formula on a table of that size, or even ten
> times that size. I think you need to have tables that are hundreds of
> gigabytes in size at least before this starts to become a serious
> problem. Looking at this from another angle, in existing releases, the
> maximum usable amount of autovacuum_work_mem is 1GB, which means we
> can store one-sixth of a billion dead TIDs, or roughly 166 million.
> And that limit has been a source of occasional complaints for years.
> So we have those complaints on the one hand, suggesting that 166
> million is not enough, and then we have this proposal, saying that
> more than half a million is too much. That's really strange; my
> initial hunch is that the value should be 100-500x higher than what
> Frédéric proposed.

Agreed, the default should probably be on the order of 100-200M minimum.

The original proposal also seems to introduce one parameter that would
affect all three of autovacuum_vacuum_threshold,
autovacuum_vacuum_insert_threshold, and autovacuum_analyze_threshold. Is
that okay? Or do we need to introduce a "limit" GUC for each? I guess the
question is whether we anticipate any need to have different values for
these limits, which might be unlikely.

Nathan Bossart
Amazon Web Services:

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Robert Haas 2024-04-25 20:21:02 Re: New GUC autovacuum_max_threshold ?
Previous Message Robert Haas 2024-04-25 18:33:05 Re: New GUC autovacuum_max_threshold ?