Re: REINDEX : new parameter to preserve current average leaf density as new implicit FILLFACTOR

On Tue, Jun 25, 2019 at 2:56 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > This would avoid the saw-tooth effect on number of pages, and also reduce the number of index page-splits which occur during the period immediately following a REINDEX done with default FILLFACTOR of 90%. In effect, it lessens the need for the physical reorganization aspect of REINDEX and focusses more on the function of removing dead keys.
>
> I think you've confused REINDEX with VACUUM. It seems like a pretty poor
> substitute for that --- it's much more expensive and has worse locking
> requirements.

There is a very recent research paper that discusses the idea of
varying fillfactor with a view to ameliorating page splits:

https://btw.informatik.uni-rostock.de/download/tagungsband/B2-2.pdf

I found the paper to be fairly convincing. The general idea is to make
page splits occur at a steady rate following a REINDEX, rather than
having "waves" of page splits. This is quite different to changing
leaf fillfactor based on the observed leaf density, though. You can
already do that by looking at pgstattuple's pgstatindex() function,
which reports a avg_leaf_density for the index. Though I agree that
that's not likely to help matters. Apart from anything else, the
steady state of an index is embodied by more than just its
avg_leaf_density. Especially following the v12 enhancements to B-Tree
indexes.

--
Peter Geoghegan