PostgreSQL Connection Pooling with PgBouncer: A Practical Guide

Sooner or later almost every growing PostgreSQL installation meets the same error: FATAL: sorry, too many clients already. The instinctive fix is to raise max_connections. That usually makes things worse. The real fix is PostgreSQL connection pooling, and the most widely used tool for it is PgBouncer — a small, single-purpose proxy that lets thousands of application connections share a few dozen real database connections.

Why PostgreSQL needs a connection pooler

PostgreSQL uses a process-per-connection model: every client connection gets its own backend process on the server. That design is robust, but each process costs memory and CPU scheduling, and opening a new connection — authentication, process start, catalog caches — takes milliseconds, not microseconds. The default max_connections is 100 for a reason.

Modern applications make this worse. Every application instance, worker, cron job and serverless function keeps its own connection pool, so ten containers with a pool of 20 already need 200 connections — most of them idle most of the time. Idle connections are not free: they hold memory, and a large number of backends makes some internal operations, such as taking snapshots, more expensive for everyone.

A pooler breaks the link between "number of clients" and "number of database processes". Applications connect to PgBouncer as if it were PostgreSQL; PgBouncer hands them a real server connection only when they actually need one.

The three pool modes

The PgBouncer documentation describes three modes. Choosing the right one is the most important decision you will make:

• Session pooling. A client keeps the same server connection until it disconnects. Every PostgreSQL feature works, but you only save the cost of opening connections — you do not reduce the number of server connections in use.
• Transaction pooling. A client holds a server connection only for the duration of a transaction; between transactions the connection goes back to the pool. This is where the big gains are: hundreds of clients can share a small pool. The price is that session-level state no longer belongs to one client.
• Statement pooling. Transaction pooling with multi-statement transactions forbidden. Useful only for special cases such as autocommit-only workloads.

For most web applications, transaction pooling is the right default — provided the application is compatible with it.

What breaks in transaction mode

Because consecutive transactions from the same client may run on different server connections, anything that relies on session state is unsafe. According to the PgBouncer feature table, these do not work in transaction mode:

• SET / RESET of session parameters (use SET LOCAL inside a transaction instead).
• LISTEN (sending NOTIFY is fine).
• Session-level advisory locks — use transaction-level pg_advisory_xact_lock.
• SQL-level PREPARE / DEALLOCATE, WITH HOLD cursors and temporary tables that outlive a transaction.

Protocol-level prepared statements — the kind most drivers create automatically — were also a problem for years. Since PgBouncer 1.21, released in October 2023, they are supported in transaction mode when max_prepared_statements is set to a non-zero value. If you run an older PgBouncer, upgrading is often simpler than reconfiguring every driver.

Sizing the pools

The key PgBouncer settings are few:

• max_client_conn — how many client connections PgBouncer accepts. This can be high (thousands); client connections to PgBouncer are cheap.
• default_pool_size — how many server connections per database/user pair. This is the number that actually loads PostgreSQL.
• reserve_pool_size and reserve_pool_timeout — a few extra connections for short bursts.
• max_db_connections — a hard ceiling per database across all users, so one service cannot exhaust the server.
• query_wait_timeout — how long a client may wait for a free connection before getting an error instead of hanging.

A database server can only run as many queries in parallel as it has CPU cores and I/O capacity. A pool of a few times the number of CPU cores is a reasonable starting point for OLTP workloads; beyond that, extra connections mostly add contention. Start small, measure, and grow the pool only while throughput keeps improving. Remember to keep max_connections on PostgreSQL above the total of all pools plus room for administrators, replication and monitoring.

Monitoring PgBouncer

PgBouncer has an admin console: connect to the virtual pgbouncer database and run SHOW POOLS; and SHOW STATS;. Watch in particular:

• cl_waiting — clients waiting for a server connection. Occasional small numbers are normal; a steady queue means the pool is too small or queries are too slow.
• maxwait — how long the oldest waiting client has been waiting.
• sv_active vs sv_idle — how busy the server connections really are.
• Average query and transaction times from SHOW STATS — if they rise, the database itself is the bottleneck, and a bigger pool will not help.

Long-running or idle-in-transaction sessions are the classic way to drain a transaction pool: each one pins a server connection. Setting idle_in_transaction_session_timeout in PostgreSQL protects the pool from forgotten transactions. If the queries themselves are slow, start with our PostgreSQL slow query tuning checklist.

Where to run PgBouncer

• Next to the database — one central pooler, simplest to manage, and the right choice for most setups.
• Next to the application (for example as a sidecar) — removes a network hop, but every application node then has its own pool, so the total number of server connections grows with the number of nodes.
• With high availability — PgBouncer itself must not become the single point of failure. Run at least two instances behind a load balancer or virtual IP, and make sure they follow the primary after a failover. We cover the database side in our guide to PostgreSQL high availability with Patroni.

Managed services offer their own equivalents, such as Amazon RDS Proxy, and alternatives like Pgpool-II, Odyssey and PgCat exist. PgBouncer remains the default choice because it is small, fast, well understood and does one job well.