Connections, and Why You Need a Pooler

A Postgres connection is an operating-system process. The postmaster forks one per client, and that process carries its own catalogue caches, its own plan cache, its own memory context tree and its own slot in the shared array that every snapshot has to walk. Opening one costs a fork and an authentication round trip; keeping one costs memory and a little of every other backend's time. No configuration setting turns a connection into a cheap object you can create per request.

Cartwheel opens 400 of them. app-01 and app-02 each run 20 worker processes with a driver-side pool of 10, and max_connections on pg-primary is 200 — so half the workers get FATAL: sorry, too many clients already during the Saturday peak, and the half that get in spend their time contending for 16 cores. The fix is not a bigger number. Putting pgbouncer-01 in transaction mode between the app and the database turns 400 client connections into 40 server ones, and throughput goes up on hardware that did not change.

What Happens Past the Knee

Throughput against concurrency has three phases, and only the first is the one people picture. It climbs while there is idle CPU to soak up, flattens once the number of actively running backends approaches the core count, and then it falls. The mechanisms are unglamorous: the scheduler starts context-switching between processes that each want a full CPU, the shared structures every backend touches become contended, from the lock table to the buffer mapping table to the process array, and the CPU caches are thrashed by processes taking turns on the same cores. Past the knee, adding a client makes every client slower, including the one you added.

The distinction that matters is active against idle. Four hundred connections that are idle waiting for their application to send something cost memory and a slightly longer array to scan. Four hundred that are all executing at once on a 16-core machine cost throughput, and the loss is not small. The sizing rule for the server-side pool is therefore expressed in cores and not in clients: two to four times the core count for a mixed workload, which on pg-primary is 32 to 64. Cartwheel settles at 40, and client 41 waits in a queue for one of them.

max_connections Is a Reservation, Not a Target

max_connections defaults to 100, or lower where initdb probes the kernel and settles under it, and it is set at server start, so changing it needs a restart. The documentation is direct about what raising it does: Postgres sizes several resources directly on this value, so a larger setting allocates more shared memory whether the connections arrive or not. Three slots are held back by superuser_reserved_connections so an administrator can still get in when the rest are gone, and reserved_connections, zero by default, does the same for roles granted pg_use_reserved_connections.

SELECT count(*) FILTER (WHERE state = 'active')              AS active,
       count(*) FILTER (WHERE state = 'idle')                AS idle,
       count(*) FILTER (WHERE state = 'idle in transaction') AS idle_in_txn,
       count(*)                                                AS total
  FROM pg_stat_activity WHERE backend_type = 'client backend';

 active | idle | idle_in_txn | total
--------+------+-------------+-------
     23 |  171 |           6 |   200

Twenty-three backends were doing work. The other 177 were processes holding memory and a slot, and six of them were sitting inside an open transaction, which is the expensive kind of idle for reasons Chapter 6 spends a whole topic on. Raising max_connections to 1000 would have admitted the 200 clients being refused and changed the shape of this table not at all: the same 16 cores, five times the processes, and a per-backend work_mem exposure five times larger. The FATAL lines disappear from the log, and the latency at the ninety-fifth percentile goes up.

PgBouncer and Its Pool Modes

PgBouncer is not part of PostgreSQL. It is a separate project — a single-threaded, event-driven proxy that speaks the Postgres wire protocol, listens on port 6432 by default, and holds thousands of client sockets against a small number of real server connections. Its settings and its limits are documented by that project rather than by the Postgres manual, which matters a great deal when you go looking for them.

It offers three pooling modes and choosing between them is the entire decision. Session pooling, the default, assigns a server connection to a client for as long as that client stays connected: every Postgres feature works, and against an application that holds 400 long-lived connections it achieves nearly nothing. Transaction pooling assigns a server connection at BEGIN and takes it back at COMMIT, so the 177 idle backends in that snapshot stop existing — this is the mode that produces the win, and the one with a compatibility list. Statement pooling returns the connection after every single statement and rejects multi-statement transactions outright; it exists for autocommit-only clients and is rarely the right answer.

; the app's pool points at the primary, the dashboard's at the replica
[databases]
cartwheel     = host=pg-primary   port=5432 dbname=cartwheel pool_size=40
cartwheel_ro  = host=pg-replica-a port=5432 dbname=cartwheel pool_size=8

[pgbouncer]
pool_mode          = transaction
listen_port        = 6432
max_client_conn    = 2000     ; client sockets: cheap
default_pool_size  = 20       ; server connections: not cheap
min_pool_size      = 10       ; keep some warm for the spike
query_wait_timeout = 10       ; default is 120s — fail faster than that

