PostgreSQL Connection Pooling with PgBouncer – Expert Setup

PostgreSQL Connection Pooling with PgBouncer – Expert Setup

Picture this: your Django/Rails app is growing, and suddenly PostgreSQL starts refusing new connections with FATAL: remaining connection slots are reserved for non-replication superuser connections. The server isn't overloaded, but all 500 slots are taken. Each connection spawns a separate process consuming ~10 MB of memory. 500 connections means 5 GB just for overhead, and most are idle waiting for the next query. PgBouncer solves this radically: it keeps a pool of 20–50 persistent connections to the database and multiplexes thousands of client connections through them. PostgreSQL sees only a dozen active processes, and you get up to 80% memory savings plus stable operation under peak load. Our experience shows that proper pool tuning delivers guaranteed results without downtime. Our engineers will configure PgBouncer specifically for your infrastructure — contact us for a consultation.

Why PgBouncer Outperforms Other Poolers

PgBouncer is a lightweight userspace pooler. It accepts client connections using the PostgreSQL protocol and forwards them through its server connection pool. The key advantage is minimal resource usage: a single PgBouncer process uses ~2 MB of memory and can serve up to 10,000 clients. Compared to Pgpool-II, PgBouncer is 5 times lighter for the same throughput, though Pgpool-II offers more features (replication, load balancing). For pure connection pooling, PgBouncer is the optimal choice. It supports three modes:

"PgBouncer is designed to be as lightweight and efficient as possible for connection multiplexing." — PgBouncer Documentation

For most projects, we recommend transaction mode. It provides the greatest performance gain and resource savings.

Problems PgBouncer Solves

1. Connection exhaustion. With max_connections=500 and 1000 concurrent users (each holding one connection), PostgreSQL refuses new connections. PgBouncer with default_pool_size=25 solves this without increasing the limit.
2. CPU and memory load. Each PostgreSQL backend process requires ~5–10 MB. 500 connections = 5 GB. With PgBouncer, you reduce this to 200–500 MB.
3. Slow startup. Establishing a new connection via PostgreSQL takes 10–30 ms (handshake, authentication). PgBouncer reuses existing connections, eliminating this delay.
4. Prepared statements in transaction mode. If your app uses prepared statements heavily, they don't work by default: after each transaction, the connection returns to the pool and the prepared statement is lost. Solution: enable max_prepared_statements (PgBouncer 1.22+).
5. Authentication overhead. PgBouncer caches authentication, speeding up reconnections.

How to Choose PgBouncer Mode

The table above gives a primary classification. In practice, nuances matter:

• Transaction mode — best for REST APIs, microservices, Django/Rails/Spring apps. It provides maximum multiplexing: one server session can handle dozens of client transactions sequentially. However, it does not support SET (e.g., SET search_path) or LISTEN/NOTIFY. If your app uses these commands, use session mode.
• Session mode — used for admin panels, background tasks, report generation. The connection is pinned to the client for the entire session. It doesn't reduce the number of connections, but avoids unnecessary reconnects.
• Statement mode — exotic for simple loads without transactions. Suitable when every query is a separate transaction (e.g., using auto_commit).

Comparison with alternatives: PgBouncer is 5 times lighter than Pgpool-II for the same throughput. For pure connection pooling, PgBouncer is the optimal choice. Typical resource savings can cut server costs by hundreds of about $9–13 in savings per month. Our experience confirms this across hundreds of projects.

Configuring PgBouncer: Configuration and Startup

The main config file is /etc/pgbouncer/pgbouncer.ini. Example for transaction mode:

[databases]
appdb = host=127.0.0.1 port=5432 dbname=appdb
* = host=127.0.0.1 port=5432

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
auth_query = SELECT usename, passwd FROM pg_shadow WHERE usename=$1
auth_user = pgbouncer_auth
pool_mode = transaction
max_client_conn = 2000
default_pool_size = 25
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 3.0
server_idle_timeout = 600
client_idle_timeout = 0
server_connect_timeout = 15
query_timeout = 0
logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid
stats_period = 60

User file /etc/pgbouncer/userlist.txt:

"appuser" "SCRAM-SHA-256$4096:..." "pgbouncer_auth" "md5abc123..." 

Systemd service:

[Unit]
Description=PgBouncer connection pooler
After=network.target postgresql.service

[Service]
Type=notify
User=pgbouncer
ExecStart=/usr/sbin/pgbouncer /etc/pgbouncer/pgbouncer.ini
ExecReload=/bin/kill -HUP $MAINPID
KillSignal=SIGINT
TimeoutSec=30
Restart=on-failure

[Install]
WantedBy=multi-user.target

Pool Size Recommendations

Pool size (default_pool_size) depends on concurrent requests and CPU cores. General rule: pool = 2–4× cores. For a typical server with 8 vCPU, default_pool_size=16–32 is optimal. The reserve pool (reserve_pool_size) engages during spikes. The minimum pool (min_pool_size) keeps hot connections.

Monitoring PgBouncer and Common Issues

Connect to the virtual database pgbouncer and get pool status:

SHOW POOLS; -- cl_active: clients with active server connection -- cl_waiting: waiting clients (if >0 — pool is too small)
SHOW STATS;
SHOW CLIENTS;
SHOW SERVERS;

If cl_waiting is consistently greater than zero, increase default_pool_size or optimize slow queries. For prepared statements in transaction mode, set max_prepared_statements = 100 in config (PgBouncer 1.22+), otherwise you'll get prepared statement "lp1" does not exist.

Turnkey PgBouncer Setup – What's Included

1. Connection scheme audit. We analyze your load profile: number of sessions, open/close frequency, use of prepared statements and SET commands.
2. Mode and pool size selection. Based on audit, we determine optimal parameters.
3. Installation and configuration. Deploy PgBouncer on server or in container (Docker/Kubernetes). Configure authentication.
4. Load balancer integration (HAProxy, Nginx) if needed.
5. Monitoring. Set up Prometheus exporter or alerts based on cl_waiting.
6. Optimization. Post-tuning under real load, stress testing.
7. Documentation and team training.

Timeline and Pricing

Basic PgBouncer setup for a typical app takes 4–8 hours. The full package with HAProxy, monitoring, and tuning takes 1–2 business days. Pricing is determined individually after audit. Our team guarantees stability and performance. Order professional PgBouncer setup — tell us about your project and we'll scope the work. Contact us to discuss your project.