How to Configure PostgreSQL Connection Pooling with PgBouncer

How to Configure PostgreSQL Connection Pooling with PgBouncer

PgBouncer is a lightweight connection pooler for PostgreSQL that sits between your application and the database server. It dramatically reduces the overhead of creating new connections, enabling your Breeze instance to handle thousands of concurrent database clients with minimal resource usage.

Why Connection Pooling Matters

Each PostgreSQL connection consumes roughly 5-10 MB of RAM. Without pooling, a busy application can exhaust server memory by opening hundreds of connections simultaneously. PgBouncer maintains a pool of reusable connections, keeping PostgreSQL resource usage predictable and stable on your Breeze instance.

Installing PgBouncer

On Ubuntu or Debian-based Breeze instances:

sudo apt update
sudo apt install -y pgbouncer

Configuring PgBouncer

Edit the main configuration file at /etc/pgbouncer/pgbouncer.ini:

[databases]
myapp = host=127.0.0.1 port=5432 dbname=myapp

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

pool_mode = transaction
default_pool_size = 20
max_client_conn = 200
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 3

log_connections = 1
log_disconnections = 1
stats_period = 60

Pool Modes Explained

• Session mode — a server connection is assigned to a client for the entire session. Safest but least efficient.
• Transaction mode — a server connection is returned to the pool after each transaction completes. Best balance of safety and performance for most applications.
• Statement mode — a server connection is returned after each statement. Most aggressive but does not support multi-statement transactions.

Setting Up Authentication

Create the user list file at /etc/pgbouncer/userlist.txt:

"appuser" "md5passwordhash"

Generate the MD5 hash from PostgreSQL:

SELECT 'md5' || md5('YourPassword' || 'appuser') AS hash;

Alternatively, use auth_query to authenticate directly against PostgreSQL:

auth_type = md5
auth_query = SELECT usename, passwd FROM pg_shadow WHERE usename=$1

Starting and Testing

Start PgBouncer and connect through the pooler:

sudo systemctl enable pgbouncer
sudo systemctl start pgbouncer

psql -h 127.0.0.1 -p 6432 -U appuser myapp

Monitoring the Pool

Connect to the PgBouncer admin console:

psql -h 127.0.0.1 -p 6432 -U pgbouncer pgbouncer

SHOW POOLS;
SHOW STATS;
SHOW CLIENTS;
SHOW SERVERS;

The SHOW POOLS command displays active connections, waiting clients, and pool utilization. Monitor the cl_waiting column; if it consistently shows waiting clients, increase default_pool_size to accommodate demand on your Breeze instance.

Performance Tuning Tips

• Set default_pool_size to match your PostgreSQL max_connections divided by the number of databases.
• Use server_idle_timeout to close unused server connections and free resources.
• Enable server_check_query = SELECT 1 to verify connections are alive before handing them to clients.
• Set client_idle_timeout to prevent idle clients from consuming pool slots.