Deploy PgBouncer in front of PostgreSQL, choose between transaction and session pool modes, calculate optimal pool sizes using a proven formula, configure monitoring with Prometheus, and diagnose common connection pooling issues. This guide turns database connection management from a mystery into a science.
PostgreSQL creates a new operating system process for every client connection. Each process consumes 5-10MB of RAM and adds context-switching overhead. Without connection pooling, a 1000-connection application consumes 5-10GB of RAM just for connection overhead, and PostgreSQL performance degrades significantly above 200-300 active connections. According to the 2024 Percona Performance Report, connection pooling improves PostgreSQL throughput by 2-5x for web application workloads. PgBouncer is the most widely deployed connection pooler, used by companies like GitLab, Notion, and Supabase.
---
1. Why Connection Pooling Matters
The connection problem becomes acute as applications scale. A typical web application with 50 application server processes, each maintaining 20 database connections, creates 1000 PostgreSQL backend processes. Most of these connections are idle at any given time, waiting for the next query.
Connection overhead comparison
| Metric | Without Pooling | With PgBouncer |
| App connections | 1000 | 1000 (to PgBouncer) |
| PostgreSQL backends | 1000 | 50-100 |
| Memory for connections | 5-10 GB | 250-500 MB |
| Connection setup time | 5-20ms per connection | <1ms (reused) |
| Max throughput (TPS) | 5,000-10,000 | 15,000-50,000 |
| Tail latency (p99) | 50-200ms | 5-20ms |
PgBouncer multiplexes many client connections onto a smaller number of PostgreSQL backend connections. Clients connect to PgBouncer (which is lightweight, handling 10,000+ connections with <100MB RAM), and PgBouncer assigns a real PostgreSQL connection only when the client executes a query.
---
2. Install and Configure PgBouncer
Installation
<h1>Ubuntu/Debian</h1>
apt-get install -y pgbouncer
<h1>RHEL/CentOS</h1>
dnf install -y pgbouncer
<h1>Docker</h1>
docker run -d --name pgbouncer \
-e DATABASE_URL="postgresql://user:pass@pg-host:5432/mydb" \
-p 6432:6432 \
bitnami/pgbouncer:latest
PgBouncer configuration (/etc/pgbouncer/pgbouncer.ini)
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb
* = host=127.0.0.1 port=5432
[pgbouncer]
; Connection pool settings
pool_mode = transaction
default_pool_size = 25
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 3
max_client_conn = 1000
max_db_connections = 100
; Timeouts
server_lifetime = 3600
server_idle_timeout = 600
server_connect_timeout = 15
server_login_retry = 15
client_idle_timeout = 0
client_login_timeout = 60
query_timeout = 0
query_wait_timeout = 120
; Logging
log_connections = 1
log_disconnections = 1
log_pooler_errors = 1
stats_period = 60
; Authentication
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
; Network
listen_addr = 0.0.0.0
listen_port = 6432
; Admin access
admin_users = pgbouncer_admin
stats_users = pgbouncer_stats
Authentication file (/etc/pgbouncer/userlist.txt)
; Format: "username" "password-hash"
; Generate hash: psql -c "SELECT concat('\"', usename, '\" \"', passwd, '\"')
; FROM pg_shadow WHERE usename = 'myuser';"
"myuser" "SCRAM-SHA-256$4096:salthere$storedkey:serverkey"
"pgbouncer_admin" "admin-password-hash"
"pgbouncer_stats" "stats-password-hash"
---
3. Pool Modes Explained
PgBouncer offers three pool modes, each with different trade-offs. Choosing the right mode is the most important configuration decision.
Pool mode comparison
| Feature | Session Mode | Transaction Mode | Statement Mode |
| Connection assigned | For entire session | Per transaction | Per statement |
| Connection reuse | Low | High | Highest |
| Prepared statements | Yes | No (PgBouncer 1.21+ partial) | No |
| SET commands | Yes | No (reset on release) | No |
| LISTEN/NOTIFY | Yes | No | No |
| Temp tables | Yes | No | No |
| Advisory locks | Yes | No | No |
| Best for | Legacy apps, ORMs needing session features | Modern web apps, APIs | Simple read-heavy apps |
Transaction mode (recommended for most applications)
In transaction mode, PgBouncer assigns a PostgreSQL connection to a client only for the duration of a transaction. Between transactions, the connection is returned to the pool and can be used by other clients. This maximizes connection reuse and is the right choice for 90% of web applications.
Limitations to be aware of:
- Prepared statements do not work across transactions (the server connection changes). Use PgBouncer 1.21+ with `max_prepared_statements` or disable prepared statements in your ORM/driver.
- Session-level settings (SET search_path, SET timezone) are lost between transactions. Move these into connection strings or per-query settings.
- LISTEN/NOTIFY does not work because the connection changes. Use a separate, non-pooled connection for pub/sub.
Configuring your ORM for transaction mode
<h1>SQLAlchemy - disable prepared statements</h1>
engine = create_engine(
"postgresql://user:pass@pgbouncer:6432/mydb",
connect_args={"prepare_threshold": None}
)
<h1>Django - disable server-side cursors</h1>
DATABASES = {
"default": {
"ENGINE": "django.db.backends.postgresql",
"HOST": "pgbouncer",
"PORT": "6432",
"OPTIONS": {
"options": "-c statement_timeout=30000"
}
}
}
// Node.js pg driver - disable prepared statements
const pool = new Pool({
host: "pgbouncer",
port: 6432,
statement_timeout: 30000,
// Use simple query protocol (no prepared statements)
// by setting prepareThreshold to 0
});
---
4. Calculate Optimal Pool Size
The optimal pool size is not "as many as possible." Too many connections overwhelm PostgreSQL, while too few cause query queuing. Use this formula, derived from PostgreSQL contributor recommendations and real-world benchmarks.
Pool sizing formula
The optimal number of PostgreSQL backend connections follows this formula:
optimal_connections = (cpu_cores * 2) + effective_spindle_count
Where:
- `cpu_cores` = number of CPU cores on the database server
- `effective_spindle_count` = 1 for SSD (most modern setups)
For a server with 8 CPU cores and SSD storage: (8 * 2) + 1 = 17 backend connections.
Sizing examples
| DB Server Specs | Optimal PG Connections | PgBouncer default_pool_size | max_client_conn |
| 2 vCPU, SSD | 5 | 5 | 200 |
| 4 vCPU, SSD | 9 | 9 | 500 |
| 8 vCPU, SSD | 17 | 17 | 1000 |
| 16 vCPU, SSD | 33 | 33 | 2000 |
| 32 vCPU, SSD | 65 | 50 (cap) | 5000 |
Multi-database configuration
When PgBouncer serves multiple databases, divide the pool across them:
[databases]
app_primary = host=pg-primary port=5432 dbname=app pool_size=15
app_analytics = host=pg-replica port=5432 dbname=app pool_size=8
app_reports = host=pg-replica port=5432 dbname=app pool_size=4
The total across all databases should not exceed your optimal_connections value. On Arxentra's managed PostgreSQL, pool sizes are automatically calculated based on your instance specifications.
---
5. Monitoring PgBouncer
PgBouncer exposes statistics through a virtual database that you query with standard SQL. Connect to PgBouncer's admin console to access them.
Key monitoring queries
-- Connect to PgBouncer admin console
-- psql -h localhost -p 6432 -U pgbouncer_stats pgbouncer
-- Show pool statistics
SHOW POOLS;
-- Show active connections per database
SHOW DATABASES;
-- Show client connection details
SHOW CLIENTS;
-- Show server (PostgreSQL) connection details
SHOW SERVERS;
-- Show aggregate statistics
SHOW STATS;
-- Show memory usage
SHOW MEM;
Critical metrics to monitor
| Metric | Source | Alert Threshold | Meaning |
| cl_active | SHOW POOLS | Above 80% of max_client_conn | Clients actively using a server connection |
| cl_waiting | SHOW POOLS | Above 0 for >30s | Clients waiting for a server connection (pool exhausted) |
| sv_active | SHOW POOLS | Above default_pool_size | All server connections in use |
| sv_idle | SHOW POOLS | 0 for extended periods | No idle connections (potential bottleneck) |
| avg_query_time | SHOW STATS | Above 100ms | Average query duration |
| avg_wait_time | SHOW STATS | Above 10ms | Time clients spend waiting for a connection |
| total_query_count | SHOW STATS | Sudden drops | May indicate connection issues |
Prometheus exporter for PgBouncer
<h1>Install pgbouncer_exporter</h1>
docker run -d --name pgbouncer-exporter \
-e PGBOUNCER_EXPORTER_HOST=pgbouncer \
-e PGBOUNCER_EXPORTER_PORT=6432 \
-e PGBOUNCER_EXPORTER_USER=pgbouncer_stats \
-e PGBOUNCER_EXPORTER_PASS=stats-password \
-p 9127:9127 \
prometheuscommunity/pgbouncer-exporter
Prometheus alerting rules
groups:
- name: pgbouncer
rules:
- alert: PgBouncerPoolExhausted
expr: pgbouncer_pools_client_waiting_connections > 0
for: 1m
labels:
severity: warning
annotations:
summary: "PgBouncer pool exhausted for {{ $labels.database }}"
description: "{{ $value }} clients waiting for connections"
- alert: PgBouncerHighWaitTime
expr: pgbouncer_stats_avg_wait_time_seconds > 0.1
for: 5m
labels:
severity: warning
annotations:
summary: "PgBouncer average wait time > 100ms"
- alert: PgBouncerMaxConnections
expr: pgbouncer_pools_client_active_connections / pgbouncer_config_max_client_conn > 0.8
for: 5m
labels:
severity: critical
annotations:
summary: "PgBouncer at 80% of max client connections"
For full monitoring setup, see our Kubernetes observability stack guide. For PostgreSQL HA with connection pooling, see our PostgreSQL HA guide.
---
6. Troubleshooting Common Issues
Problem: "no more connections allowed" errors
<h1>Check current connection counts</h1>
psql -p 6432 -U pgbouncer_stats pgbouncer -c "SHOW POOLS;"
<h1>If cl_waiting > 0, pool is exhausted</h1>
<h1>Solutions:</h1>
<h1>1. Increase default_pool_size (if PostgreSQL can handle it)</h1>
<h1>2. Increase max_db_connections</h1>
<h1>3. Reduce application connection pool size</h1>
<h1>4. Find and fix long-running queries:</h1>
psql -c "SELECT pid, now() - query_start AS duration, query
FROM pg_stat_activity
WHERE state = 'active' AND query_start < now() - interval '30 seconds'
ORDER BY duration DESC;"
Problem: Prepared statement errors in transaction mode
<h1>Error: "prepared statement X does not exist"</h1>
<h1>Solution: Disable prepared statements in your driver</h1>
<h1>Or upgrade PgBouncer to 1.21+ and set:</h1>
<h1>max_prepared_statements = 100</h1>
Problem: "SET" commands not persisting
-- In transaction mode, SET is lost between transactions
-- Solution 1: Use connection-level defaults in PgBouncer
-- pgbouncer.ini:
-- mydb = host=pg port=5432 dbname=mydb connect_query='SET timezone TO "UTC"'
-- Solution 2: Set per-query
SELECT * FROM events
WHERE created_at AT TIME ZONE 'UTC' > now() - interval '1 hour';
Troubleshooting decision tree
| Symptom | Likely Cause | Fix |
| cl_waiting > 0 | Pool too small or slow queries | Increase pool or optimize queries |
| sv_active = pool_size always | All server connections busy | Optimize queries or increase pool |
| High avg_wait_time | Pool exhausted | Increase pool, reduce app connections |
| Connection refused | max_client_conn reached | Increase max_client_conn |
| Prepared statement errors | Transaction mode incompatibility | Disable prepared statements |
| Random disconnections | server_lifetime too low | Increase server_lifetime |
---
PgBouncer on Kubernetes
For Kubernetes deployments, run PgBouncer as a sidecar container or as a separate deployment. CNPG includes an optional built-in PgBouncer integration.
apiVersion: postgresql.cnpg.io/v1
kind: Pooler
metadata:
name: app-db-pooler
namespace: production
spec:
cluster:
name: app-db
instances: 2
type: rw
pgbouncer:
poolMode: transaction
parameters:
default_pool_size: "25"
max_client_conn: "1000"
query_wait_timeout: "120"
---
Frequently Asked Questions
Should I use PgBouncer or pgcat?
PgBouncer for most workloads. It is battle-tested, lightweight, and widely supported. pgcat (by Instacart) is newer, written in Rust, and supports features PgBouncer does not (query load balancing, sharding, prepared statements in transaction mode). If you need those features, evaluate pgcat. Otherwise, PgBouncer is the safer choice.
Can I use PgBouncer with connection pooling in my ORM?
Yes, but configure them correctly. Your ORM connection pool should be smaller than PgBouncer's max_client_conn. Set the ORM pool to 5-20 connections per application instance, and let PgBouncer handle the multiplexing to PostgreSQL. Having two layers of pooling is fine as long as you do not over-allocate at either layer.
How do I handle migrations with PgBouncer?
Run migrations directly against PostgreSQL (port 5432), not through PgBouncer (port 6432). Migrations often use DDL statements, advisory locks, and long-running transactions that are incompatible with transaction pool mode. After the migration completes, restart PgBouncer to ensure clean connection state.
What is the memory overhead of PgBouncer?
PgBouncer is extremely lightweight. It uses about 2KB per client connection and 8KB per server connection. A PgBouncer instance handling 5,000 client connections and 100 server connections uses approximately 11MB of RAM. This is why PgBouncer can handle 10,000+ connections on a single CPU core.
How do I monitor PgBouncer performance in production?
Use the pgbouncer_exporter for Prometheus (described in Section 5) and create a Grafana dashboard. The key metrics to track are: cl_waiting (should always be 0), avg_wait_time (should be under 10ms), and the ratio of sv_active to default_pool_size. The Arxentra monitoring documentation includes pre-built PgBouncer dashboards.
Is connection pooling needed with managed databases like RDS?
Yes. Managed databases still use the same PostgreSQL process-per-connection model. RDS Proxy provides built-in pooling, but PgBouncer gives you more control and typically better performance. If you are on RDS and do not want to manage PgBouncer, RDS Proxy is a reasonable alternative. For Arxentra-managed PostgreSQL, connection pooling is included and pre-configured.