Postgresql: does effective_cache_size includes shared_buffers

database-tuningpostgresqlpostgresql-performance

I am tuning a PG installation with 32GB of RAM.

The question is: which one of the following sentences is true?

  1. effective_cache_size + shared_buffers ~= 30GB (PG knows that it has shared buffer plus the effective_cache_size)

  2. effective_cache_size ~= 30GB (effective_cache_size includes shared_buffers)

Best Answer

Quote from the manual

When setting this parameter you should consider both PostgreSQL's shared buffers and the portion of the kernel's disk cache that will be used for PostgreSQL data files

(emphasis mine)

So the answer is: yes, it includes the shared buffers.

If you set shared buffer to e.g. 8GB and see that the operating system uses approx 16GB for caching, effective_cache_size should be set to 24GB

Note that this parameter does not allocate any memory. It's just a hint to the optimizer how likely it is that a block needs to physically read from disk.