On Fri, Feb 7, 2020 at 5:36 AM Steve Atkins <steve@blighty.com> wrote:
What's a good number of active connections to aim for? It probably depends on whether they tend to be CPU-bound or IO-bound, but I've seen the rule of thumb of "around twice the number of CPU cores" tossed around, and it's probably a decent place to start, then run it under normal load and see how it behaves - cpu usage, RAM, IO, request latency and throughput.
Back in the day of spinning media, when I was more active on the list and postgresql 9 was just released, conventional wisdom for starting number was 2*cores + 1*spindles, if memory serves. You can set max_connections higher, but that was the number you wanted to have active, and then adjust for workload - OLTP vs warehouse, how much disk access vs buffer cache, etc. Benchmarks, at the time, showed that performance started to fall off due to contention if the number of processes got much larger. I imagine that the speed of storage today would maybe make 3 or 4x core count a pretty reasonable place to start. There will be a point of diminishing returns somewhere, but you can probably construct your own benchmarks to determine where that point is likely to be for your workload.
I was doing a lot of java development at the time, and tended to use a connection pool per application server rather than an external connection pool in front of postgresql, just for ease of administration, so I might have more connections than the desired pool size, but I tried to keep the number of active connections under that limit and set max_connections to a value that prevented me from being locked out if each application server was at max pool size. I'm not sure how well that strategy would work now that autoscaling is so ubiquitous, since there is memory allocation overhead associated even with idle connections and the sheer number of per-server pools could get quite high