In my previous blog post on Azure blogs, I explain why it is important to leverage connection pooling proxy with PostgreSQL and how using a connection pooling proxy like PgBouncer against Azure Database for PostgreSQL can provide a significant performance boost, assuming connection pooling is not used at the application layer. To give some estimates of the performance improvement when using PgBouncer for connection pooling with Azure Database for PostgreSQL, we ran a simple performance benchmark test with pgbench. pgbench provides a configuration setting to create new connection for every transaction so we leveraged that to measure the impact of connection latency on throughput of the application. The following are the observations with A/B testing comparing throughput with standard pgbench benchmark testing with and without PgBouncer. In the test, we ran pgbench with scale factor of 5 against Azure Database for PostgreSQL running on general purpose tier with 2 vCores (GP_Gen5_2). The only variable during the tests was PgBouncer. With PgBouncer, the throughput improved 4x times as shown below while connection latency was reduced by 40 percent. In this blog post, we share the steps to install and setup PgBouncer connection pooling proxy between application and Azure DB for PostgreSQL to reap the performance and resiliency benefits it offers. In addition, we also provide you with a QuickStart template at the end of the blog post which can enable you to quickly deploy and setup a PgBouncer in a VM and get started. PgBouncer connection proxy is setup between the application and database layer as shown in the image below. As Azure DB for PostgreSQL is a fully managed platform service, you won’t have access to install any external component on database server. In this case, if you are running your application on an Azure VM, you can setup PgBouncer on the same VM running the application. If the application runs on a managed service like Azure App Services or Azure Functions, you may have to provision a separate Ubuntu VM to run PgBouncer proxy service. However, if you are running your application on Azure Kubernetes Services (AKS), you can leverage PgBouncer sidecar to run it as a Kubernetes sidecar to your application. In this blog post, we will focus on steps to install and run PgBouncer on Ubuntu VM while steps to setup PgBouncer sidecar can be a topic for a follow-up blog post. Steps to setup PgBouncer on Ubuntu VM All the steps mentioned below uses Azure CLI to provision and deploy resources. Provision an Ubuntu VM. If your application is already running on Azure VM and you have a VM provisioned, you can skip this step and move to step 2. az group create --name myResourceGroup --location eastus az vm create \ --resource-group myResourceGroup \ --name PgBouncerPoolVM \ --image UbuntuLTS \ --admin-username pgadminuser \ --generate-ssh-keys Open port 5432 on Network Security Group (NSG) on Ubuntu VM so PgBouncer can listen on the port for incoming connections from application. az vm open-port --port 5432 --resource-group myResourceGroup –name PgBouncerPoolVM Important Note: The above command will create an NSG for the VM and open port 5432 on the VM for Any Source IP. This is not recommended from security perspective. Ideally, the VM should be configured in a VNet and the Source IP range must be defined and whitelisted in NSG using az network nsg to restrict access to PgBouncer VM to client application VMs only. Ssh into the VM and install PgBouncer. You can leverage just in time secure access to login to VM to configure PgBouncer. ssh pgadminuser@
