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

On Thu, May 13, 2021 at 04:27:47PM +0900, Michael Paquier wrote:
> On Tue, May 11, 2021 at 04:42:27PM +0900, Michael Paquier wrote:
> > Whatever the solution chosen, the thing I can see we agree on here is
> > that we need to do something, at least in the shape of an on/off
> > switch to have an escape path in case of problems. Peter, could we
> > get something by beta1 for that? FWIW, I would use a float GUC to
> > control that, and not a boolean switch, but I am just one voice here,
> > and that's not a feature I worked on.
>
> So, I have been thinking more about this item, and a boolean switch
> still sounded weird to me, so attached is a patch to have two GUCs,
> one for manual VACUUM and autovacuum like any other parameters, to
> control this behavior, with a default set at 2% of the number of
> relation pages with dead items needed to do the index cleanup work.
>
> Even if we switch the parameter type used here, the easiest and most
> consistent way to pass down the parameter is just to use VacuumParams
> set within ExecVacuum() and the autovacuum code path. The docs need
> more work, I guess.
>
> Thoughts?

> + cleanup_index_scale_factor = autovacuum_cleanup_index_scale >= 0 ?
> + autovacuum_cleanup_index_scale : VacuumCostDelay;

CostDelay is surely not what you meant.

> + <title>Vacuum parameters for Indexes</title>
> + <para>
> + During the execution of <xref linkend="sql-vacuum"/>
> + and <xref linkend="sql-analyze"/>

"and analyze" is wrong?

> + This parameter can only be set in the <filename>postgresql.conf</filename>
> + file or on the server command line.

It's SIGHUP

> + This parameter can only be set in the <filename>postgresql.conf</filename>
> + file or on the server command line.

Same

+ {
+ {"vacuum_cleanup_index_scale_factor", PGC_SIGHUP, VACUUM_INDEX,
+ gettext_noop("Fraction of relation pages, with at least one dead item, required to clean up indexes."),
+ NULL
+ },
+ &VacuumCleanupIndexScale,
+ 0.02, 0.0, 0.05,
+ NULL, NULL, NULL
+ },

Why is the allowed range from 0 to 0.05? Why not 0.10 or 1.0 ?
The old GUC of the same name had max 1e10.
I think a reduced range and a redefinition of the GUC would need to be called
out as an incompatibility.

Also, the old GUC (removed at 9f3665fbf) had:
- {"vacuum_cleanup_index_scale_factor", PGC_USERSET, CLIENT_CONN_STATEMENT,

I think USERSET and STATEMENT were right ?

Alternately, what if this were in the DEVELOPER category, which makes this
easier to remove in v15.

--
Justin