> > Doesn't this design completely fail to take index bloat into account? > Repairing heap fragmentation does not reduce the need for VACUUM to work > on the indexes.
Index bloat is a bit of an open issue already. Because page splits already prune any LP_DELETEd pointers any busy index keys will be pruned already.
However any index keys which have not been the subject of an index lookup -- and that includes keys which are only accessed by bitmap-index-scans -- won't be pruned.
So we don't really know how much bloat is currently in an index. Perhaps we need a new statistic which gets updated whenever a page split prunes LP_DELETEd pointers (or perhaps when LP_DELETE is set?).
I agree here. As a first step, may be can address the heap space usage statistics and then take up index stats separately. Index bloat would carry a different weight in triggering autovacuum.
I shall code up a patch which tracks the dead space in the heap and trigger autovac based on that.