PgBouncer is an open-source, lightweight, single-binary connection pooler for PostgreSQL. It can pool connections to one or more databases (on possibly different servers) and serve clients over TCP and Unix domain sockets. The primary benefit of PgBouncer is to improve idle connections and short-lived connections at the database server.
When enabled, PgBouncer runs on port 6432 on your database server. You can change your application’s database connection configuration to use the same host name, but change the port to 6432 to start using PgBouncer.
Azure Database for PostgreSQL – Flexible Server offers PgBouncer as a built-in connection pooling solution. This is an optional service that can be enabled on a per-database server basis. PgBouncer runs in the same virtual machine as the Postgres database server.
PgBouncer also provides an internal database that you can connect to called pgbouncer
. Once connected to the database you can execute SHOW commands that provide information on the current state of pgbouncer.
SHOW HELP
- list all the available show commandsSHOW POOLS
— show number of connections in each state for each poolSHOW DATABASES
- show current applied connection limits for each databaseSHOW STATS
- show stats on requests and traffic for every database
Checking PgBouncer performance impact using a simple script
$ cat test.sql
SELECT 1;
$ 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)
$ 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
have increased by 50%, a nice gain without tweaking anything in the app or spending on any extra resources. In addition, the average connection time
have reduced by 81 milliseconds, a clear reduction in connection overhead.
- PgBouncer synopsis - https://www.pgbouncer.org/usage.html#synopsis
- PgBouncer in Azure Database for PostgreSQL - Flexible Server - https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/concepts-pgbouncer
- Azure Database for PostgreSQL – Flexible Server with managed PgBouncer in preview - https://azure.microsoft.com/en-us/updates/azure-database-for-postgresql-flexible-server-with-managed-pgbouncer-in-preview/
- Repo - https://github.com/pgbouncer/pgbouncer