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
