How to Pool Connections Without Pgbouncer Lying to You

Postgres allocates a backend process per connection. A few hundred idle connections from an application that opens one per worker will eat memory and scheduler time long before they do any work, and the database gets slower while doing nothing.

PgBouncer sits in front and hands a small set of real server connections to a large set of clients. It solves the problem completely, and it introduces one decision that decides whether your application still behaves.

The decision is pool_mode

There are three, and the default is not the one people mean.

session is the default. A server connection is held for as long as the client stays connected. Everything behaves exactly like a direct connection, and you have pooled almost nothing: a hundred connected clients still hold a hundred server connections.

transaction releases the server connection back to the pool at the end of each transaction. This is the mode that produces the numbers people install PgBouncer for. Between transactions, your client has no server connection at all, and the next transaction may land on a different backend.

statement releases after every single statement and disallows multi-statement transactions. Narrow, and rarely what an application wants.

# /etc/pgbouncer/pgbouncer.ini
[databases]
app = host=10.0.0.5 port=5432 dbname=app

[pgbouncer]
pool_mode = transaction
default_pool_size = 20
max_client_conn = 1000
server_reset_query = DISCARD ALL

What transaction pooling takes away

Anything that lives on a session rather than in a transaction stops being reliable, because the next statement may run on a different backend.

• SET outside a transaction. Set a search_path or a timezone once at connect, and it applies to whichever backend you happened to hold at the time.
• Session-level advisory locks. Take one, release the connection, and it is held by a backend you no longer have.
• LISTEN and NOTIFY. The listener is attached to a backend that has moved on.
• Temporary tables. Created on one backend, invisible to the next.
• WITH HOLD cursors.

None of these fail loudly at deploy time. They fail under load, when the pool is actually being shared, which is why the symptom is usually "it works in staging".

Prepared statements, which used to be the classic trap

Protocol-level named prepared statements are a session feature, and for years they simply could not be used through transaction pooling. Many ORMs use them by default, which is why the standard advice was to turn them off in the driver.

PgBouncer now tracks them itself. max_prepared_statements defaults to 200 and enables protocol-level prepared statement support in transaction and statement pooling mode, at the cost of some CPU and memory.

That changes the advice: check your PgBouncer version and the effective value before you disable prepared statements in your driver, because you may be paying a real performance cost for a workaround you no longer need.

Sizing, which is where people go wrong in the other direction

default_pool_size is the number of server connections per user and database pair. It defaults to 20. max_client_conn is how many clients may connect to PgBouncer at all, and defaults to 100.

The mistake is treating a bigger pool as a faster database. Postgres does not go faster with more concurrent backends; past the point where they saturate CPU and disk, it goes slower. A pool of 20 to 40 in front of a machine with 8 cores is a normal answer. Forty application containers each opening 10 connections to a pool of 200 is a way to reproduce the original problem through a proxy.

Raise max_client_conn freely, since idle client connections in PgBouncer are cheap. Raise default_pool_size carefully, and only with a measurement behind it.

Watch it while it works

-- connect to the pgbouncer admin console, not to Postgres
SHOW POOLS;
SHOW STATS;

cl_waiting in SHOW POOLS is the number that matters. If clients are waiting, the pool is too small for the load or a transaction is holding a connection far too long. Persistent waiting with an idle database usually means the second one, and the fix is in the application, where a transaction was opened around work that did not need to be inside it.

The safe order to adopt it

1. Deploy in session mode. Nothing changes behaviourally, and you get an inventory of who connects.
2. Find and fix the session-state assumptions listed above.
3. Move to transaction mode in staging under realistic concurrency, not with one test user.
4. Watch cl_waiting and error rates for a week before tuning sizes.

Connection pooling that holds up under real load, rather than in a demo, is part of our infrastructure management work.