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.
All the steps mentioned below uses Azure CLI to provision and deploy resources.
az group create --name myResourceGroup --location eastus
az vm create \
--resource-group myResourceGroup \
--name PgBouncerPoolVM \
--image UbuntuLTS \
--admin-username pgadminuser \
--generate-ssh-keys
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 pgadminuser@<PublicIPEndpoint>
sudo apt-get update
sudo apt-get install -y pgbouncer
sudo wget https://www.digicert.com/CACerts/BaltimoreCyberTrustRoot.crt
sudo openssl x509 -inform DER -in BaltimoreCyberTrustRoot.crt -text -out /etc/root.crt
[databases]
* = host=<servername>.postgres.database.azure.com port=5432
[pgbouncer]
# Do not change these settings:
listen_addr = 0.0.0.0
auth_file = /etc/pgbouncer/userlist.txt
auth_type = trust
server_tls_sslmode = verify-ca
server_tls_ca_file = /etc/root.crt
# These are defaults and can be configured
# please leave them as defaults if you are
# uncertain.
listen_port = 5432
unix_socket_dir =
user = postgres
pool_mode = session
max_client_conn = 100
ignore_startup_parameters = extra_float_digits
admin_users = postgres
"username@hostname" "password"
Each entry is simply new line separated for example:
"sa@mypgserver" "P@ssword1234"
"test@mypgserver" "Test@#1234"
Note: Azure Database for PostgreSQL usernames are always in the format username@hostname
sudo service pgbouncer start
more /var/log/postgresql/pgbouncer.log
sudo apt-get update
sudo apt-get install postgresql-client
psql -h 127.0.0.1 -p 5432 -U sa@mypgserver -d postgres
psql -h <PublicIPEndpoint> -p 5432 -U sa@mypgserver -d postgres
You are now all setup to leverage PgBouncer connection pooling proxy to connect to Azure DB for PostgreSQL service.
If you would like to skip the above steps and want to quickly setup and test PgBouncer with Azure DB for PostgreSQL, you can click on Deploy button below. It will take you to the template and once you provide the parameter values, it will provision an Ubuntu VM with PgBouncer installed, setup and running connected to Azure Database for PostgreSQL. I would encourage you to give it a try. The template is available in our GitHub repository
Once you have deployed the ARM Template **successfully without any errors**, you will be able to see the hostname, sshCommand and psqlCommand as part of Deployment Outputs
hostname is the Public DNS for the Ubuntu VM hosting pgbouncer
sshCommand provides the ssh command to connect to the Ubuntu VM hosting pgbouncer
psqlCommand provides the psql command to connect the PostgreSQL Server
psql "host={dnsLabelPrefix}.{region}.cloudapp.azure.com port=5432 dbname={your_database} user={your_username}"
For Example : psql "host=pgubuntu.westus.cloudapp.azure.com port=5432 dbname=postgres user=pguser@pgbouncerserver"
Hope this helps !!!
Ramkumar Chandrasekaran
Senior Software Engineer, Microsoft
Parikshit Savjani
Senior Program Manager, Microsoft
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.