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

On Fri, Jun 4, 2021 at 3:15 PM Michael Paquier <michael(at)paquier(dot)xyz> wrote:
>
> On Mon, May 31, 2021 at 10:30:08AM +0900, Masahiko Sawada wrote:
> > On Fri, May 28, 2021 at 9:53 AM Peter Geoghegan <pg(at)bowt(dot)ie> wrote:
> >> Another concern with this approach is what it
> >> means for the VACUUM command itself. I haven't added an 'auto'
> >> spelling that is accepted by the VACUUM command in this POC version.
> >> But do I need to at all? Can that just be implied by not having any
> >> INDEX_CLEANUP option?
> >
> > 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?
>
> I have read through the patch, and I am surprised to see that this
> only makes possible to control the optimization at relation level.
> The origin of the complaints is that this index cleanup optimization
> has been introduced as a new rule that gets enforced at *system*
> level, so I think that we should have an equivalent with a GUC to
> control the behavior for the whole system. With what you are
> presenting here, one could only disable the optimization for each
> relation, one-by-one. If this optimization proves to be a problem,
> it's just going to be harder to users to go through all the relations
> and re-tune autovacuum. Am I missing something?

I've not thought to disable that optimization at system level. I think
we can use this option for particular tables whose indexes
unexpectedly bloated much due to this optimization. Similarly, we have
DISABLE_PAGE_SKIPPING option but don’t have a way to disable lazy
vacuum’s page skipping behavior at system level.

Regards,

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