I have a select query that is consuming a lot of I/O and an increase in the number of calls led to max IOPS saturation in past. The table is heavily partitioned and I was wondering if there was a way with pg_prewarm to just pre_warm the latest partitions. Also, I was thinking of increasing the size of shared_buffer from 25% to 40% as its a fairly large database
Is this the right use case for pg_prewarm?
The most-used data will already be warm, except for during a period after a restart. Does the problem only occur after the database is restarted? If so, pg_prewarm could help, otherwise it probably will not.
Increasing shared_buffers to 40% could easily backfire. When a new page is read, it first is read into the filesystem file cache, and then into shared_buffers. That means the page is placed into memory twice. Increasing shared_buffers can increase the amount of double-buffering and so decrease the overall cache effectiveness. In some ways the worst setting for shared_buffers is 50% of RAM, because that way the data is maximally fragmented between two cache-replacement algorithms that can't coordinate with each other.
High IOPS could be driven by poor plans/missing indexes. Tuning your queries might be easier and more effective than playing around with the IO caching.
