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 back to what it was earlier, by removing dead keys etc. This can result in the average page density of these indexes fluctuating up and down in a saw-tooth fashion, REINDEX causing large increase in density (large drop in total number of 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 leaf page density in the index to be rebuilt, (e.g. the value of pgstatindex().avg_leaf_density from the pgstattuple extension ), 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 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. An admin do this for themselves by monitoring index page density and setting the FILLFACTOR to the current density before each REINDEX (and may find that this doesn't change much if the workload is truly steady-state), but I wonder if this community would agree that it would provide a useful automation of the process. Cheers, John Lumby
