PgBouncer Connection Pooling Guide

Why Connection Pooling Matters

Each PostgreSQL connection consumes ~10 MB of RAM and a dedicated backend process. An application with 500 connections on a server with 16 GB RAM is already consuming 5 GB just for connections — leaving less for shared_buffers, work_mem, and actual query processing.

PgBouncer sits between your application and PostgreSQL, multiplexing hundreds of application connections into a small pool of database connections.

Connection Pooling Modes

PgBouncer Configuration

# /etc/pgbouncer/pgbouncer.ini
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 3

# Timeouts
server_idle_timeout = 300
client_idle_timeout = 600
query_timeout = 60

Sizing Your Pool

A good starting formula:

default_pool_size = (CPU cores × 2) + effective_spindle_count
# For a 4-core server with SSD: 4 × 2 + 1 = 9 connections per database
# Round up to 15-25 for headroom

Monitoring PgBouncer

-- Connect to PgBouncer admin console
psql -h 127.0.0.1 -p 6432 -U pgbouncer pgbouncer

-- Check pool stats
SHOW POOLS;
SHOW STATS;
SHOW CLIENTS;

Common Pitfalls

• Prepared statements — Don't work in transaction mode by default. Use prepared_statements parameter or switch to session mode.
• SET statements — Session-level settings are lost between transactions in transaction mode.
• LISTEN/NOTIFY — Requires session mode to maintain the persistent connection.

Eazy SaaS Tip: Start with transaction mode and a pool size of 25 per database. Monitor SHOW POOLS for cl_waiting — if clients are waiting, increase the pool size gradually.