Re: New GUC autovacuum_max_threshold ?

Le 25/04/2024 à 21:21, Nathan Bossart a écrit :
> 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.
>

I'm not sure... 500000 comes from the table given in a previous message. 
It may not be large enough. But vacuum also updates the visibility map, 
and a few hundred thousand heap fetches can already hurt the performance 
of an index-only scan, even if most of the blocs are read from cache.

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

I agree with you, it seems unlikely. This is also an answer to Melanie's 
question about the name of the GUC : I deliberately left out the other 
"vacuum" because I thought we only needed one parameter for these three 
thresholds.

Now I have just read Robert's new message, and I understand his point. 
But is there a real problem with triggering analyze after every 500000 
(or more) modifications in the table anyway?