Database Connection Pooling

What you'll explore

1. 01

   Three Modes, One Line of Code

   The entire difference between session and transaction pooling is one condition. On the packet that ends a query — ReadyForQuery — PgBouncer decides whether to give the backend back, and the test is at src/server.c:888: if (connection_pool_mode(server) != POOL_SESSION || server->state == SV_TESTED || server->resetting). In transaction and statement mode that is true, so release_server() runs (:913) and the backend returns to the pool the instant the transaction ends. In session mode it is false, and the backend stays linked to that client until the client disconnects. Everything people attribute to pooling "strategy" follows from which side of that branch you are on. Statement mode adds a second condition rather than a different release rule: if the ReadyForQuery state is anything but idle, src/server.c:449-451 calls disconnect_server(server, true, "transaction blocks not allowed in statement pooling mode") — a BEGIN does not get an error, it gets the connection torn down. And the configured mode is not always the running mode: connection_pool_mode() (:267-271) forces POOL_SESSION for replication connections whatever pool_mode says, because replication cannot survive being multiplexed. The modes are POOL_SESSION 0, POOL_TX 1, POOL_STMT 2 in include/bouncer.h:260-262 — with a fourth value, POOL_INHERIT 3 (:263), that is not a mode at all but the marker for "take the default", which is why a per-database pool_mode and the global one resolve rather than conflict.

2. 02

   One Slider, Three Limits, Two of Them Adverse

   default_pool_size looks like a single capacity number and is really three at once, and they do not move together. Raise it and (1) the throughput ceiling rises: a pool of P connections serving queries of mean service time L can retire at most P / L queries per second, which is Little's law read as a service rate, and it is the only one of the three that improves. (2) The claim on the server rises, multiplied: pool_size is per (database, user) pair — get_pool() (src/objects.c:776-791) matches on pool->db == db within one user's pool_list, so the pool object is keyed by the pair — and PgBouncer writes that multiplication out itself when it estimates file descriptors: fd_count = max_client_conn + 10, then += pool_size * total_users per database (src/main.c:830-836), with a single-pool branch when forced_user_credentials is set (:833-834). (3) The reconnect cadence on the server rises: when a backend passes server_lifetime (default 3600 s, src/main.c:327) PgBouncer does not close them together, it staggers them — lifetime_kill_gap = server_lifetime / pool_pool_size(pool) (src/objects.c:1203-1204), guarded by the comment "This avoids the need to re-launch lot of connections together" (:1198-1201). A pool of 20 replaces a connection every 180 seconds; a pool of 200, every 18. Which of the three binds is a question about your server, not your pooler, and the resolution order for the value itself is user pool_size, then database pool_size, then cf_default_pool_size (src/server.c:294-303). And the number the second limit is measured against is not the one in postgresql.conf: max_connections defaults to 100 and is PGC_POSTMASTER, a restart rather than a reload (src/backend/utils/misc/guc_tables.c:2332-2339), while superuser_reserved_connections keeps 3 of those back (:2343-2350). An untouched cluster therefore admits 97 ordinary connections, and the stock pooler arithmetic — 20 per pair across five credentials — asks it for 100. Two components whose defaults are individually reasonable are collectively three over the line, and nothing in the pooler compares its own fd estimate against the server's setting to find out. The collision is not unfixable, though — it is un-defaulted. launch_new_connection() checks three caps in order (src/objects.c): the pair's own pool_size (:1951-1953), then max_db_connections, which bounds total server connections for a DATABASE across every user (:1958, resolved db-then-global at src/server.c:349-355), then max_user_connections for a user across every database (:1973, src/server.c:357-363). max_db_connections is precisely the aggregate this section says is otherwise unbounded — and it defaults to 0, meaning unlimited (src/main.c:303; max_user_connections likewise at :307). Note what each does before refusing: it tries to EVICT an unused connection first (:1960-1964, :1975-1979), so these caps reshape the pool rather than simply rejecting. The pattern is the one nginx's upstream keepalive teaches in a different costume: the knob you need exists, and the default is what surprises you.

