Connection Pooling

What it is

A connection pool is a cache of pre-established database (or service) connections that are reused by multiple request threads, rather than opening and closing a new connection for each request. Establishing a TCP connection, TLS handshake, and database authentication takes 10–100ms per connection on a local network, and much more across regions. Multiplying this overhead by thousands of requests per second makes per-request connection establishment untenable. A pool maintains a set of warm connections ready to be borrowed, used, and returned — reducing per-request database setup to microseconds.

Connection pools operate on a borrow-use-return lifecycle: a request borrows a connection from the pool, executes its queries, and returns the connection to the pool. If all pool connections are in use when a new request needs one, it either waits (blocks) for a connection to be returned, or times out and fails. The pool also manages connection health: idle connections are periodically validated (using SELECT 1 or equivalent) and replaced if the database dropped them due to server-side idle timeouts.

Why it exists

PostgreSQL spawns a new OS process per connection. A PostgreSQL instance with 1,000 active connections is running 1,000 OS processes, each consuming ~5MB of RAM for the connection state — 5GB just for connections, before any data is processed. The OS scheduler adding context-switch overhead across 1,000 processes causes significant CPU overhead even when most connections are idle. PostgreSQL's recommended max_connections is typically 100–300; exceeding this causes serious performance degradation.

Connection poolers like PgBouncer act as a multiplexer: 1,000 application threads can connect to PgBouncer, which maintains only 100 real connections to PostgreSQL. PgBouncer queues requests and routes them through its 100 connections, allowing the application to scale horizontally without creating a proportional number of database connections. This is especially important in containerized environments where the number of application pods may vary dynamically from 10 to 500 during auto-scaling events.

When to use

• Any application making repeated connections to a relational database — connection pooling is a near-universal requirement for production systems.
• Horizontally scaled application tiers where the total number of application instances would otherwise create hundreds or thousands of database connections.
• Applications connecting to PostgreSQL, where per-connection process overhead is particularly high.
• Microservices with high-frequency short-lived service calls to other services via HTTP or gRPC (HTTP connection pools, gRPC channel pools).
• Serverless functions that require database access — without pooling, each invocation creates a new connection; PgBouncer or RDS Proxy mediates this.

When not to use

• Databases that use a thread-per-connection model with very low per-connection overhead (MySQL with max_connections=5,000 on large instances) — pooling is still beneficial but less critical than with PostgreSQL.
• Single-threaded applications with sequential processing — a single pre-established connection is sufficient; a pool of one is equivalent.
• Long-running transactional operations in PgBouncer transaction mode — transaction mode closes the server connection between transactions, which breaks session-level features like advisory locks and SET LOCAL settings.

Typical architecture

  In-Process Pool (HikariCP / c3p0):
  App Server ──► HikariCP Pool (min=5, max=20)
                 ├── conn1 [in use by thread A]
                 ├── conn2 [in use by thread B]
                 ├── conn3 [idle, validated]
                 ├── conn4 [idle, validated]
                 └── ... up to max=20
                 └──► PostgreSQL (20 real connections)

  External Pool (PgBouncer):
  100 App Pods (each: HikariCP max=5) → 500 app-side connections
       ↓
  PgBouncer (pools to 100 server-side connections)
       ↓
  PostgreSQL (max_connections=120)

  PgBouncer Pooling Modes:
  Session mode:    1 server conn per client session     (full compat)
  Transaction mode: 1 server conn per transaction       (best multiplex)
  Statement mode:  1 server conn per statement          (limited use)

  Pool Sizing Formula (Hikari rule of thumb):
  pool_size = (core_count * 2) + effective_spindle_count
  Example: 4-core machine, SSD (1 spindle equivalent):
  pool_size = (4 * 2) + 1 = 9

  Connection Leak Detection (HikariCP):
  leakDetectionThreshold=60000  # warn if connection held > 60s
  connectionTimeout=30000        # fail after 30s waiting for connection
  idleTimeout=600000             # return idle connections after 10min
  maxLifetime=1800000            # replace connections after 30min (avoids stale)

  Symptoms of Pool Exhaustion:
  - Thread blocks on getConnection() → P99 latency spikes
  - "Connection is not available, request timed out after 30000ms" errors
  - Database shows N connections all ACTIVE (none idle)
          

Pros and cons

Implementation notes

