On Tue, Jun 25, 2019 at 2:56 PM Tom Lane <tgl@xxxxxxxxxxxxx> 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
