Leverage built-in PgBouncer in Flexible Server
Published Nov 01 2022 02:30 PM 6,960 Views
Microsoft

pgbouncer-header.png
When it comes to connection pooling in the PostgreSQL, PgBouncer is probably the most popular option. It’s a very simple utility that does exactly one thing – it sits between the database and the clients and speaks the PostgreSQL protocol, emulating a PostgreSQL server. A client connects to PgBouncer with the exact same syntax it would use when connecting directly to PostgreSQL – PgBouncer is essentially invisible.

Azure Database for Postgres has supported PgBouncer integration with Single Server.  While this helped customers manage applications with short duration connections, the overwhelming feedback we received was to integrate PgBouncer with the managed service. Listening from our customers we have now announced “built-in” PgBouncer in Flexible Server. To leverage this built-in connection pooler, all you need to do is enable the PgBouncer from server parameters and update your app connection string to talk to PgBouncer on port 6432, rather than directly to the database server. What’s more, PgBouncer supports both public and private access connections and allows you to use the same benefits as for the rest of the managed service, including regular updates.


Issues with PostgreSQL Connection management

 

In PostgreSQL, establishing a connection is an expensive operation. This is attributed to the fact that each new connection to PostgreSQL requires forking of the OS process and a new memory allocation for the connection. As a result, transactional applications frequently opening and closing the connections at the end of transactions can experience higher connection latency and high CPU utilization resulting in lower database throughput (transactions per second) and overall higher application latency. 

It is therefore recommended to leverage connection pooling when designing applications using Azure Database for PostgreSQL. This significantly reduces the connection latency by reusing existing connections and enables higher database throughput (transactions per second) on the server. With connection pooling, a fixed set of connections are established at the startup time and maintained. This also helps reduce the memory fragmentation on the server that is caused by the dynamic new connections established on the database server.

 

Note: This blog post applies to Azure Database for Postgres Flexible Server.

Steps to enable PgBouncer on Azure Database for PostgreSQL Flexible Server?

  1. Navigate to the “Server Parameters” blade in the Azure portal, and search for “PgBouncer
  2. Change the pgbouncer.enabled setting to “true” for PgBouncer to be enabled. Note: There is no need to restart the server.


For more details on the Azure Postgres PgBouncer configurations, please refer documentation.

 

Benchmarking to find how much time we spend to open the connections with and without PgBouncer.

Here with the help of pgbench (postgres benchmarking tool) we are showing how having using Azure Postgres “built-in” PgBouncer can greatly benefit the transaction throughput. Our benchmarking goal is to have script that does little work as-possible, however opens a connection for each request.

Step 1. Create sample script for benchmarking 

 

 

 

-- Sample SQL script, that will be executed repeatedly using pgbench
$ cat test.sql
SELECT 1;

 

 

 

 

Step 2. Initialize pgbench database

 

 

 

$ pgbench -i --fillfactor=90 --scale=1000

 

 

 

Step 3. Finally, let’s run the pgbench to benchmark following scenarios

-- We are running 20 concurrent clients (or users) and they all execute 100 single transactions.
-- Note that we used the -C switch in pgbench, which makes sure that connections are really closed after every transaction.
-- This means that we really had to open 2000 connections. And could be considered a typical case on a webserver without connection pooling.

SCENARIO A: Without the connection pooling – Port 5432

 

 

 

$ pgbench -c 20 -t 100 -S foo -C -f test.sql -p 5432

pgbench (14.2, server 14.4)
starting vacuum...end.
transaction type: multiple scripts
scaling factor: 1
query mode: simple
number of clients: 20
number of threads: 1
number of transactions per client: 100
number of transactions actually processed: 2000/2000
latency average = 4333.507 ms
average connection time = 230.568 ms
tps = 3.615200 (including reconnection times)

 

 

 

 

SCENARIO B: With the connection pooling – Port 6432

 

 

 

$ pgbench -c 20 -t 100 -S foo -C -f test.sql -p 6432

pgbench (14.2, server 14.4)
starting vacuum...end.
transaction type: multiple scripts
scaling factor: 1
query mode: simple
number of clients: 20
number of threads: 1
number of transactions per client: 100
number of transactions actually processed: 2000/2000
latency average = 3590.498 ms
average connection time = 149.425 ms
tps = 6.570258 (including reconnection times)

 

 

 

 

 

As you can see the transaction throughput (tps) has increased by 100%, a nice performance gain without changing anything in the app or spending on any extra resources. In addition, the average connection time has been reduced by 81 milliseconds, a clear reduction in connection overhead.

Conclusion

In this post, we showed how to boost application performance with “built-in” PgBouncer in Azure Database for PostgreSQL Flexible Server. Transactional workloads having a high number of read queries but are short-lived can benefit from such an architecture. Also, so far, we have seen the basic configuration and performance improvements with the usage of the PgBouncer. By tweaking even more config variables based on our needs in PgBouncer, we can get more performance improvements. 


As always, If you have any feedback for us or questions, drop us an email @AskAzureDBforPostgreSQL

References

 

 

Acknowledgements

Special thanks to for Gennady Kostin for their contributions to this post.

Version history
Last update:
‎Mar 29 2023 09:24 AM
Updated by: