Best practise Azure Postgresql Flexible Server maximum connection slots

Question

Hello,

I have a question regarding the maximum allowed connection slots on a Azure Postgresql Flexible Server. Initially when a server is created, Microsoft sets the default maximum allowed connection slots to the maximum what the SKU can provide. Now recently we have updated our Azure Postgresql Flexible Server to a higher tier, which provides more maximum allowed connection slots. I'm reading that it isn't a best practise to just slap the maximum allowed connections to the maximum that the SKU provides. In our solution we make use of PGBouncer (we're using a GP server). So my question is, should I set the allowed connections to the maximum or should I even lower it which is more in line with the metrics? We have now 829 maximum connection slots but were utilizing only around the 100 of them.

Thank you in advance!

Kind regards,

Michiel

Answer 1

Since you are utilizing PgBouncer, the primary goal is to minimize the number of backend database connections to reduce process overhead and context switching. PostgreSQL allocates a specific amount of memory for every connection slot regardless of whether it is active or idle. Keeping 829 slots open when you only use 100 is likely lead to to wasted memory that the database could otherwise use for its shared_buffers or disk caching, which directly impacts query performance.

Instead, you might consider setting the database max_connections parameter to a value that covers your peak concurrent backend needs plus a small overhead for administrative tasks and maintenance workers. If your metrics show a peak of 100 connections, setting the limit to approximately 150 or 200 is more efficient than leaving it at 829. This prevents the "thundering herd" problem where a sudden spike in requests could potentially overwhelm the CPU with connection management rather than actual data processing.

You can adjust this value in the Azure Portal under the Server Parameters section for your Flexible Server. Search for the max_connections parameter and update it to your desired value. Keep in mind that reducing this value usually requires a server restart to take effect. Once applied, ensure that your PgBouncer configuration for default_pool_size is aligned with this new limit so that the proxy does not attempt to open more connections than the database now allows.

---

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin