Background : For some workloads involving high volume of INSERT/UPDATE/DELETE, It is sometimes beneficial
to schedule regular REINDEX of high-activity indexes, so as to improve performance, or restore performance levels
backto what it was earlier, by removing dead keys etc. This can result in the average page density of these
indexesfluctuating up and down in a saw-tooth fashion, REINDEX causing large increase in density (large drop in total
numberof pages) and the workload gradually decreasing density back to some "steady-state".
Suggestion : it would be useful if REINDEX could , when some new parameter is set , first determine current average
leafpage density in the index to be rebuilt, (e.g. the value of pgstatindex().avg_leaf_density from the
pgstattupleextension ), and then adopt this density as the temporary override FILLFACTOR while rebuilding index
pages, as to to minimize change in density.
This would avoid the saw-tooth effect on number of pages, and also reduce the number of index page-splits which occur
duringthe period immediately following a REINDEX done with default FILLFACTOR of 90%. In effect, it lessens the need
forthe physical reorganization aspect of REINDEX and focusses more on the function of removing dead keys.
An admin do this for themselves by monitoring index page density and setting the FILLFACTOR to the current density
beforeeach REINDEX (and may find that this doesn't change much if the workload is truly steady-state), but I wonder
ifthis community would agree that it would provide a useful automation of the process.
Cheers, John Lumby