Connection Pooling

Build a connection pool as code: a bounded set of reusable connections, a wait with a timeout, validation, lifetime limits, and the sizing math from HikariCP and PostgreSQL.

Overview

Opening a database connection is several network round trips: the TCP handshake, usually a TLS handshake, then authentication and session setup on the server. Doing that for every query would spend more time connecting than querying, and the server would hold a session for every request in flight. A connection pool opens a small number of connections once, hands them out for the length of one query or transaction, and takes them back.

The interview question looks like a data-structure exercise, and the first half is: a bounded set of objects, a blocking acquire with a timeout, a release. The second half is where the marks are: what happens when the pool is empty, how you notice a leaked connection, why a connection that sat idle for an hour may be dead, and how many connections a database can actually use. This topic builds the pool in Python and takes the numbers from HikariCP's and PostgreSQL's own documentation.

What the pool does per request

Connect — TCP, TLS, authenticate, set up the session, every request — Once per connection, at startup or first use
Run the query — Same — Same
Close — Tear down the session and the socket — Return the connection; it stays open
Sessions the server holds — One per in-flight request, unbounded — At most the pool's maximum

A connection's life in the pool

A trace with a maximum of two connections and an acquire timeout of 1 second. Two threads, A and B, and a third, C, that has to wait.

0.0 — A — acquire — none — c1 — Pool empty and under the maximum: opens c1
0.0 — B — acquire — none — c1, c2 — Opens c2; the pool is now full
0.1 — C — acquire — none — c1, c2 — At the maximum: waits up to 1 s
0.3 — A — release c1 — c1 — c2 — C is woken and takes c1
0.3 — C — got c1 — none — c1, c2 — Waited 0.2 s
1.5 — B — release c2 — c2 — c1
1.5 — B — acquire — none — c1, c2 — Reuses c2: no handshake

The pool as code

Everything sits behind one lock and one condition variable. acquire reuses an idle connection, opens a new one if under the maximum, or waits; release puts the connection back and wakes one waiter. The clock and the connection factory are injected so the tests are exact.

The waiting thread in the trace needs real threads to show. Eight workers share a pool of three; the assertion is that no more than three connections ever exist and every worker finishes.

How many connections

The tempting answer is 'as many as the database allows'. The pool-sizing page of HikariCP argues the opposite: a database core runs one query at a time, so past a small multiple of the core count, extra connections only add context switches and lock contention. It quotes the PostgreSQL project's starting formula, connections = cores × 2 + effective spindles, and an Oracle demonstration where cutting a pool from 2,048 connections to 96 took response time from about 100 ms to about 2 ms.

Starting formula — cores × 2 + effective spindles (SSDs count as few) — PostgreSQL project, quoted by HikariCP
PostgreSQL max_connections default — 100 (lower if the kernel cannot support it) — PostgreSQL docs
Of which reserved for superusers — 3 (superuser_reserved_connections) — PostgreSQL docs
HikariCP maximumPoolSize default — 10 — HikariCP README
HikariCP minimumIdle default — Equal to maximumPoolSize (a fixed-size pool, which HikariCP recommends) — HikariCP README
PgBouncer default_pool_size — 20 server connections per user and database pair — PgBouncer docs

The formula is per database, not per application server. Ten servers each holding 9 connections is 90 sessions on one PostgreSQL, close to the default cap. That is why a server-side pooler such as PgBouncer sits between many application pools and one database.

Timeouts and lifetimes

Every pool has several clocks, and mixing them up is a common interview slip. HikariCP's defaults are given as anchors.

connectionTimeout — How long may acquire wait before failing? — 30 seconds
idleTimeout — How long may a connection sit idle before it is closed? (Only when minimumIdle is below maximumPoolSize) — 10 minutes
maxLifetime — How old may a connection get before it is retired, even if healthy? — 30 minutes; set it a few seconds under any limit the database or network imposes
validationTimeout — How long may the aliveness check take? — 5 seconds
leakDetectionThreshold — After how long out of the pool is a connection reported as leaked? — 0, disabled
keepaliveTime — How often is an idle connection pinged so the network does not drop it? — 2 minutes

What you will be able to do

Implement acquire, release, a bounded size and a wait with a timeout, and trace them with a frozen clock.
Explain why a pool of 9 can beat a pool of 200, and do the sizing arithmetic.
Handle broken and expired connections without the caller noticing.
Detect and prevent pool exhaustion and connection leaks.
Place a server-side pooler such as PgBouncer and say what transaction pooling breaks.

Interview questions

Design a connection pool. What is the core data structure?
How do you size a connection pool correctly?
What is connection pool exhaustion, and how do you detect and prevent it?
How do you handle stale connections that the database or network has broken?
Where does a server-side pooler such as PgBouncer fit, and what does transaction pooling break?