3. 03

   Sized for Sessions, Sized for Work

   The multiplier people quote — max_client_conn divided by pool_size — is a ratio between two counts, so it is at least dimensionally honest, but it does not predict anything on its own, because what a pool needs is concurrency and neither number is one. Concurrency is what Little's law gives you: over any interval, the mean number of queries in service equals the arrival rate times the mean service time. So a service with 200 handlers each running one 3 ms query per 200 ms request needs 200 x 0.003 / 0.2 = 3 server connections' worth of concurrency, and it can be served by a pool of 5 — in transaction mode. In session mode the same workload needs 200, because the release branch at src/server.c:888 never fires: the backend is owned by the client session, not by the query, so the pool is sized by how many clients are connected and is entirely indifferent to how busy they are. That is the whole of "session mode wastes connections on async workloads", and it is why the two modes want different numbers from the same slider. The honest warning band on the multiplier is a calibrated judgement, labelled Tier-2 where it appears — the arithmetic above is the Tier-1 part. When demand does exceed the pool, the queue is not a parameter: clients wait, and the module's next section is what waiting costs.

4. 04

   The Advice That Inverted

   The most repeated sentence about PgBouncer is that you cannot use protocol-level prepared statements in transaction mode. At this pin the gate says the opposite, in one macro: is_prepared_statements_enabled() (include/prepare.h:34-35) is (connection_pool_mode(client_or_server) != POOL_SESSION && cf_max_prepared_statements != 0). Tracking runs when the mode is NOT session — transaction and statement mode are the modes it exists for — and max_prepared_statements defaults to 200 (src/main.c:305), so with a non-session pool_mode it is on with no configuration at all. Out of the box it is NOT on, and that is the detail which kept the folklore alive: pool_mode's own default is session (cf_pool_mode = POOL_SESSION at src/main.c:100, the CF_ABS default "session" at :313), so an untouched PgBouncer fails the macro's FIRST half. A team that switches to transaction pooling and hits an error blames the mode it just changed, when the mode is what turned the feature ON. PgBouncer keeps a per-client map of statement names and a per-server map of query ids (include/prepare.h:21-32), re-preparing on whichever backend a transaction lands on and evicting once a server's map passes the cap (src/prepare.c:262). Session mode is the branch where tracking is OFF, and it needs nothing: one client keeps one backend, so the backend's own prepared statements persist natively for the life of the session. The real pathology is therefore not a mode, it is a number — set max_prepared_statements = 0 while pooling by transaction and the second half of the macro fails, at which point a client that prepares on one backend and binds on another gets told its statement does not exist. That is a live failure mode, reachable from this module's own controls, and the advice in circulation points at the wrong axis entirely. Statement mode inherits the tracking and keeps its own restriction: no transaction blocks (src/server.c:449-451).

5. 05

   Two Pools, and the Queue Between Them

   There is rarely one pool. An in-process pool lives inside each application replica, and an external pooler sits in front of the server, and the sizes MULTIPLY rather than override: replicas x in-process MaxConns arrive as clients at the pooler, whose own pool_size x (database, user) pairs arrive as connections at the server. The two layers are not interchangeable and their facts must not be cited to each other — pgxpool's defaults are Go library defaults (defaultMaxConns = 4 at pgxpool/pool.go:19, raised to runtime.NumCPU() when that is larger, :384-386, documented :176-177; MinConns 0, MaxConnLifetime 1 h, MaxConnIdleTime 30 min, HealthCheckPeriod 1 min, :20-24), while pool_size and max_client_conn are PgBouncer's. What the two layers do share is that neither refuses work at the edge: pgxpool's Acquire() (pgxpool/pool.go:615) delegates to a blocking acquire and returns only when a connection frees or the CALLER'S context is done, and PgBouncer queues too. PgBouncer's queue has three exits, in order. It can be promoted: after reserve_pool_timeout (default 5 s, src/main.c:318) a waiting client may take a connection from reserve_pool_size (src/objects.c:1930-1936, which logs "taking connection from reserve_pool") — but that size defaults to 0 (:317), so by default this exit does not exist. It can be served. Or it expires, and the exit is blunter than most people expect: the janitor calls disconnect_client(client, true, "query_wait_timeout") (src/janitor.c:344). Not an error into a live session — the session is gone. The same verb handles the other boundary: exceed max_client_conn and src/client.c:1075-1077 disconnects with "no more connections allowed (max_client_conn)". A pooler under pressure does not return errors; it closes connections, which is why the application's symptom is a dropped socket and the server's graphs look fine.