On Sat, Mar 13, 2021 at 7:23 PM Peter Geoghegan <p...@bowt.ie> wrote: > In other words, I am not worried about debt, exactly. Debt is normal > in moderation. Healthy, even. I am worried about bankruptcy, perhaps > following a rare and extreme event. It's okay to be imprecise, but all > of the problems must be survivable. The important thing to me for a > maintenance_work_mem threshold is that there is *some* limit. At the > same time, it may totally be worth accepting 2 or 3 index scans during > some eventual VACUUM operation if there are many more VACUUM > operations that don't even touch the index -- that's a good deal! > Also, it may actually be inherently necessary to accept a small risk > of having a future VACUUM operation that does multiple scans of each > index -- that is probably a necessary part of skipping index vacuuming > each time. > > Think about the cost of index vacuuming (the amount of I/O and the > duration of index vacuuming) as less as less memory is available for > TIDs. It's non-linear. The cost explodes once we're past a certain > point. The truly important thing is to "never get killed by the > explosion".
I just remembered this blog post, which gives a nice high level summary of my mental model for things like this: https://jessitron.com/2021/01/18/when-costs-are-nonlinear-keep-it-small/ This patch should eliminate inefficient index vacuuming involving very small "batch sizes" (i.e. a small number of TIDs/index tuples to delete from indexes). At the same time, it should not allow the batch size to get too large because that's also inefficient. Perhaps larger batch sizes are not exactly inefficient -- maybe they're risky. Though risky is actually kind of the same thing as inefficient, at least to me. So IMV what we want to do here is to recognize cases where "batch size" is so small that index vacuuming couldn't possibly be efficient. We don't need to truly understand how that might change over time in each case -- this is relatively easy. There is some margin for error here, even with this reduced-scope version that just does the SKIP_VACUUM_PAGES_RATIO thing. The patch can afford to make suboptimal decisions about the scheduling of index vacuuming over time (relative to the current approach), provided the additional cost is at least *tolerable* -- that way we are still very likely to win in the aggregate, over time. However, the patch cannot be allowed to create a new risk of significantly worse performance for any one VACUUM operation. -- Peter Geoghegan
