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

On Sun, May 30, 2021 at 6:30 PM Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
> We need to accept "yes" and "no" too? Currently, the parsing of a
> boolean type reloption accepts those words.

Added those in the attached revision, version 2. This is much closer
to being commitable than v1 was. I plan on committing this in the next
several days.

I probably need to polish the documentation some more, though.

> It seems to me that it's better to have INDEX_CLEANUP option of VACUUM
> command support AUTO for consistency. Do you have any concerns about
> supporting it?

v2 sorts out the mess with VacOptTernaryValue by just adding a new
enum constant to VacOptTernaryValue, called VACOPT_TERNARY_AUTO -- the
enum still has a distinct VACOPT_TERNARY_DEFAULT value. v2 also adds a
new reloption-specific enum, StdRdOptIndexCleanup, which is the
datatype that we actually use inside the StdRdOptions struct. So we
are now able to specify "VACUUM (INDEX_CLEANUP AUTO)" in v2 of the
patch.

v2 also adds a new option to vacuumdb, --force-index-cleanup. This
seemed to make sense because we already have a --no-index-cleanup
option.

> > And does StdRdOptions.vacuum_truncate now need
> > to become a VacOptTernaryValue field too, for consistency with the new
> > definition of StdRdOptions.vacuum_index_cleanup?
>
> We don't have the bypass optimization for heap truncation, unlike
> index vacuuming. So I think we can leave both vacuum_truncate
> reloption and TRUNCATE option as boolean parameters.

Actually FWIW we do have a bypass optimization for TRUNCATE -- it too
has an always-on dynamic behavior -- so it really is like the index
vacuuming thing. In theory it might make sense to have the same "auto,
on, off" thing, just like with index vacuuming in the patch. However,
I haven't done that in the patch because in practice it's a bad idea.
If we offered users the option of truly forcing truncation, then
lazy_truncate_heap() could just insist on truncating. It would have to
just wait for an AEL, no matter how long it took. That would probably
be dangerous because waiting for an AEL without backing out in VACUUM
just isn't a great idea.

Thanks

--
Peter Geoghegan