Le 25/04/2024 à 18:51, Melanie Plageman a écrit :
>> I'm not too sure I understand. What are the reasons it might by skipped?
>> I can think of a concurrent index creation on the same table, or
>> anything holding a SHARE UPDATE EXCLUSIVE lock or above. Is this the
>> sort of thing you are talking about?
> No, I was thinking more literally that, if reltuples (assuming
> reltuples is modified/inserted tuples) > autovacuum_max_threshold, I
> would expect the table to be vacuumed. However, with your formula,
> that wouldn't necessarily be true.
>
> I think there are values of reltuples and autovacuum_max_threshold at
> which reltuples > autovacuum_max_threshold but reltuples <=
> vac_base_thresh + vac_scale_factor * reltuples / (1 + vac_scale_factor
> * reltuples / autovacuum_max_threshold)
>
> I tried to reduce the formula to come up with a precise definition of
> the range of values for which this is true, however I wasn't able to
> reduce it to something nice.
>
> Here is just an example of a case:
>
> vac_base_thresh = 2000
> vac_scale_factor = 0.9
> reltuples = 3200
> autovacuum_max_threshold = 2500
>
> total_thresh = vac_base_thresh + vac_scale_factor * reltuples / (1 +
> vac_scale_factor * reltuples / autovacuum_max_threshold)
>
> total_thresh: 3338. dead tuples: 3200. autovacuum_max_threshold: 2500
>
> so there are more dead tuples than the max threshold, so it should
> trigger a vacuum, but it doesn't because the total calculated
> threshold is higher than the number of dead tuples.
>
OK, thank you! I got it.
> This of course may not be a realistic scenario in practice. It works
> best the closer scale factor is to 1 (wish I had derived the formula
> successfully) and when autovacuum_max_threshold > 2 * vac_base_thresh.
> So, maybe it is not an issue.
I haven't thought much about this yet. I hope we can avoid such an
extreme scenario by imposing some kind of constraint on this parameter,
in relation to the others.
Anyway, with Nathan and Robert upvoting the simpler formula, this will
probably become irrelevant anyway :-)