Re: New GUC autovacuum_max_threshold ? - Mailing list pgsql-hackers

From Nathan Bossart
Subject Re: New GUC autovacuum_max_threshold ?
Date
Msg-id 20240425192131.GA888056@nathanxps13
Whole thread Raw
In response to Re: New GUC autovacuum_max_threshold ?  (Robert Haas <robertmhaas@gmail.com>)
Responses Re: New GUC autovacuum_max_threshold ?
Re: New GUC autovacuum_max_threshold ?
List 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: https://aws.amazon.com



pgsql-hackers by date:

Previous
From: Robert Haas
Date:
Subject: Re: New GUC autovacuum_max_threshold ?
Next
From: Robert Haas
Date:
Subject: Re: New GUC autovacuum_max_threshold ?