PostgreSQL Connection Pooling with PgBouncer: A Complete Guide

You launch your app. Traffic is light, everything works. A few weeks later you start seeing FATAL: remaining connection slots are reserved for non-replication superuser connections. Your PostgreSQL server is out of connections and your app is falling over.

This is one of the most common PostgreSQL scaling problems, and connection pooling is the fix. But the fix has its own complexity: PgBouncer has three modes with different tradeoffs, the configuration is full of footguns, and if you get it wrong you get subtle bugs that are much harder to debug than the original connection error.

This guide covers how PostgreSQL connections actually work, how to set up and configure PgBouncer correctly, and how to choose the right pool mode for your application.

Why PostgreSQL Connections Are Expensive

PostgreSQL handles each connection with a dedicated server process. When a client connects, Postgres forks a new OS process. That process:

• Allocates its own memory (typically 5-10 MB per connection including shared memory overhead)
• Maintains its own backend state, transaction state, and lock tables
• Requires the kernel to schedule it like any other process

At 50 connections, this is fine. At 500 connections, you have 500 OS processes and the scheduler starts showing up in your performance profiles. At 1,000 connections, you are likely hitting the max_connections limit (default 100 in stock PostgreSQL) and your app is returning errors.

The naive fix is to increase max_connections. Don't do that without thinking it through. Each connection costs memory. Set max_connections = 1000 on a server with 8 GB of RAM and you've allocated the entire heap to idle connections before a single query runs. The shared_buffers and work_mem math goes sideways fast.

The right fix is to reduce the number of actual connections to PostgreSQL. That's what connection poolers do.

What PgBouncer Does

PgBouncer sits between your application and PostgreSQL. Your app thinks it's talking to Postgres, but it's actually talking to PgBouncer. PgBouncer maintains a pool of real connections to Postgres and hands them out to client requests.

The numbers look like this in practice:

• Before PgBouncer: 300 app threads, 300 Postgres connections
• After PgBouncer (transaction mode): 300 app threads, 20 actual Postgres connections

Those 20 connections serve 300 clients because most clients are not actually executing SQL at any given moment. They're waiting for network I/O, processing results, or sitting idle. Transaction mode takes advantage of this by returning a connection to the pool the moment a transaction commits.

Installing PgBouncer

On Ubuntu/Debian:

sudo apt-get install pgbouncer

On macOS with Homebrew:

brew install pgbouncer

Configuring PgBouncer

A minimal working config:

[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25
min_pool_size = 5
reserve_pool_size = 5
server_idle_timeout = 600

For production, use hashed passwords. Generate them with:

SELECT concat('"', rolname, '" "', rolpassword, '"')
FROM pg_authid
WHERE rolname = 'myuser';

Your app connects to port 6432 instead of 5432. Nothing else changes in the app code.

The Three Pool Modes

Session Mode

A server connection is assigned when the client connects and held until the client disconnects. This is the safest mode, behaves identically to a direct PostgreSQL connection. Prepared statements, advisory locks, LISTEN/NOTIFY all work correctly.

Session mode does not help much with connection counts at steady state. Use it when you need full compatibility and your problem is peak load, not constant high concurrency.

Transaction Mode

A server connection is assigned for the duration of a transaction and returned to the pool immediately after. This gives you the dramatic reduction in server connections.

The tradeoff: session-level state does not persist across transactions. This breaks:

• PREPARE and server-side prepared statement caching
• SET commands that are not wrapped in a transaction
• Advisory locks (session-scoped)
• LISTEN and NOTIFY subscriptions
• Temp tables that are supposed to persist across transactions

Transaction mode works well for stateless web applications using pg (Node.js), psycopg2/psycopg3 (Python), or JDBC (Java), as long as those frameworks don't use session-level features.

Statement Mode

A connection is held only for a single SQL statement, then returned. Breaks multi-statement transactions entirely. Rarely the right choice for web applications.

Choosing Your Mode

Your app	Recommended mode
Stateless API using ORM (Django, Rails, Prisma)	Transaction
Long-lived connections with prepared statements	Session
Connection count issues at peak only	Session
Connection count issues at steady state	Transaction
Serverless functions	Transaction
Application using LISTEN/NOTIFY	Session

Pool Sizing

A reasonable starting formula:

default_pool_size = (number of PostgreSQL CPU cores) * 2 + number of disks

In practice, 20-30 works well for most web applications. Check pool utilization while running:

SHOW POOLS;

cl_waiting > 0 sustained means the pool is undersized. sv_idle consistently high means it's oversized.

Monitoring PgBouncer

Connect to the admin console:

psql -h 127.0.0.1 -p 6432 -U pgbouncer pgbouncer

Key commands: SHOW POOLS, SHOW STATS, SHOW CLIENTS, SHOW SERVERS, RELOAD.

SHOW STATS tells you avg_query_time and avg_wait_time. If either is climbing, something is backing up.

Common Pitfalls

Prepared statements in transaction mode: If your app uses server-side prepared statements, transaction mode will break it. Disable them in your driver: prepare_threshold=None in psycopg2, { prepare: false } in pg (Node.js).

Connection storms at startup: Set reserve_pool_size = 5 and max_client_conn to at least 2-3x your expected peak.

SSL: Always use SSL for both client-to-PgBouncer and PgBouncer-to-PostgreSQL in production.

When to Skip PgBouncer

You probably don't need it if:

• Fewer than 50 concurrent connections at peak
• Using a modern ORM with client-side pooling already (Prisma, Django, Rails)
• Serverless workloads with 1-2 connections per function instance

You need it if multiple processes or pods each maintain their own pool and the total exceeds max_connections.

Bottom Line

PostgreSQL connection pooling is not optional at scale. PgBouncer in transaction mode is the right default for most web applications.

The main things to get right: pool size (start at 20-30 for OLTP), mode (transaction for stateless apps, session for anything using prepared statements or advisory locks), monitoring (cl_waiting and avg_wait_time), and SSL in production.

Connection pooling is unglamorous infrastructure, but it's the difference between an app that falls over at traffic spikes and one that just handles them.

---

Originally published at rivestack.io