Sizing the pool: The naive assumption is "more connections = more throughput." This is wrong. Beyond the optimal pool size, more connections increase lock contention and context-switch overhead, reducing throughput. The Hikari team's recommended formula is pool_size = (core_count × 2) + effective_spindle_count. For an I/O-bound workload on a 4-core machine with SSD storage, this yields approximately 9 connections. For heavily I/O-bound workloads (slow queries, lots of network waits), you can increase beyond this formula, but monitor actual throughput — it should plateau or decline past the optimal point. Keep minimumIdle equal to maximumPoolSize (the HikariCP recommendation) to avoid the overhead of pool warmup on traffic bursts.

PgBouncer modes: Use transaction mode for read-heavy workloads with short transactions — it provides the best connection multiplexing (many application connections share few server connections). Use session mode when your application uses session-level features (prepared statements, advisory locks, SET LOCAL, LISTEN). Never use statement mode in production — it breaks multi-statement transactions. When using transaction mode, disable client-side prepared statements in your JDBC driver (prepareThreshold=0 in pgjdbc) because PgBouncer transaction mode routes each transaction to potentially a different server connection, making server-side prepared statement handles invalid.

Common failure modes

• Pool exhaustion under load: All pool connections are held by long-running queries or transactions. New requests block until timeout. Symptoms: database shows all connections ACTIVE, application shows connection timeout errors. Fix: reduce pool size to match database capacity, optimize slow queries, set statement_timeout on long-running queries.
• Connection leak: A code path acquires a connection but fails to return it (uncaught exception bypassing connection close, missing try-with-resources block). The pool slowly shrinks until exhausted. Fix: use leakDetectionThreshold in HikariCP; use try-with-resources for all connection usage.
• Stale connections after database restart: All pooled connections become invalid when the database restarts. Applications receive "broken pipe" errors. Fix: set maxLifetime less than any server-side idle timeout; configure connection validation (connectionTestQuery or keepaliveTime).
• Serverless cold start connection storm: 500 Lambda functions cold-start simultaneously after a traffic spike; each creates a new connection, exhausting max_connections. Fix: use RDS Proxy (AWS) or PgBouncer as an external pool that mediates Lambda connections.

Decision checklist

• Pool size has been set based on the sizing formula and load testing, not an arbitrary large number.
• Connection leak detection is enabled (leakDetectionThreshold in HikariCP).
• Connection timeout (connectionTimeout) is set to fail fast rather than block indefinitely.
• maxLifetime is set below the database server's idle_in_transaction_session_timeout and any firewall idle timeout.
• For PgBouncer transaction mode: client-side prepared statements are disabled and session-level feature compatibility is verified.
• The total connection count (pool_size × application_instances) does not exceed the database's max_connections limit.
• Pool metrics (active connections, pending connections, connection wait time) are monitored and alerted on.

Example use cases

• Multi-tenant SaaS API: 50 API pods each have a HikariCP pool of max=10, targeting a PostgreSQL primary instance. Total connections = 500, exceeding PostgreSQL's safe max_connections=200. PgBouncer is added in transaction mode, allowing the 500 app-side connections to multiplex through 150 server-side connections, keeping PostgreSQL healthy.
• Serverless data pipeline: AWS Lambda functions process S3 events and write to Aurora PostgreSQL. Without pooling, 1,000 concurrent invocations create 1,000 connections. RDS Proxy is deployed: Lambda connects to RDS Proxy's JDBC endpoint, which maintains a pool of 100 connections to Aurora, multiplexing all Lambda invocations transparently.
• Microservice with HTTP client pool: A Node.js microservice makes frequent HTTP calls to a downstream REST API. HTTP keep-alive connection pooling (via axios with http.Agent({keepAlive: true, maxSockets: 50})) reuses TCP connections, reducing per-request TCP handshake overhead from ~10ms to near-zero for subsequent requests to the same host.

Related patterns

• Request Throttling — the pool size is effectively a concurrency throttle; pool exhaustion is the same as semaphore exhaustion in request throttling.
• Read Replicas — read replica connection pools must be managed separately from the primary pool; routing reads to replicas splits the connection load.
• Bulkhead Pattern — separate connection pools per service or feature class are a direct implementation of the bulkhead pattern.

Further reading

• HikariCP — About Pool Sizing (the definitive pool size guide)
• PgBouncer — Features and Pooling Modes
• AWS RDS Proxy — Serverless Connection Pooling