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