RE: max_connections parameter: too_many_connections error - Mailing list pgsql-general

1. How can we decide on optimal value for max_connections for a given setup/server? I checked many posts saying that even 1000 is considered as a very high value but we are hitting the error too_many_connections due to Max_connections value limit.

I have one set at 1000 but I usually top out around 500 right now.  As long as you have the CPU and memory to handle whatever you’ve set I would think it’s ok.  Hopefully others with more experience with big hardware will chime in.

2. We usee hikari pool on the client side but that even has limitations. Implementing pg_bouncer is another suggestion in the posts. In a HA setup, do we have to set it up on both primary standby?

Client side pooling is a different proposition than server side.  Each server maintaining its own pool VS the server maintaining a common pool, the server side pooling would lower your connection count.  The value of pooling also depends on your app.  If it’s maintaining persistent connections for hours at a time then any kind of pooling is going to be of limited value depending on how the pooler is operating, IE connection pooling VS transactional pooling.    If your transactions are simple you could use transactional pooling which could greatly reduce the connection count. 

For an HA setup you would need to set it up on both PG nodes.  If you’re using the standby for read-only queries then it would have to be running on both nodes at all times and pacemaker would start/stop it as part of the failover process.  If you’re only using the active node then pacemaker would have to start the pooler on the active node and stop it on the passive node, and again control it on both nodes for failover.  If it’s that large of a setup you may want to make the pooler its own cluster with pacemaker to relieve the PG cluster of the additional load.