Two logical databases point at two different hosts, which is how the analytics dashboard stops competing with checkout: a long report can occupy at most the eight server connections in its own pool. On the client side, 2,000 slots cost a socket and a buffer each and exist so that a deploy or a restart storm queues instead of erroring. query_wait_timeout is the one default worth changing immediately: two minutes of queueing is indistinguishable from a database outage upstream, and ten seconds produces an error while the request that caused it is still alive.

What Transaction Pooling Takes Away

Everything that lives in a session rather than a transaction stops being reliable, because consecutive transactions from one client can land on different server connections. PgBouncer's compatibility matrix names them: session-level SET and RESET, LISTEN, WITH HOLD cursors, SQL-level PREPARE and DEALLOCATE, session-level advisory locks, temporary tables declared ON COMMIT PRESERVE ROWS or DELETE ROWS, and LOAD. What survives is the transaction-scoped half of each pair — NOTIFY works, a cursor WITHOUT HOLD works, and a temporary table declared ON COMMIT DROP works.

-- broken behind a transaction pooler: two statements, possibly
-- two different server connections, and the SET applies to a stranger
SET work_mem = '256MB';
SELECT … FROM analytics.daily_orders ORDER BY placed_day;

-- correct: one transaction is never split across server connections
BEGIN;
SET LOCAL work_mem = '256MB';
SELECT … FROM analytics.daily_orders ORDER BY placed_day;
COMMIT;

A session-level SET in transaction mode does not fail. It succeeds on whichever server connection happened to be free, the query that was supposed to benefit from it runs somewhere else at the default, and some unrelated client inherits a 256 MB grant it never asked for — a bug with no error message, which is the worst kind. Session advisory locks behave the same way and are worse, because a lock taken by one client is released by whoever gets that connection next. Wrapping the pair in a transaction removes the ambiguity entirely: PgBouncer never splits a transaction.

Prepared statements deserve their own sentence, because the advice on the internet is out of date. Protocol-level named prepared statements, which is what JDBC, psycopg and most modern drivers actually use, have worked in transaction mode since PgBouncer 1.21, and 1.24 turned the support on by default with max_prepared_statements = 200. SQL-level PREPARE still does not work. Audit which of the two your driver emits before switching modes rather than after, and pin max_prepared_statements explicitly; on a build older than 1.24 the same configuration file behaves differently.

Sizing the Pool and Watching It

The server-side pool is sized to the database host and nothing else: 40 for pg-primary's 16 cores, 8 for the analytics pool on the replica. The client side is sized to the application and can be an order of magnitude larger, because a waiting client costs a socket. min_pool_size keeps a floor of warm server connections so that a Saturday ramp does not pay for 40 simultaneous connection setups at the worst possible moment. If you run one PgBouncer per application host, remember that each has its own pool and the database sees the sum — two hosts at pool_size=40 is 80 backends, not 40.

$ psql -p 6432 -U pgbouncer pgbouncer -c "SHOW POOLS"

   database   |        user         | cl_active | cl_waiting | sv_active | sv_idle | maxwait
--------------+---------------------+-----------+------------+-----------+---------+---------
 cartwheel    | cartwheel_app       |       386 |         14 |        38 |       2 |       3
 cartwheel_ro | cartwheel_analytics |         6 |          0 |         3 |       5 |       0

The diagnosis lives in three columns. cl_waiting is clients that have sent a query and have no server connection yet; sv_active against the pool size says how much of the database-side capacity is in use; and maxwait is how many seconds the oldest waiting client has been queued. A maxwait that is briefly non-zero at peak is the pooler doing its job. A maxwait that climbs steadily means either the pool is too small or the queries are too slow, and sv_active tells you which: pinned at 40, the database is the constraint; well under it, the queue is somewhere else.

Where the Pooler Lives

The pooler can live in three places, and each one fails differently. On the application host, it adds no network hop and dies with the host it serves, at the cost of N independent pools whose sum is what the database actually sees, and N configurations to keep identical. On a dedicated host like pgbouncer-01, there is one pool with one number to reason about and one more network hop on every query, typically a fraction of a millisecond on a local network. As a sidecar next to each application container, the model is the per-host one with a higher process count and the same arithmetic.

The dedicated host has the obvious problem, and it is worth saying plainly: putting a single non-redundant pooler in front of a highly available database moves the single point of failure one hop upstream. Whatever runs PgBouncer needs the same treatment as the database: a standby, a virtual IP or a load balancer in front, monitoring on the pool metrics, and a line in the runbook. Chapter 13 promotes pg-replica-b with the pooler in the diagram, because the application reaches the new primary through it or not at all.