2026-09-01 · 11 min
PostgreSQL Connection Pooling with PgBouncer in Production: What Actually Matters
Your PostgreSQL instance is not the bottleneck. Your connections are.
This is the lesson I had to learn the hard way while running a fintech backend for a remittance service operating across Tangerang, Bekasi, and Bogor. During Lebaran 2024, transaction volume spiked 4x within two hours. The PostgreSQL server — 32 cores, 128GB RAM — was sitting at 20% CPU. But pg_stat_activity showed 497 connections, all waiting on locks or in idle state, and our Node.js app was throwing connection timeout after 5000ms errors for roughly 8% of requests. The query performance was fine. The connection management was not.
PgBouncer fixed it. Not because it made queries faster, but because it made the connection layer not be the ceiling.
Why PostgreSQL connections are expensive
Each PostgreSQL connection is a separate OS process — not a thread, a process. On Linux, ps aux | grep postgres will show you one postgres: client backend process per active connection. Forking a process carries memory overhead (typically 5–10MB per connection depending on work_mem and shared_buffers configuration), and at 200+ simultaneous connections you start paying real CPU cost just from process scheduling.
The practical implication: max_connections = 1000 on PostgreSQL is not a target, it’s a hard ceiling that you never want to approach. Most well-tuned PostgreSQL instances run with max_connections between 100 and 400, and they run better for it.
Connection pooling between your application and PostgreSQL exists to let you have many more application-level connections than backend connections. PgBouncer is a lightweight proxy that sits between your app and PostgreSQL, maintaining a pool of actual database connections and multiplexing client connections onto them.
PgBouncer installation and basic config
On Ubuntu/Debian:
apt install pgbouncer
On Amazon Linux / RHEL:
yum install pgbouncer
The main config lives at /etc/pgbouncer/pgbouncer.ini. Here is a production-realistic starting point:
[databases]
# Format: local_db_alias = host=... port=... dbname=...
myapp = host=127.0.0.1 port=5432 dbname=myapp_production
[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
# Transaction mode: the only mode worth using for typical web apps
pool_mode = transaction
# Max connections PgBouncer will open to PostgreSQL
# Start conservative — tune up based on pg_stat_activity observation
default_pool_size = 50
# How many connections can wait in queue before being rejected
max_client_conn = 1000
# Reserve some connections for admin/monitoring tools
reserve_pool_size = 5
reserve_pool_timeout = 3
# Kill idle server connections after 10 minutes
server_idle_timeout = 600
# Kill idle client connections after 5 minutes (prevents ghost connections)
client_idle_timeout = 300
# Log destination
logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid
# Admin interface — accessible via psql -h 127.0.0.1 -p 6432 -U pgbouncer pgbouncer
admin_users = pgbouncer
stats_users = monitoring_user
# Important for transaction mode compatibility
ignore_startup_parameters = extra_float_digits,application_name
The userlist.txt file stores credentials in md5 or scram format:
# Generate scram-sha-256 hash for a user
psql -c "SELECT concat('\"', usename, '\" \"', passwd, '\"') FROM pg_shadow WHERE usename = 'myapp_user';"
Paste the output line into /etc/pgbouncer/userlist.txt.
Transaction mode: what breaks and how to fix it
Transaction mode is the reason PgBouncer works, and also the source of every compatibility issue. When a client’s transaction ends, PgBouncer can reassign that server connection to a different client. Features that are stateful at the session level stop working correctly.
Prepared statements. In PostgreSQL, PREPARE creates a server-side statement tied to the current connection. In transaction mode, the next transaction from the same application client might land on a different server connection — the prepared statement is gone.
Fix for node-postgres (pg):
// Instead of named queries, use simple query protocol
const { Client } = require('pg');
const client = new Client({
connectionString: 'postgresql://user:pass@pgbouncer-host:6432/myapp',
// Disable prepared statement caching
query_timeout: 30000,
});
// When using pg Pool, disable statement caching explicitly
const pool = new Pool({
connectionString: 'postgresql://user:pass@pgbouncer-host:6432/myapp',
// Avoid reusing prepared statement names across pool connections
allowExitOnIdle: true,
});
Fix for pgx in Go:
config, err := pgxpool.ParseConfig(os.Getenv("DATABASE_URL"))
if err != nil {
log.Fatal(err)
}
// Use simple query protocol — no prepared statements
config.ConnConfig.DefaultQueryExecMode = pgx.QueryExecModeSimpleProtocol
pool, err := pgxpool.NewWithConfig(ctx, config)
For Prisma, add ?pgbouncer=true to your DATABASE_URL:
DATABASE_URL="postgresql://user:pass@pgbouncer-host:6432/myapp?pgbouncer=true&connection_limit=1"
The pgbouncer=true flag tells Prisma to disable prepared statement caching and use simple protocol. connection_limit=1 is important for serverless functions — each function instance should open at most one connection to PgBouncer, relying on PgBouncer to do the actual multiplexing.
Advisory locks. pg_advisory_lock() is session-scoped. In transaction mode, the lock is released when the transaction ends and the server connection is returned to the pool. If your code relies on advisory locks persisting across transactions, you need to either switch to a different locking mechanism or run those specific operations through a dedicated connection that bypasses PgBouncer.
SET commands and session variables. SET search_path = ... or SET LOCAL applies to the session. In transaction mode, this gets reset when the connection is returned to the pool. Use server_reset_query in pgbouncer.ini to define what gets run when a connection is returned:
server_reset_query = DISCARD ALL
DISCARD ALL resets search_path, temp tables, prepared statements, and session-level settings. It has a small cost — if you’re returning thousands of connections per second, profile whether this matters.
Sizing the pool correctly
The most common mistake: setting default_pool_size to the same number as your old max_connections. That defeats the purpose.
The formula I use:
pool_size_per_db = floor((max_connections - superuser_reserve) / num_pgbouncer_instances)
Example: PostgreSQL max_connections = 200, 10 reserved for superuser and monitoring, 2 PgBouncer instances:
pool_size = floor((200 - 10) / 2) = 95
Start there, then observe actual server_active count in PgBouncer stats:
-- Connect to PgBouncer admin interface
psql -h 127.0.0.1 -p 6432 -U pgbouncer pgbouncer
-- Show pool stats
SHOW POOLS;
-- Watch in real time
SHOW STATS_TOTALS;
If server_active never exceeds 40 out of your pool_size of 95, you’re holding 55 idle connections on PostgreSQL for no reason. Reduce default_pool_size to 50 and add server_idle_timeout = 300 to release them faster during quiet periods.
The real ceiling is not max_connections — it is PostgreSQL’s CPU. Beyond roughly (2 × vCPUs) actively executing queries, you get contention, not parallelism. On an 8-core instance, 16 concurrent executing queries is often your practical limit. Everything above that queues.
Monitoring PgBouncer in production
PgBouncer exposes stats through its admin interface. Pull them into your metrics stack:
# One-liner to dump key metrics
psql -h 127.0.0.1 -p 6432 -U monitoring_user pgbouncer -c "SHOW STATS;" -t -A -F","
The three numbers to watch:
cl_waiting— clients waiting for a server connection. If this is consistently > 0 during normal load, your pool is undersized or your queries are too slow.sv_idle— idle server connections. High numbers here mean your pool is oversized or your traffic pattern is spiky. Tuneserver_idle_timeoutdown.avg_wait_time— average time a client waited for a connection. Anything above 50ms is a problem. Above 200ms, you’re likely seeing application-level timeouts.
For Prometheus, use pgbouncer_exporter:
docker run -d \
--name pgbouncer-exporter \
-e DATA_SOURCE_URI="pgbouncer-host:6432/pgbouncer?sslmode=disable" \
-e DATA_SOURCE_USER=monitoring_user \
-e DATA_SOURCE_PASS=secret \
-p 9127:9127 \
prometheuscommunity/pgbouncer-exporter
Add a Grafana alert on pgbouncer_pools_client_waiting_connections > 5 with a 2-minute window. That will catch pool exhaustion before your application’s error rate climbs.
The mistake everyone makes: connection_limit per app instance
If you have 20 Node.js application instances, each creating a pg Pool with max: 20, you have 400 potential connections hammering PgBouncer. PgBouncer then multiplexes those 400 down to pool_size connections to PostgreSQL — that’s the right behavior.
But here’s where teams get confused: they see 400 connections to PgBouncer and panic. They raise max_client_conn to 2000, raise pool_size to match, and end up with no meaningful pooling. The whole point is that 400 client connections map to 50 server connections. That ratio — 8:1 in this case — is your actual gain.
For typical transactional OLTP workloads, a ratio of 5:1 to 20:1 is healthy. If your avg_wait_time is low and cl_waiting is near zero at that ratio, you’re configured correctly. Don’t chase a bigger ratio for its own sake.
Practical advice based on what I’ve seen break
Use PgBouncer’s track_extra_parameters if you’re on PgBouncer 1.22+. It allows clients to set a limited set of session parameters that PgBouncer will replay when a server connection is assigned. This solves the SET search_path problem for most frameworks without needing DISCARD ALL.
Never put PgBouncer behind a load balancer that does connection persistence without understanding how PgBouncer handles it. HAProxy in TCP mode works fine. Nginx stream proxy works fine. AWS NLB with a 350-second idle timeout and no keep-alive tuning has caused mysterious disconnections that took two days to diagnose — the NLB was silently killing connections that PgBouncer thought were still alive.
Test your connection pooling under load before relying on it. Use pgbench against PgBouncer, not directly against PostgreSQL:
pgbench -h pgbouncer-host -p 6432 -U myapp_user \
-c 200 -j 8 -T 60 myapp
Then compare with direct PostgreSQL at the same concurrency. The difference in TPS at high client counts is your pooling gain. If you see no difference, your bottleneck is somewhere else — likely query performance or lock contention — and PgBouncer alone won’t save you.
Where PgBouncer does not help
Connection pooling is not a substitute for query optimization. If your p99 query time is 3 seconds, 200 clients at 3 seconds each means 200 connections occupied for 3 seconds at a time. Pool size 50 means 150 clients are waiting. That is a query performance problem.
PgBouncer also does not solve read scaling. If your PostgreSQL primary is saturated with reads, add read replicas and route read queries there. PgBouncer can point at a replica via the [databases] section, but you handle the routing logic in your application.
What PgBouncer genuinely solves: the connection overhead problem that appears when your application layer scales horizontally faster than your database tier. Twenty app servers, each with a connection pool, connecting to one PostgreSQL instance — that is precisely the problem PgBouncer was built for.
After the Lebaran incident, we settled on: PgBouncer running on two dedicated t3.small instances behind an internal NLB, pool_size = 60, max_client_conn = 800, pool_mode = transaction. PostgreSQL max_connections = 150. Application connection pool per instance capped at 15. The math worked out, cl_waiting stayed at zero through the next peak, and we stopped thinking about connections entirely. That is the outcome you want — the connection layer should be invisible.
Ditulis oleh Reza Pradipta