Re: Teaching users how they can get the most out of HOT in Postgres 14

On Thu, May 13, 2021 at 01:27:44PM -0700, Peter Geoghegan wrote:
> 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.

Perhaps that's an awful deal, but based on which facts can you really
say that this new behavior of needing at least 2% of relation pages
with some dead items to clean up indexes is not a worse deal in some
cases? This may cause more problems for the in-core index AMs, as
much as it could impact any out-of-core index AM, no? What about
other values like 1%, or even 5%? My guess is that there would be an
ask to have more control on that, though that stands as my opinion.

Saying that, as long as there is a way to disable that for the users
with autovacuum and manual vacuums, I'd be fine. It is worth noting
that adding an GUC to control this optimization would make the code
more confusing, as there is already do_index_cleanup, a
vacuum_index_cleanup reloption, and specifying vacuum_index_cleanup to
TRUE may cause the index cleanup to not actually kick if the 2% bar is
not reached.
--
Michael