Reduce database index bloat regularly
Total database index bloat is at about 500 GB currently and growing. This is the chart for the last 90 days (yellow line is index bloat):
While this makes up only 10% of the total database size, it is still non-negligible. We should be doing index repacking on a regular cadence until we find better vacuum settings or benefit from PG 12 improvements.
I suspect that in any case a regular routine to repack the most affected indexes (and perhaps tables) is needed and doing this manually (including the decision making) each time is wasting time.
I would like this issue to cover two topics:
- How can we make database index maintenance a regular task with as little effort as possible?
- Short term, can we schedule database index repacking again?