Connection Pooling: The General Idea and the Tools That Implement It

Establishing a new connection, whether to a database or over plain HTTP, carries real, measurable cost: a network handshake at minimum, and for a database, authentication and process or memory allocation on top of that. Connection pooling is the practice of maintaining a small set of already-open connections and reusing them across many logical operations, rather than opening and closing a new one for every single operation. A pool maintains a bounded number of live connections; a caller checks one out, uses it, and returns it to the pool instead of closing it, and the pool enforces a maximum size that caps the load placed on whatever resource sits behind it.

Why it matters, and why it's close to mandatory for databases specifically

The cost being amortized is concrete. The node-postgres library's own documentation notes that connecting a new client to a PostgreSQL server requires a handshake that can take 20 to 30 milliseconds, and that a Postgres server can only handle a limited number of clients at once, tying directly back to why database connections are an expensive, fixed resource in the first place. Paying that handshake cost on every single query, instead of once per pooled connection reused many times over, is the difference pooling exists to capture.

There's a second reason pooling becomes close to mandatory once a backend is horizontally scaled, not just a performance nicety: if an application runs across multiple instances and each instance keeps its own in-process pool of size k, the database ends up facing up to (number of instances) times k simultaneous connections, regardless of how much actual concurrent work is happening. This is well-documented operational guidance across major Postgres hosting providers: with ten application replicas each running a pool of 20 connections, the database sees up to 200 connections competing for a max_connections budget that's often set far lower, producing a hard failure once that ceiling is hit. An external, shared pooler exists specifically to enforce one bounded connection budget against the database regardless of how large the application fleet grows.

Database poolers

PgBouncer, the standard pooler for PostgreSQL, documents three distinct pooling modes, and the choice between them is a real tradeoff, not a formality. In session mode, a server connection is released back to the pool only after the client disconnects entirely: the most compatible mode, and the least efficient. In transaction mode, the server connection is released back to the pool as soon as a transaction finishes, the commonly recommended default, since it allows far more reuse. In statement mode, the connection is released after each individual query, and transactions spanning multiple statements are disallowed entirely. PgBouncer's own documentation is direct about what transaction and statement pooling break: since the underlying server connection can be handed to a different client between statements or transactions, session-scoped Postgres features stop working reliably, including SET/RESET, LISTEN, prepared statements, and session-level advisory locks. Choosing transaction pooling means auditing for any of these before switching to it.

Pgpool-II is a related but heavier tool: alongside pooling, it bundles load balancing, automatic failover, and query caching, making it more of a full middleware layer than a lightweight pooler. ProxySQL is the closest MySQL-ecosystem equivalent to PgBouncer, adding connection multiplexing, query routing, and read/write splitting on top of pooling itself.

There's an important architectural distinction worth being precise about: an in-process pool (SQLAlchemy's default QueuePool, or node-postgres's built-in Pool) lives inside the application's own process, one pool per running instance. An external pooler like PgBouncer is a separate service that many application instances share. The multi-instance connection-explosion problem described above is specifically what motivates reaching for the external kind once an application is running as more than a single instance.

HTTP connection pooling: the same idea, a different protocol

The same reuse principle applies to plain HTTP connections, and it's worth knowing since it's usually invisible, handled automatically by HTTP client libraries rather than something a developer configures deliberately. Python's requests library documents that keep-alive is "100% automatic within a session": any requests made within a Session object automatically reuse the appropriate underlying connection. httpx exposes this more explicitly, with tunable limits (max_connections, max_keepalive_connections, keepalive_expiry) via an httpx.Limits object. Node's http.Agent documents the same underlying mechanism: with keepAlive: true set, idle sockets are kept in a pool for reuse against the same host and port rather than being closed after each request. This is the identical checkout-use-release shape as a database pool, just applied to TCP (and, for HTTPS, TLS) connection setup cost instead of a database connection's setup cost.

Sizing a pool correctly

HikariCP, a widely regarded JVM connection pool implementation, publishes an unusually direct and citable sizing formula in its own documentation: connections = (core_count * 2) + effective_spindle_count, where core_count is the physical core count of the database server and effective_spindle_count approaches zero as more of the active dataset fits in memory. HikariCP's own worked example, a four-core machine with one disk, arrives at roughly ten connections, which they document as sufficient to support 3,000 front-end users at 6,000 transactions per second.

The more counterintuitive and genuinely useful part of HikariCP's own documentation is a real before-and-after case: reducing an oversized pool actually decreased response times, from roughly 100 milliseconds to roughly 2 milliseconds, over a 50x improvement. Their stated reasoning: connections aren't like application threads. Once there are enough connections to keep the database's CPU cores saturated, additional connections don't add more throughput, they add contention, through context switching and lock contention on the database's own internals. Their own guidance, worth quoting directly: a small pool of a few dozen connections at most is usually correct, with application threads blocking on the pool to wait their turn rather than each getting its own dedicated connection.

Applying it

• Reach for an external, shared pooler like PgBouncer the moment an application runs as more than one instance, not only once a specific connection-limit error has already occurred.
• Choose PgBouncer's transaction pooling mode as a sensible default, but explicitly check the application for reliance on session-scoped features (prepared statements, session variables, advisory locks) before switching, since those silently stop working correctly under it.
• Don't assume a bigger pool is always safer. HikariCP's own documented case shows a smaller, correctly sized pool measurably outperforming an oversized one, since excess connections add contention rather than throughput past a certain point.
• Remember that HTTP connection reuse is usually already happening automatically through whichever client library is in use. Confirm keep-alive is actually enabled, and its limits are tuned appropriately, rather than assuming the defaults are automatically correct for a given workload.