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

On Tue, Jun 25, 2019 at 2:45 PM John Lumby <johnlumby(at)hotmail(dot)com> wrote:
> 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".

I suspect that you might find that the enhancements to B-Tree indexes
that went into Postgres 12 would help with this workload, especially
if you notice that this happens with indexes that have a lot of
duplicates. For the full background, you might take a look at my pgCon
talk:

https://youtu.be/p5RaATILoiE

Fair warning: this is a very technical talk.

Does it seem at all possible that you were affected by either the
issue with duplicates, or the issue that is addressed by the "split
after new tuple" optimization? They're both causes of index bloat that
VACUUM cannot usually prevent.

--
Peter Geoghegan