Design a Connection Pool Manager in Java

Why a pool

Opening a connection is 50-200ms (TCP + TLS + DB auth). A request that takes 10ms can't afford to do that per call. Pool keeps pre-established connections ready, loans them out, returns on close.

Core data structure

• ArrayBlockingQueue for available connections — bounded, blocking poll(timeout), FIFO
• ConcurrentHashMap.newKeySet() for in-use connections — tracking for leak detection and shutdown
• PooledConnection wrapper: holds physical Connection, tracks borrow time + last-used time, wraps close() to return to pool instead of physically closing

HikariCP's optimization: ConcurrentBag — thread-local cache + steal queue for near-lock-free borrow/return at extreme throughput. For an interview answer, ArrayBlockingQueue is fine; mention HikariCP's trick to show depth.

Pool sizing — Little's Law, NOT 'more = better'

Naive 'more is better' is wrong. Oversized pools hit DB connection limits, cause DB-side context-switch thrashing, waste memory (10MB per connection on Postgres), and worsen tail latency from contention.

Little's Law: pool size = arrival rate × service time.

• 1000 QPS × 10ms = 10 concurrent = pool 10
• 1000 QPS × 50ms = 50

Add headroom: pool = concurrent + sqrt(concurrent).

HikariCP authors recommend 10-20 typically. DB processes one query per CPU core efficiently; more concurrent queries don't help.

Per-instance: 10 instances × pool of 10 = 100 connections; Postgres max_connections=200 leaves 100 for ops.

When apps multiply: PgBouncer or AWS RDS Proxy multiplexes thousands of app connections onto small DB connection set.

Borrow / return

Connection getConnection() {
    long deadline = nanoTime() + acquireTimeout;
    while (true) {
        PooledConnection pc = available.poll();
        if (pc == null) {
            if (inUse.size() < maxSize) pc = createNew();      // grow on demand
            else pc = available.poll(deadline - now);          // wait
            if (pc == null) throw new SQLException("acquire timeout");
        }
        if (validateIfNeeded(pc)) {
            pc.markBorrowed();
            inUse.add(pc);
            return pc;
        }
        destroy(pc);     // bad connection, loop
    }
}

Key choices:

• Lazy validation — only validate connections idle > 30s; fresh-from-pool trusted
• Grow on demand to maxSize — don't pre-create everything
• Validate failure → destroy + retry, don't fail borrower for one bad connection
• Acquire timeout prevents hang under DB outage

Connection lifecycle

Validate:

• isValid(1) — driver health check, fast
• Test query 'SELECT 1' — fallback if isValid not implemented

When:

• On borrow if idle > N seconds (HikariCP default)
• Periodic background sweeper for broken/old connections

Evict:

• idleTimeout (10 min): shrink below max idle
• maxLifetime (30 min): prevents long-lived state accumulation and NAT timeouts
• Broken: query failure marked it bad

maxLifetime < DB wait_timeout is critical. MySQL default wait_timeout = 8h, AWS NAT timeout = 350s. Always defend.

Leak detection — the #1 production problem

Forgotten close() (missing try-with-resources). Pool drains until everything blocks.

Detection: record borrowedAt + thread stack trace at borrow. Background sweeper scans inUse; if held > leakThreshold (30s typical), log warning with the borrow stack — finds the bug quickly.

Hard policy: after longer threshold (5 min), forcibly evict from inUse, mark leaked, log critical, replace. Eventual close() ignored.

HikariCP's leakDetectionThreshold does exactly this. Recommended 15-30s in production.

Prevention beats detection: try-with-resources for every connection. Spring @Transactional handles it automatically.

LLD

• ConnectionPool facade: getConnection, shutdown, stats
• PooledConnection wrapper: physical connection + borrow tracking + close-overrides
• ConnectionFactory interface (JdbcConnectionFactory impl) — mockable for tests
• HouseKeeper: background tasks (evict idle, evict old, detect leaks, refill min idle)
• PoolStats: active, idle, wait time P99, timeout count

HikariCP config cheat sheet

minimumIdle: 0-10
maximumPoolSize: 10
connectionTimeout: 5000ms
idleTimeout: 600000ms
maxLifetime: 1800000ms (< DB wait_timeout)
leakDetectionThreshold: 30000ms

Failure scenarios + observability

• DB down → borrow times out → 503 → circuit breaker trips
• DB slow → pool drains; borrowers wait or timeout; alert
• Network blip → broken connection returned, evicted, replaced
• Cold start storm → ramp minimumIdle slowly

Metrics: active/idle counts, acquire wait P99, acquire timeout count (alert >0), leaked count (must be 0 steady-state), connection creation rate (high = churn).

Recommendation

Use HikariCP. Don't roll your own. Configure: small pool (10-20 per instance) sized via Little's Law, maxLifetime under DB wait_timeout, leak detection at 30s, lazy validation only for idle-too-long connections. Wrap every connection in try-with-resources or rely on Spring @Transactional. For multi-instance scaling beyond DB connection limits, layer PgBouncer or AWS RDS Proxy. The interview answer is to demonstrate you understand what HikariCP does under the hood — ArrayBlockingQueue + lazy validation + leak detection + Little's-Law sizing.