Azure PostgreSQL flexible server connection pool exhausted

Question

Dear Team,

For the past 2-4 weeks, I have been facing a persistent challenge regarding the utilisation of Azure PostgreSQL Flexible Server with PgBouncer (as a managed service). Despite extensive optimisation attempts, such as fine-tuning the PostgreSQL database and setting the connection pool limit to a maximum of 5000 connections, I'm encountering continuous exhaustion of the entire pool. Consequently, numerous users seeking to connect to the database via the PgAdmin application are encountering connection denials.

I am seeking guidance on how to effectively track all connections, including details on which clients are establishing connections, the associated databases, user information, query execution details, and the duration of these connections.

To provide further context, the configuration details for the Azure PostgreSQL Flexible Server I am utilising are as follows:

Server Type: General Purpose
Instance Type: D32ds_v5
Specifications: 32 vCores, 128 GB of RAM, and 1024 GB of storage

Additionally, I have sporadically observed dashed lines (---) appearing in the storage and CPU monitoring tab.

Urgent resolution of this connectivity issue is paramount to our operational requirements. I would greatly appreciate your valuable insights and prompt guidance on addressing this concern.

Your prompt attention to this matter is highly appreciated.

Answer 1

Hi @@Rishabh we have bunch of documentation regrading pgBouncer,  

Consult Azure Documentation: Our Azure documentation provides comprehensive guidance on utilizing PgBouncer with Azure PostgreSQL Flexible Server. Please refer to this link for detailed information: Azure PostgreSQL Flexible Server and PgBouncer.

Admin Console for Connection Monitoring: Utilize the admin console to observe active connections. This tool can be vital in understanding your current usage and pinpointing issues. Details on how to use the admin console can be found here: Admin Console for PgBouncer.

Review Pool Mode: It's crucial to ensure that the pool mode configured in PgBouncer aligns with your application's needs. If PgAdmin is your primary client, the transaction pool mode might be more suitable, especially if your clients typically maintain open sessions without disconnecting. More information about pool modes can be found in our documentation.

PgBouncer Metrics: Monitoring PgBouncer metrics can provide insights into the performance and issues of your connection pool. You can access these metrics here: PgBouncer Metrics.

Azure Blog Posts for Additional Insights: For more insights and best practices, take a look at our blog post on Query Performance Insight: Query Performance Insight Blog Post.

I hope these resources prove helpful in resolving your connectivity issues.

Regards

Geetha