Best practise Azure Postgresql Flexible Server maximum connection slots

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