On 28 November 2017 at 23:17, Peter Geoghegan <p...@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
