Connection Pooling

In a naive database architecture, a backend application opens a new database connection whenever it needs to run a query, and closes the connection immediately after receiving the results. Under load, this pattern quickly becomes a severe bottleneck.

Opening a physical database connection is an expensive, multi-step process:

• TCP Handshake: Initiating a network socket requires a three-way handshake (SYN, SYN-ACK, ACK) between the application and the database server.
• TLS Negotiation: Encrypted connections require multiple cryptographic round-trips to establish session keys.
• Authentication and Authorization: The database must parse credentials, verify user permissions, and establish session contexts.
• Process Spawning: Traditional database engines (like PostgreSQL) spawn a dedicated backend OS process for each incoming connection, consuming system memory and CPU during initialization.

Because of this overhead, establishing a connection can take anywhere from tens to hundreds of milliseconds. If your web request runs a query in 2 milliseconds but spends 50 milliseconds opening the connection, your system efficiency is abysmally low.

To bypass this cost, systems use connection pooling. A connection pool is a thread-safe cache of open, authenticated database connections maintained by the client application.

When the application needs to query the database:

1. It requests a connection from the pool.
2. The pool immediately returns an existing, active connection from its idle queue.
3. The application executes the query over this connection.
4. When finished, the application releases the connection back to the pool rather than closing it.

By keeping a warm pool of sockets, the application avoids connection negotiation costs on almost every query.

The following diagram illustrates how multiple client threads share a bounded set of idle and active database connections:

A common mistake is configuring a massive connection pool under the assumption that more connections equal higher performance. In reality, too many connections degrade performance due to disk I/O bottlenecks and CPU context switching.

A single CPU core can only do one thing at a time. If you have 4 CPU cores and run 100 concurrent queries, the operating system must constantly swap tasks on and off the cores (context switching), wasting cycles. Furthermore, spinning disk hard drives are constrained by physical spindles.

The PostgreSQL and HikariCP maintainers recommend a sizing formula based on hardware limitations:

connections = (core_count * 2) + effective_spindle_count

Where:

• core_count is the number of physical CPU cores.
• effective_spindle_count represents the disk parallelism (e.g. a RAID array of hard drives). For SSDs, this can be modeled based on concurrent I/O capabilities.

For example, on a 4-core server with an SSD, a pool size of roughly 9 or 10 connections is often optimal. Keeping the pool size small keeps the database server focused, preventing context-switch thrashing and disk queue congestion.

Configuring a connection pool requires tuning several key variables:

• MaxPoolSize: The absolute limit on active connections. When reached, subsequent callers block until a connection is returned.
• MinIdle: The minimum number of idle connections the pool maintains. Keeping this equal to MaxPoolSize (fixed-size pool) is recommended in production to avoid dynamic scaling latency.
• MaxLifetime: The maximum age of a connection. Periodically destroying and recreating connections prevents memory leaks and clears stale server resources.
• ConnectionTimeout: The duration a client will wait to borrow a connection before failing with an error.

A robust connection pool must handle networks drops, database crashes, and application bugs:

• Validation Queries: Before handing a connection to the application, the pool runs a cheap test query like SELECT 1 to verify the socket is alive.
• Failover Handling: If the database server crashes or fails over, the pool must purge all existing connections and reconnect to the new primary host.
• Connection Leak Detection: If an application fails to release a connection back to the pool, the connection is leaked. Over time, leaks exhaust the pool. Production pools track borrowed connections and log warning stack traces if a connection is held longer than a configured threshold.

• HikariCP Wiki on Pool Sizing
• PostgreSQL Connection Pooling Docs
• Designing Data-Intensive Applications by Martin Kleppmann