There’s a lot of mystery-cult information floating around out there about what to set shared_buffers to in PostgreSQL. Most of it is, at best, overthinking a fairly simple setting.
You can get 90% of the way there, on 95%+ of PostgreSQL systems, just by setting it to 25% of total system memory, to a maximum of 32GB. (It used to be 8GB, and then 16GB; as processors get faster, the number tends to go up.)
In particular, the difference between 24GB and 32GB, for example, will almost certainly be unmeasurable, on a system with 128GB or more memory.
It can be of benefit to lower shared_buffers on systems that have a high write rate, to reduce the total amount of data flushed on one checkpoint. I don’t have analytic values for that (shame on me!), but I’ve seen benefits by reducing it to as low as 2GB on a very high write-rate system.
But don’t agonize.
Comments
bezpieczne haslo · 10 June 2017
Hi, I would like to ask why 32GB? Where does this value come from? I've heard about 8GB floor and I'm aware about management problems which postgres had. My system is cpu bounded and it happens that just freezes because of cpu load, despite the 80, quite modern, cores with postgres 9.5. What's the correlation between core performance and shared buffers size?Xof · 10 June 2017
It's entirely a rule of thumb. The clock algorithm that PostgreSQL uses to manage the shared buffers tends to be (somewhat) CPU-intensive, so a point of diminishing returns is reached as more space is allocated to shared buffers.