11:27

shared_buffers is not a sensitive setting

9 June 2017

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.

There are 2 comments.

bezpieczne haslo at 13:23, 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 at 13:45, 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.