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

On Wed, May 19, 2021 at 6:09 AM Peter Geoghegan <pg(at)bowt(dot)ie> wrote:
>
> On Tue, May 18, 2021 at 7:29 AM Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
> > If this skipping
> > behavior badly affects other indexes AMs, this optimization should be
> > considered within btree indexes, although we will need a way for index
> > AMs to consider and tell the vacuum strategy. But I guess this works
> > well in most cases so having an on/off switch suffice.
>
> Right. I doubt that it will actually turn out to be necessary to have
> such a switch. But I try to be modest when it comes to predicting what
> will be important to some user workload -- it's way too complicated to
> have total confidence about something like that. It is a risk to be
> managed.

I think the possible side effect of this hard-coded
BYPASS_THRESHOLD_PAGES would be that by default, bulkdelete is not
called for a long term and the index becomes bloat. IOW, we will
enforce users have the index bloat corresponding to 2% of table pages.
The bloat could be serious depending on the index tuple size (e.g.,
index including many columns). The user may have been running
autovacuums aggressively on that table to prevent the index bloat but
it's no longer possible and there is no choice. So I think that for
those (relatively) rare use cases, it's good to provide a way to
somehow control it. Fortunately, an on/off switch is likely to be
useful for controlling other optimizations that could be added in the
future.

Regards,

--
Masahiko Sawada
EDB: https://www.enterprisedb.com/