Hello,
I would like to suggest a new parameter, autovacuum_max_threshold, which
would set an upper limit on the number of tuples to delete/update/insert
prior to vacuum/analyze.
A good default might be 500000.
The idea would be to replace the following calculation :
vacthresh = (float4) vac_base_thresh + vac_scale_factor * reltuples;
with this one :
vacthresh = (float4) vac_base_thresh + vac_scale_factor * reltuples / (1
+ vac_scale_factor * reltuples / autovacuum_max_threshold)
(and the same for the others, vacinsthresh and anlthresh).
The attached graph plots vacthresh against pgclass.reltuples, with
default settings :
autovacuum_vacuum_threshold = 50
autovacuum_vacuum_scale_factor = 0.2
and
autovacuum_max_threshold = 500000 (the suggested default)
Thus, for small tables, vacthresh is only slightly smaller than 0.2 *
pgclass.reltuples, but it grows towards 500000 when reltuples → ∞
The idea is to reduce the need for autovacuum tuning.
The attached (draft) patch further illustrates the idea.
My guess is that a similar proposal has already been submitted... and
rejected 🙂 If so, I'm very sorry for the useless noise.
Best regards,
Frédéric
