Skip to content

Connection Pooling

1. What is it? (2-3 lines)

Connection Pooling is a data structure pattern that maintains a warm, reusable cache of active database or HTTP socket connections, allowing application threads to check out an established connection, execute queries, and return it to the pool instead of creating and destroying connections on every request.

2. Why does it exist?

Creating a database connection is an expensive operation involving TCP 3-way handshake, TLS negotiation, authentication, process spawning (Postgres), and backend memory allocation (\(10-100\text{ms}\) latency penalty). Connection pools amortize connection creation overhead, bounding max concurrent connections to prevent database memory exhaustion.

3. Where is it used?

  • Relational Database Drivers: HikariCP (Java default), pgx (Go), SQLAlchemy (Python), npm pg-pool.
  • Database Proxies: PgBouncer (PostgreSQL connection pooler), ProxySQL (MySQL).
  • HTTP & gRPC Clients: Go http.Client, Apache HttpClient, gRPC Channel Pools.

4. How does it work? (simple flow)

  1. Pool Initialization: At app startup, pool creates minIdle warm established connections (e.g. 10 connections).
  2. Checkout (Borrow): App thread calls pool.getConnection() \(\to\) Pool checks out an available idle connection in \(<1\text{ms}\).
  3. Execution: Thread executes SQL query over the borrowed connection.
  4. Return (Release): Thread calls connection.close() \(\to\) Driver does NOT close TCP socket; it resets connection state and returns connection to pool array for immediate reuse by other threads!
  5. Pool Bounds: If all connections are in use, new checkout requests block for connectionTimeout (e.g. 3000ms); if exceeded, throws ConnectionTimeoutException.

5. Advantages

  • Eliminates 10-100ms connection creation latency overhead for application requests.
  • Protects target databases from crashing due to connection explosion (max_connections exceeded).
  • Reuses established TCP/TLS sockets efficiently across high QPS web worker threads.

6. Limitations

  • Thread Starvation / Pool Exhaustion: If connections are held too long by slow queries, application threads block waiting for connections, leading to API timeouts.
  • Idle Connection Eviction: Stateful firewalls drop idle TCP connections; pool must run periodic validation checks (testOnBorrow / keepalive).

7. Common interview questions (10)

  1. Why is opening a new database connection per HTTP request considered a critical production anti-pattern?
  2. How does HikariCP achieve sub-millisecond connection checkout performance using FastList and lock-free byte arrays?
  3. How do you size a database connection pool using the HikariCP formula (\(\text{Connections} = (\text{CPU Cores} \times 2) + \text{Disk Count}\))?
  4. Why is a connection pool of 20 connections often faster than a pool of 200 connections on a 16-core database server?
  5. How does PgBouncer act as a middle-tier connection pooler for PostgreSQL?
  6. What is the difference between Session Pooling, Transaction Pooling, and Statement Pooling in PgBouncer?
  7. What happens when application code forgets to close a checked-out connection (Connection Leak)?
  8. How does connectionTimeout differ from idleTimeout and maxLifetime in connection poolers?
  9. Why must maxLifetime be set 30-60 seconds shorter than firewall or load balancer idle connection timeouts?
  10. How do HTTP Client connection pools manage per-host max connections (maxSockets)?

8. Common misconceptions

  • Misconception: Larger connection pools are always better. Fact: Oversized connection pools cause context-switching thrashing and disk I/O contention on the database; smaller pools (\(2 \times \text{CPU cores}\)) yield far higher QPS.
  • Misconception: Calling connection.close() terminates the TCP connection. Fact: In a pooled environment, close() returns the connection to the pool data structure.

10. 30-second cheat sheet

  • Purpose: Maintain warm array of active DB/HTTP sockets. Borrow on query \(\to\) Return on completion.
  • HikariCP Pool Sizing Formula: \(\text{Pool Size} = (\text{CPU Cores} \times 2) + \text{Effective Spindle Count}\).
  • Postgres Pooler: Use PgBouncer in Transaction Pooling mode for high-concurrency microservices.
  • Critical Setting: Always set maxLifetime slightly shorter than infrastructure network idle timeouts!