Re: To keep indexes in memory, is large enough effective_cache_size enough?

On 19 September 2018 at 22:12, Kaixi Luo <kaixiluo(at)gmail(dot)com> wrote:
> Does a large shared_buffers impact checkpoint performance negatively? I was
> under the impression that everything inside shared_buffers must be written
> during a checkpoint.

Only the dirty buffers get written.

Also having too small a shared buffers can mean that buffers must be
written more than they'd otherwise need to be. If a buffer must be
evicted from shared buffers to make way for a new buffer then the
chances of having to evict a dirty buffer increases with smaller
shared buffers. Obviously, this dirty buffer needs to be written out
before the new buffer can be loaded in. In a worst-case scenario, a
backend performing a query would have to do this. pg_stat_bgwriter is
your friend.

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services