Re: Behavior change in PostgreSQL 14Beta3 or bug?

On Mon, Sep 6, 2021 at 9:21 AM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
> #define BYPASS_THRESHOLD_PAGES  0.02    /* i.e. 2% of rel_pages */
>
> So up to an additional 2% of all pages can have the all-visible bit
> unset with "index_cleanup = auto".
>
> That is probably not worth worrying, right?

I don't think it's worth worrying about. I would say that, since I
chose the exact threshold myself. The threshold was a bit arbitrary,
of course.

Note that Daniel's example had a non-HOT update, even though it's the
kind of update that we imagine can use HOT (because it didn't modify
an indexed column). He could have ensured a HOT update by lowering
heap fill factor, but why should that be necessary if updates are rare
anyway?

The bypass-index-vacuuming feature may have had a bit of a messaging
problem. It was something we usually talked about as being about
skipping index vacuuming, because that's what it actually does.
However, the feature isn't really about doing less work during VACUUM.
It's actually about doing *more* work during VACUUM -- more useful
work. Especially setting visibility map bits. But also freezing. Now
you can very aggressively tune VACUUM to do these things more often,
with no fear of that being way too expensive because of index
vacuuming that has only marginal value.

The threshold is not so much about any one VACUUM operation -- you
have to think about the aggregate effect on the table over time. Most
individual tables will never have the new optimization kick in even
once, because the workload just couldn't possibly allow it -- the 2%
threshold is vastly exceeded every single time. The cases that it
actually applies to are pretty much insert-only tables, perhaps with
some HOT updates. 100% clean inserts are probably very rare in the
real world. I believe that it's *vastly* more likely that such a table
will have pages that are ~98%+ free of LP_DEAD line pointers in heap
pages (i.e., the thing that BYPASS_THRESHOLD_PAGES applies to). To get
to 100% you cannot allow even one single insert transaction to abort
since the last VACUUM.

If you assume that BYPASS_THRESHOLD_PAGES is actually too low for your
workload (which is the opposite problem), then it doesn't matter very
much. The feature as coded should still have the desired effect of
skipping index vacuuming in *most* cases where it's unnecessary
(however you happen to define "unnecessary") -- the number of pages
with LP_DEAD items will naturally increase over time without index
vacuuming, until the threshold is crossed. Maybe still-unnecessary
index vacuuming will still take place in 1 out of 5 cases with the
feature. This is still much better than 5 out of 5. More importantly,
you can now aggressively tune vacuuming without noticeably increasing
the number of individual vacuums that still have the problem of
unnecessary index vacuuming. So if you go from 5 vacuums per day to 20
through tuning alone, the number of vacuum operations that do
unnecessary index vacuuming doesn't increase at all (except perhaps
due to rounding effects).

-- 
Peter Geoghegan