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