Re: Skip index cleanup if autovacuum did not do any work

On 28 November 2017 at 23:17, Peter Geoghegan <pg@bowt.ie> wrote:
> BTW, a good short term solution for you might be to change the vacuum
> cost delay settings. They're pretty conservative by default.
>
> There is a good chance that your indexes are mostly in memory even on
> large tables, and B-Tree indexes are read sequentially during VACUUM.

Unfortunately we have already tuned autovacuum to be more aggressive.
Our indexes are large (more than 1TB of indexes for a given table) and
the very frequent wraparound requires us to be doing > 25MB/s just to
do a lot of index vacuuming, which in turn is a significant chunk of the I/O
of the whole system, and is filling memory with unused pages.

I was hoping to find a silver bullet, but it seems it's a bit more complex.

Perhaps there is a way to skip any index maintenance if we can determine
that the heap is all-frozen, all-visible, so for stale partitions, even if only
limited to btree indexes. I'll see if I can review the patch in progress.

Thanks for the advice,

regards,

Feike