On Thu, May 13, 2021 at 5:06 AM Justin Pryzby <pry...@telsasoft.com> wrote: > Why is the allowed range from 0 to 0.05? Why not 0.10 or 1.0 ? > The old GUC of the same name had max 1e10.
It also had a completely different purpose and default. > I think a reduced range and a redefinition of the GUC would need to be called > out as an incompatibility. The justification from Michael for this approach is that not having this level of control would be weird, at least to him. But that justification itself seems weird to me; why start from the premise that you need a knob (as opposed to an off switch) at all? Why not start with the way the mechanism works (or is intended to work) in practice? Most individual tables will *never* have VACUUM apply the optimization with *any* reasonable threshold value, so we only need to consider the subset of tables/workloads where it *might* make sense to skip index vacuuming. This is more qualitative than quantitative. It makes zero sense to treat the threshold as a universal scale -- this is one reason why I don't want to expose a true tunable knob to users. Though the threshold-driven/BYPASS_THRESHOLD_PAGES design is not exactly something with stable behavior for a given table, it almost works like that in practice: tables tend to usually skip index vacuuming, or never skip it even once. There is a clear bifurcation along this line when you view how VACUUM behaves with a variety of different tables using the new autovacuum logging stuff. Almost all of the benefit of the optimization is available with the current BYPASS_THRESHOLD_PAGES threshold (2% of heap pages have LP_DEAD items), which has less risk than a higher threshold. I don't think it matters much if we have the occasional unnecessary round of index vacuuming on account of not applying the optimization. The truly important benefit of the optimization is to not do unnecessary index vacuuming all the time in the extreme cases where it's really hard to justify. There is currently zero evidence that anything higher than 2% will ever help anybody to an appreciably degree. (There is also no evidence that the optimization will ever need to be disabled, but I accept the need to be conservative and offer an off switch -- the precautionary principle applies when talking about new harms.) Not having to scan every index on every VACUUM, but only every 5th or so VACUUM is a huge improvement. But going from every 5th VACUUM to every 10th VACUUM? That's at best a tiny additional improvement in exchange for what I'd guess is a roughly linear increase in risk (maybe a greater-than-linear increase, even). That's an awful deal. -- Peter Geoghegan