Connection Pooling with PgBouncer in PostgreSQL

PostgreSQL connections are expensive. Each one is a separate operating-system process with its own memory, and Postgres runs well with a few hundred of them, not thousands. Modern application stacks, especially serverless and containerized ones that scale horizontally, can easily try to open thousands of connections, and Postgres falls over long before it runs out of CPU or disk. Connection pooling sits between your app and the database and fixes this, and PgBouncer is the standard tool for it. This is the connection side of scaling, and it's a limit teams hit surprisingly early.

This is part 33 of the PostgreSQL Masterclass, following table partitioning.

Why connections are the bottleneck

Every Postgres connection forks a backend process using several megabytes of memory even when idle. Set max_connections to 5,000 and you've committed gigabytes of RAM to connection overhead and put the scheduler under load, and throughput actually drops. The database performs best with a connection count roughly proportional to your CPU cores, often in the low hundreds, with queries flowing through them quickly.

The problem is that application instances want their own connections. Ten app servers with a pool of 20 each is 200 connections, which is fine. But autoscaling to 100 instances, or serverless functions that each open a connection, quickly blows past what Postgres handles well. You need something that lets many clients share a small set of real database connections.

What a pooler does

A connection pooler maintains a set of established connections to Postgres and hands them out to clients as needed. Clients connect to the pooler, which is cheap, and the pooler multiplexes their work over its small pool of real, expensive database connections. Thousands of clients, a few dozen actual Postgres backends.

PgBouncer is the lightweight, widely-used pooler for this. It's a small, single-purpose process that clients talk to exactly as if it were Postgres, while it manages the real connections behind it.

The three pooling modes

PgBouncer has three modes, and choosing correctly is the one decision that matters:

Session pooling assigns a real connection to a client for the whole time the client is connected. It's the safest (everything works exactly as a direct connection) but the least efficient, since an idle client still holds a backend. It barely helps with the thousands-of-clients problem.

Transaction pooling assigns a real connection only for the duration of a transaction, then returns it to the pool. This is the mode that solves the scaling problem: between transactions, a client holds no backend, so a few dozen connections serve thousands of clients. It's the default choice for web applications and the reason PgBouncer exists.

Statement pooling returns the connection after every single statement. It's the most aggressive and forbids multi-statement transactions, so it's rarely used outside specific setups.

Most teams run transaction pooling. It gives the big win, with one set of caveats to respect.

Transaction pooling's caveats

Because transaction pooling hands a client a different physical connection per transaction, anything that relies on session state across transactions breaks. The things to watch:

• Session-level features: SET that's meant to persist, session variables, LISTEN/NOTIFY, and advisory session locks don't work reliably, because the next transaction may land on a different backend.
• Prepared statements: server-side prepared statements tied to a session can break. Modern PgBouncer supports prepared statements in transaction mode with configuration, and many drivers handle it, but it's a known sharp edge.
• Search path and temp tables: anything set once and expected to persist across transactions won't.

The practical rule: in transaction pooling, keep everything you need within a single transaction, and don't rely on session state carrying over. Most web request handlers already work this way, which is why the mode fits them.

A minimal configuration

PgBouncer is configured with a simple ini file pointing at your database and setting the mode and pool size:

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

[pgbouncer]
listen_port = 6432
auth_type = scram-sha-256
pool_mode = transaction
max_client_conn = 5000     ; clients can connect
default_pool_size = 25     ; real connections per user/db

Your application connects to PgBouncer's port (6432) instead of Postgres directly. max_client_conn is how many clients can connect to PgBouncer; default_pool_size is how many real Postgres connections it opens per database. The gap between those two numbers is the whole point: 5,000 clients, 25 backends.

Sizing and where it runs

Pool size wants to be small: enough real connections to keep your CPUs busy, not more. A common starting point is a few times your core count, tuned by watching whether clients wait for a connection. Too large and you're back to the original problem; too small and clients queue.

Managed Postgres services often provide a built-in pooler (AWS RDS Proxy, or PgBouncer bundled in), so you may not run it yourself. Whether managed or self-hosted, the concepts are identical: transaction pooling, a small real-connection pool, and awareness of the session-state caveats.

Connection pooling is not optional at scale, it's the thing standing between your autoscaling app and a database that tips over at a few hundred connections. Put PgBouncer (or a managed equivalent) in transaction mode between your app and Postgres, keep the pool small, and respect that session state doesn't cross transactions. Do that and one modest database serves thousands of clients comfortably.

Next, we scale reads specifically with streaming replication and read replicas, spreading query load across multiple copies of the database.