Introduction#
PostgreSQL creates a process per connection. Each process uses ~5-10MB of memory and requires a full fork on connection establishment. Applications that open many short-lived connections — especially stateless APIs — can overwhelm the database. PgBouncer is a lightweight connection pool that multiplexes thousands of application connections over a small number of actual server connections.
Connection Overhead Without Pooling#
1
2
3
4
5
6
7
8
9
10
11
| # Each PostgreSQL backend process
ps aux | grep postgres
# postgres: app_user mydb 10.0.0.5 idle ← one process per connection
# Memory per connection
# ~5-10MB RSS per backend process
# 500 connections = 2.5-5GB overhead
# Connection establishment time (costly without pooling)
# 3-way TCP handshake + TLS + authentication + session setup ≈ 5-20ms
# At 100 req/s with new connection per request = ~1-2 second of connection overhead per second
|
PgBouncer Pooling Modes#
Transaction pooling: connection returned to pool after each transaction. Most efficient — one server connection can serve many clients. Requires applications to not use session-level features.
Session pooling: connection held for the life of the client session. Less efficient than transaction pooling, but safe for all applications.
Statement pooling: connection returned after each statement. Rarely used — incompatible with multi-statement transactions.
Configuration#
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
| # /etc/pgbouncer/pgbouncer.ini
[databases]
# Format: client_db = host=server_host port=5432 dbname=actual_db
mydb = host=postgres.prod.internal port=5432 dbname=mydb
[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 5432
# Authentication
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
# Or use auth_query to query pg_shadow dynamically:
# auth_query = SELECT usename, passwd FROM pg_shadow WHERE usename=$1
# Pooling mode
pool_mode = transaction
# Server connections per user/database pair
default_pool_size = 25
max_client_conn = 1000 # max application connections
reserve_pool_size = 5 # extra connections for emergencies
reserve_pool_timeout = 5
# Server connection health
server_idle_timeout = 600 # close idle server connections after 10min
server_lifetime = 3600 # recycle server connections hourly
server_reset_query = DISCARD ALL # reset session state between uses
# Client timeouts
client_idle_timeout = 0 # 0 = no timeout
query_timeout = 0 # 0 = no timeout (set in app or PostgreSQL)
query_wait_timeout = 120 # client waits max 2min for a server connection
# Logging
log_connections = 0
log_disconnections = 0
log_pooler_errors = 1
admin_users = pgbouncer_admin
|
1
2
3
| # /etc/pgbouncer/userlist.txt
"app_user" "scram-sha-256$..."
"pgbouncer_admin" "md5abc123..."
|
Sizing the Pool#
The ideal pool size depends on the number of available CPU cores on the PostgreSQL server.
1
2
3
| Recommended: pool_size = num_cores * 2 + num_drives
# For a 4-core server with NVMe SSD:
# pool_size ≈ 4 * 2 + 1 = 9 to 25
|
More connections than cores means the OS is context-switching between PostgreSQL processes, adding overhead without increasing throughput.
1
2
3
4
5
6
7
8
9
10
11
| -- Check current connections in PostgreSQL
SELECT count(*), state
FROM pg_stat_activity
GROUP BY state;
-- Check wait events (high waits = pool too small or slow queries)
SELECT wait_event_type, wait_event, count(*)
FROM pg_stat_activity
WHERE state = 'active'
GROUP BY 1, 2
ORDER BY 3 DESC;
|
Monitoring PgBouncer#
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
| # Connect to PgBouncer admin console
psql -h 127.0.0.1 -p 5432 -U pgbouncer_admin pgbouncer
# Key metrics
SHOW STATS;
SHOW POOLS;
SHOW CLIENTS;
SHOW SERVERS;
# Pool utilization
SELECT database, pool_mode,
cl_active, cl_waiting,
sv_active, sv_idle, sv_used,
maxwait
FROM pgbouncer.pools;
# cl_waiting > 0: clients waiting for a server connection — increase pool_size
# maxwait increasing: pool exhausted — needs investigation
|
Application-Side Configuration#
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
| # SQLAlchemy + PgBouncer: disable prepared statements in transaction mode
# Prepared statements are session-scoped and break transaction pooling
from sqlalchemy import create_engine
engine = create_engine(
"postgresql+psycopg2://app_user:password@pgbouncer:5432/mydb",
pool_size=10, # SQLAlchemy's own pool (client-side)
max_overflow=20,
pool_pre_ping=True, # test connections before use (detects dropped connections)
connect_args={
"options": "-c statement_timeout=30000" # 30s query timeout
},
# Disable prepared statements for PgBouncer transaction mode
execution_options={"prepared": False},
)
|
1
2
3
4
5
6
7
8
9
10
| # asyncpg + PgBouncer: disable prepared statement caching
import asyncpg
async def create_pool():
return await asyncpg.create_pool(
"postgresql://app_user:password@pgbouncer:5432/mydb",
min_size=5,
max_size=20,
statement_cache_size=0, # disable prepared statement cache for PgBouncer
)
|
PgBouncer in Kubernetes#
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
| apiVersion: apps/v1
kind: Deployment
metadata:
name: pgbouncer
namespace: production
spec:
replicas: 2
template:
spec:
containers:
- name: pgbouncer
image: bitnami/pgbouncer:latest
env:
- name: POSTGRESQL_HOST
value: postgres.production.svc.cluster.local
- name: POSTGRESQL_PORT
value: "5432"
- name: PGBOUNCER_DATABASE
value: mydb
- name: PGBOUNCER_POOL_MODE
value: transaction
- name: PGBOUNCER_DEFAULT_POOL_SIZE
value: "25"
- name: PGBOUNCER_MAX_CLIENT_CONN
value: "1000"
ports:
- containerPort: 5432
|
Conclusion#
PgBouncer’s transaction mode is the most efficient: it multiplexes thousands of application connections over tens of server connections. Size the pool to match your PostgreSQL server’s CPU cores, not your application’s concurrency. Disable prepared statements in your application when using transaction mode. Monitor cl_waiting and maxwait to detect pool exhaustion.