PgBouncer Connection Pooling: Modes and Sizing
Postgres gives every client connection its own operating-system process, and each backend carries fixed memory and scheduling overhead. That is fine when a handful of long-lived connections do the work, but modern apps rarely behave that way. Web frameworks, background workers, and serverless functions each open their own connections, and the count climbs fast. Past a point, adding connections makes throughput worse: the database spends more time context-switching and contending on locks than running queries. A pooler sits between the application and Postgres to keep the number of real server connections small and busy.
Why a postgres connection pool changes the math
The job of a postgres connection pool is multiplexing. Many short-lived client connections share a much smaller set of server connections that stay open and warm. PgBouncer is a common choice because it is lightweight, single-purpose, and adds negligible latency. It holds the client sockets and hands each one a server connection only when there is actual work to do, returning it to the pool the moment the work finishes. A database tuned for forty active backends can then serve thousands of application clients without exceeding that ceiling. Before changing topology, confirm the bottleneck is connection count and not slow statements: one unindexed query repeated thousands of times looks like a capacity problem but is really a plan problem. Pasting it into a free analyzer like the one at Querk's analyze page returns a diagnosis and any missing index DDL in seconds.
Session, transaction, and statement pool modes
PgBouncer offers three pool modes, and the choice decides how aggressively server connections get reused. Session pooling assigns a server connection to a client for the whole life of that client connection and only releases it on disconnect; it is the safest mode because every session feature behaves as it would with no pooler, but it multiplexes the least. Transaction pooling returns the server connection to the pool at the end of each transaction, so one backend serves many clients between their statements — this is where the large connection reductions come from and the mode most teams want. Statement pooling releases the connection after every individual statement, forbids multi-statement transactions entirely, and suits only narrow autocommit workloads.
Sizing pools and the transaction-mode traps
Transaction pooling is powerful, but it changes the contract the application relies on. Anything that lives at the session level — SET commands, session variables, advisory locks, LISTEN/NOTIFY, and server-side prepared statements — can leak between clients or break, because the server connection no longer belongs to one client across statements. Server-side prepared statements are the classic trip-wire: a driver that prepares a statement on one backend may try to execute it on another. Recent PgBouncer releases added protocol-level prepared-statement support in transaction mode, which removes much of that pain when enabled and the driver cooperates. On sizing, bigger pools are not better; a practical starting point ties pool size to CPU cores rather than to client count, often near cores times two, then adjusts using real wait data. The pg_stat_statements view shows which statements dominate time, and the query planner explains why a given one is slow, so you can separate connection pressure from slow queries. Reach for PgBouncer when connection counts outpace what the server can comfortably hold, when short transactions dominate, or when serverless scaling makes client count unpredictable — not as a blanket fix for a database that is slow per query.