Skip to content

Connection Pooling#

Problem statement (interviewer prompt)

A web service opens a new TCP+TLS+auth handshake to its database on every request, costing 50ms+ before any query runs. Design a connection-management strategy that amortizes the handshake cost without exhausting database resources.

A pool is a bounded set of pre-established connections that requests borrow, use, and return. New connections are expensive (TCP handshake, TLS, authentication, schema fetch); pools amortize that cost over many requests.

flowchart LR
  R1([Request]) -->|acquire| P[(Pool<br/>max=20)]
  R2([Request]) -->|acquire| P
  P -->|conn 1| DB[(Database)]
  P -->|conn 2| DB
  R1 -. release .-> P
  R2 -. release .-> P
  R3([Request]) -.queue if pool full.-> P

    classDef client fill:#dbeafe,stroke:#1e40af,stroke-width:1px,color:#0f172a;
    classDef cache fill:#fed7aa,stroke:#9a3412,stroke-width:1px,color:#0f172a;
    classDef datastore fill:#fee2e2,stroke:#991b1b,stroke-width:1px,color:#0f172a;
    class R1,R2,R3 client;
    class P cache;
    class DB datastore;

HikariCP (Java), pgBouncer (Postgres), ProxySQL (MySQL), and HTTP keep-alive are all instances of the same pattern at different layers.

A connection pool turns "open and close" into "borrow and return". Sized correctly, it's the cheapest path to absorbing load; sized poorly, it's the cause of the next outage.

Why pool at all#

Per-connection setup costs:

Cost Typical
TCP handshake 1 RTT
TLS handshake 1-2 RTT
Database auth 1 round trip + bcrypt
Session prepare per-driver
Total 50-200ms for a fresh DB connection

With pooling and 1000 req/s, only the first 20 requests pay this; the rest see microseconds.

Sizing with Little's law#

ConnectionsNeeded = ThroughputPerSecond × MeanServiceTimeSeconds

For 500 req/s and 20ms mean DB time: 500 × 0.020 = 10 connections at the application. Add headroom for variance: ~20.

The classic mistake: setting maxPoolSize = 200 because "more is faster". The DB has a hard concurrency limit; exceeding it causes context-switch storms and the latency cliff.

flowchart TB
  subgraph Healthy
    L1[Latency] -.flat.-> Conc1[Concurrency=10]
  end
  subgraph Cliff
    L2[Latency] -.spike.-> Conc2[Concurrency=200<br/>past DB CPU]
  end

    classDef client fill:#dbeafe,stroke:#1e40af,stroke-width:1px,color:#0f172a;
    classDef edge fill:#cffafe,stroke:#0e7490,stroke-width:1px,color:#0f172a;
    classDef service fill:#fef3c7,stroke:#92400e,stroke-width:1px,color:#0f172a;
    classDef datastore fill:#fee2e2,stroke:#991b1b,stroke-width:1px,color:#0f172a;
    classDef cache fill:#fed7aa,stroke:#9a3412,stroke-width:1px,color:#0f172a;
    classDef queue fill:#ede9fe,stroke:#5b21b6,stroke-width:1px,color:#0f172a;
    classDef compute fill:#d1fae5,stroke:#065f46,stroke-width:1px,color:#0f172a;
    classDef storage fill:#e5e7eb,stroke:#374151,stroke-width:1px,color:#0f172a;
    classDef external fill:#fce7f3,stroke:#9d174d,stroke-width:1px,color:#0f172a;
    classDef obs fill:#f3e8ff,stroke:#6b21a8,stroke-width:1px,color:#0f172a;

PgBouncer pooling modes#

PostgreSQL connections are expensive (one process per connection). PgBouncer adds a middle pool:

flowchart LR
  App1[App pod 1] -->|10 client conns| PB[PgBouncer]
  App2[App pod 2] -->|10 client conns| PB
  App3[App pod 3] -->|10 client conns| PB
  PB -->|20 server conns| PG[(Postgres)]

    classDef service fill:#fef3c7,stroke:#92400e,stroke-width:1px,color:#0f172a;
    classDef edge fill:#cffafe,stroke:#0e7490,stroke-width:1px,color:#0f172a;
    classDef datastore fill:#fee2e2,stroke:#991b1b,stroke-width:1px,color:#0f172a;
    class App1,App2,App3 service;
    class PB edge;
    class PG datastore;
Mode What's pooled Restriction
Session Whole session safest; pool size = max concurrent sessions
Transaction Per transaction no session-scoped features (prepared stmts, temp tables)
Statement Per statement autocommit only

Transaction pooling is the production default for serverless and high-fanout apps.

HTTP / gRPC pooling#

session = requests.Session()  # reuses TCP+TLS
session.mount("https://", HTTPAdapter(pool_connections=20, pool_maxsize=20))
Layer Reuse mechanism
HTTP/1.1 Connection: keep-alive
HTTP/2 Single connection multiplexed
gRPC HTTP/2 connection per channel; per-request stream
TCP Long-lived socket inside the pool

Failure modes#

Connection leak#

A request borrowed a connection and didn't return it (exception path skipped close()). Pool drains, all requests block, service hangs.

# wrong: leak on exception
conn = pool.acquire()
do_work(conn)
pool.release(conn)

# right
with pool.acquire() as conn:
    do_work(conn)

Stale connections#

