Database Connection Pooling
A connection pool is a queue you cannot see, in front of a limit you did not set — and the three most repeated things about PgBouncer are a mode confusion, an arithmetic error and an inversion. Start with the inversion, because it is the one still in every runbook: protocol-level prepared statements are not incompatible with transaction pooling. The gate is a single macro requiring a non-session mode AND a non-zero cap, so transaction pooling is the mode the feature exists for, the cap defaults to 200, and session mode is where tracking is switched off — because a client that keeps one backend for its whole session needs no emulation at all. The failure everyone attributes to the mode is really a cap of zero, at which point a client that prepares on one backend and binds on another is told its statement does not exist. Next the arithmetic. What a pool needs is concurrency, and concurrency is Little's law: an arrival rate times a service time. A client cap is not a rate, so multiplying clients by milliseconds produces a number in the wrong units — 200 handlers each running one 3 ms query per 200 ms request need about three connections, not two hundred, and the pool that does need two hundred is the same workload in session mode, where the backend is released only when the client disconnects and the pool is therefore sized by how many clients are connected rather than by how busy they are. That one difference is a single condition in the source, evaluated on the packet that ends each query. Then the mode confusion, which is really a counting error: pool_size is per (database, user) pair, not per database and not per server, and PgBouncer writes the multiplication out itself where it estimates its own file descriptors. Five distinct users on one database at the default pool size is a hundred server connections, against the ninety-seven an untouched PostgreSQL leaves for ordinary roles once its three superuser slots are subtracted — so the stock configuration of both components does not fit, and nothing in the pooler clamps itself to discover that. The same slider is three limits at once and only one of them improves when you raise it: throughput rises as pool size over service time, the claim on the server rises multiplied by your credential count, and the staggered recycle interval — server_lifetime divided by pool size — gets shorter, so a bigger pool reconnects to PostgreSQL more often, not less. Finally, what overload looks like. Neither pool refuses at the edge and neither returns a database error: the in-process pool blocks until a connection frees or the caller's context expires, and the proxy queues, with three exits — a notification that only logs, a reserve tier that defaults to zero connections and therefore does not exist unless configured, and an expiry that disconnects the waiting client outright. The same verb handles the other boundary, so an overloaded pooler presents to the application as a dropped socket and to the database as nothing at all. Change the pool mode, the pool size, the client cap, how many (database, user) pairs you use, PostgreSQL's own max_connections, the service time, the prepared-statement cap and the queue timeout, and watch the server-computed cascade across all five layers — connection modes, pool sizing, transaction versus session, prepared statements and the two-pool stack — including the places where the defaults of three separate components are individually reasonable and collectively over the line. Validated against PgBouncer pgbouncer_1_26_0 source (connection_pool_mode, release_server, get_pool, pool_pool_size, life_over, add_prepared_statement, is_prepared_statements_enabled, pool_client_maint, handle_client_startup), pgx v5.11.0 (pgxpool Pool.Acquire and its defaults) and PostgreSQL REL_18_6 (max_connections and superuser_reserved_connections in guc_tables.c).
- All current + future modules
- Full kata library
- Progress dashboard + weak area analysis
Cancel anytime. Billed annually.