PgBouncer Configuration (Connection Pooling) for Web Applications
PostgreSQL can't efficiently hold thousands of simultaneous connections. Each connection is a separate process with ~5–10 MB memory. 500 connections = up to 5 GB just on overhead. PgBouncer solves this by multiplexing application connections into a small pool of real PostgreSQL connections.
Pooling Modes
Session pooling — PostgreSQL connection is assigned to client for entire session. Minimal SQL restrictions, but minimal savings — if application keeps connections open.
Transaction pooling — PostgreSQL connection returns to pool after each transaction. Maximum efficiency. Restrictions: can't use prepared statements, SET outside transaction, advisory locks, LISTEN/NOTIFY.
Statement pooling — after each query. Almost never needed.
For web applications: transaction pooling.
Installation
# Ubuntu/Debian
apt-get install pgbouncer
# or Docker
docker run -d \
-e DATABASE_URL="postgresql://app:pass@postgres:5432/mydb" \
-e POOL_MODE=transaction \
-e MAX_CLIENT_CONN=1000 \
-e DEFAULT_POOL_SIZE=20 \
-p 5432:5432 \
edoburu/pgbouncer
pgbouncer.ini Configuration
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb user=app password=secret
; or via DSN:
; 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
; Pooling mode
pool_mode = transaction
; Max client connections (app → pgbouncer)
max_client_conn = 1000
; Pool size per database+user (pgbouncer → postgres)
default_pool_size = 20
; Min connections (keep "warm")
min_pool_size = 5
; Reserve connections for superuser
reserve_pool_size = 5
reserve_pool_timeout = 3
; Timeouts
server_connect_timeout = 15
server_login_retry = 15
query_timeout = 0 ; 0 = no limit (limit at PG level)
query_wait_timeout = 120 ; wait for free connection from pool
client_idle_timeout = 0
; Close server connection after N transactions (prevents memory bloat in PG)
server_lifetime = 3600
server_idle_timeout = 600
; Logging
log_connections = 0 ; disable in production, otherwise flooding
log_disconnections = 0
log_pooler_errors = 1
stats_period = 60
; Admin interface
admin_users = pgbouncer_admin
stats_users = pgbouncer_stats
userlist.txt
"app" "SCRAM-SHA-256$4096:...hash..."
"pgbouncer_admin" "md5hashhere"
Get hashes from PostgreSQL:
SELECT usename, passwd FROM pg_shadow WHERE usename = 'app';
Or use auth_query — PgBouncer queries PostgreSQL for password:
; pgbouncer.ini
auth_user = pgbouncer_auth
auth_query = SELECT usename, passwd FROM pg_shadow WHERE usename=$1
-- In PostgreSQL
CREATE ROLE pgbouncer_auth WITH LOGIN PASSWORD 'authpass';
GRANT SELECT ON pg_shadow TO pgbouncer_auth;
Prepared Statements in Transaction Mode
Transaction pooling is incompatible with protocol-level prepared statements. Solutions:
1. Disable in ORM/driver:
# SQLAlchemy
engine = create_engine(dsn, connect_args={"prepare_threshold": None})
# asyncpg
conn = await asyncpg.connect(dsn, statement_cache_size=0)
// GORM + pgx
db, err := gorm.Open(postgres.New(postgres.Config{
DSN: dsn,
PreferSimpleProtocol: true, // disables extended query protocol
}), cfg)
// node-postgres
const pool = new Pool({ max: 10, statement_timeout: 30000 });
// pg doesn't cache prepared statements in Pool by default
2. PgBouncer 1.21+ supports protocol-level prepared statements in transaction mode. Update PgBouncer and don't change application.
Monitoring
PgBouncer provides pseudo-database pgbouncer with commands:
psql -h 127.0.0.1 -p 6432 -U pgbouncer_admin pgbouncer
SHOW POOLS;
-- See: database, user, cl_active, cl_waiting, sv_active, sv_idle, sv_used, maxwait
SHOW CLIENTS;
SHOW SERVERS;
SHOW STATS;
-- total_query_count, total_query_time, avg_query_time, etc.
SHOW CONFIG;
RELOAD; -- reload config without restart
cl_waiting > 0 for long — pool too small, increase default_pool_size.
sv_idle close to default_pool_size — pool excessive, decrease.
Prometheus Metrics
pip install prometheus-pgbouncer-exporter
# docker-compose.yml
pgbouncer-exporter:
image: spreaker/prometheus-pgbouncer-exporter
environment:
PGBOUNCER_HOST: pgbouncer
PGBOUNCER_PORT: 6432
PGBOUNCER_USER: pgbouncer_stats
PGBOUNCER_PASSWORD: statspass
ports:
- "9127:9127"
Key metrics: pgbouncer_pools_cl_waiting, pgbouncer_pools_sv_active, pgbouncer_stats_avg_query_time.
Production Topology
App pods (100 instances)
↓ 5 connections per pod
PgBouncer (2 instances, HAProxy in front)
↓ 20 connections to primary, 10 to replica
PostgreSQL Primary + Replica
500 application connections → 30 real PostgreSQL connections. Memory savings: ~2.3 GB.
Timelines
Installation and configuration of PgBouncer for existing application: half a day–1 day. Includes configuration, driver adaptation (disabling prepared statements), monitoring setup and load testing.