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