Idle connections die silently (firewall TCP timeout, DB restart). The next borrow gets an error mid-query. Mitigations:

  • TCP keep-alive (SO_KEEPALIVE).
  • Application-level validationQuery (SELECT 1) before use.
  • Maximum lifetime (HikariCP maxLifetime = 30min).

Thundering herd on cold start#

Container starts, every pod opens N connections at once. DB CPU spikes. Use jittered warm-up.

Pool topology at scale#

flowchart TB
  subgraph App Tier
    A1[App 1] --> P1[Local pool]
    A2[App 2] --> P2[Local pool]
  end
  P1 --> PB[PgBouncer]
  P2 --> PB
  PB --> Primary[(Primary)]
  PB --> Replica[(Read replica)]

    classDef service fill:#fef3c7,stroke:#92400e,stroke-width:1px,color:#0f172a;
    classDef edge fill:#cffafe,stroke:#0e7490,stroke-width:1px,color:#0f172a;
    classDef datastore fill:#fee2e2,stroke:#991b1b,stroke-width:1px,color:#0f172a;
    class A1,A2 service;
    class P1,P2,PB edge;
    class Primary,Replica datastore;

Two-tier pooling lets you have 1000s of app pods with only 100 DB connections - critical for serverless and Kubernetes auto-scaling.

Where pooling shows up#

flowchart TB
  CP((Connection<br/>pooling))
  LB[Load balancer<br/>HTTP pool sibling]
  RES[Resilience patterns<br/>timeout / circuit-break]
  BP[Backpressure<br/>queue in front of pool]
  CAP[Capacity planning<br/>Little's law sizing]
  CP --> LB
  CP --> RES
  BP --> CP
  CAP --> CP

    classDef client fill:#dbeafe,stroke:#1e40af,stroke-width:1px,color:#0f172a;
    classDef edge fill:#cffafe,stroke:#0e7490,stroke-width:1px,color:#0f172a;
    classDef service fill:#fef3c7,stroke:#92400e,stroke-width:1px,color:#0f172a;
    classDef datastore fill:#fee2e2,stroke:#991b1b,stroke-width:1px,color:#0f172a;
    classDef cache fill:#fed7aa,stroke:#9a3412,stroke-width:1px,color:#0f172a;
    classDef queue fill:#ede9fe,stroke:#5b21b6,stroke-width:1px,color:#0f172a;
    classDef compute fill:#d1fae5,stroke:#065f46,stroke-width:1px,color:#0f172a;
    classDef storage fill:#e5e7eb,stroke:#374151,stroke-width:1px,color:#0f172a;
    classDef external fill:#fce7f3,stroke:#9d174d,stroke-width:1px,color:#0f172a;
    classDef obs fill:#f3e8ff,stroke:#6b21a8,stroke-width:1px,color:#0f172a;
    class CP service;
    class LB,RES,BP,CAP datastore;

Glossary & fundamentals#

Tag Concept What it is Page
HLD Load balancer sibling pool for HTTP backends load-balancer
HLD Resilience patterns timeouts and circuit breakers around pool resilience-patterns
HLD Backpressure the queue in front of a saturated pool backpressure
HLD Capacity planning sizing pool with Little's law capacity-planning

Quick reference#

Why#

Amortize 50-200ms setup (TCP+TLS+auth+session) across many requests.

Sizing#

pool_size = throughput × mean_service_time + headroom
Example: 500 req/s × 20ms = 10 → set max 20.

Too big: DB context-switch storm; latency cliff.

PgBouncer modes#

Mode Use
Session Safest; prepared stmts work
Transaction Default for serverless / fanout
Statement Autocommit only

Failure modes#

  • Leak: missing release() in exception path → use with / try-finally
  • Stale: idle conn killed by firewall → keep-alive + validation query
  • Herd: cold-start mass acquire → jittered warm-up

Tuning checklist#

  • maxLifetime < firewall idle timeout
  • validationTimeout > network jitter
  • connectionTestQuery = SELECT 1
  • Health-check endpoint exercises a pool acquire

Layers#

Layer Mechanism
HTTP/1.1 keep-alive
HTTP/2 multiplexed connection
gRPC channel + stream
DB HikariCP, PgBouncer, ProxySQL

Refs#

  • HikariCP pool sizing wiki
  • PgBouncer docs
  • DDIA - chapter on transactions

FAQ#

What is connection pooling?#

Connection pooling reuses a bounded set of pre-established database connections across requests, amortizing the TCP, TLS, and authentication handshake that would otherwise add 50ms+ per query.

How do I size a database connection pool?#

Apply Little's Law: pool size equals expected query rate times mean query time, plus a small buffer. For most apps the right answer is far smaller than people think, often 10 to 20 connections per node.

What is the difference between HikariCP and PgBouncer?#

HikariCP is an in-process JDBC pool that lives inside the application. PgBouncer is an external Postgres proxy that pools connections across many application instances, useful when you have hundreds of app nodes.

What is connection pool exhaustion?#

Exhaustion happens when all pooled connections are in use and new requests block waiting. Causes include long-running queries, leaks where code forgets to return connections, or undersized pools.

When should I use PgBouncer transaction pooling?#

Use transaction pooling when you have many short-lived app instances (serverless or many pods) that each need few connections. It lets thousands of clients share a small Postgres backend pool.

Further reading#