Database Access
Connection Pooling
Technique to cache database connections for reuse, improving performance by avoiding connection overhead.
Interview: Performance tuning — understanding how connection pools work under load is critical for building scalable applications.
Database connections are expensive to establish. Handshaking, authentication, and setting up sockets take significant time. Connection Pooling solves this by keeping a pool of active database connections open and ready for reuse by the application.
How Connection Pools Work
Instead of opening a new connection for every query, the application requests a connection from the pool. Once the query completes and the session is closed, the connection is returned to the pool instead of being closed. This reduces connection overhead to nearly zero.
Key Configuration Parameters
- Pool Size: The minimum/standard number of connections kept open in the pool.
- Max Overflow: The maximum number of temporary connections allowed above the standard pool size when traffic spikes.
- Pool Timeout: The number of seconds the application will wait to get a connection from the pool before throwing an exception.
- Pool Recycle: Periodically closes and recreates connections to prevent stale connections (e.g. databases dropping connections inactive for too long).
Interview Insight
Connection pool exhaustion is a common production issue. If your pool size is 5 and you get 10 concurrent requests that take a long time, the remaining 5 requests will block. If they exceed the pool timeout, they will crash. Adjust pool parameters according to concurrent database usage and max db server limits.
Use Cases
Web APIs — Multi-threaded web servers reusing a small set of open connections to handle thousands of requests per minute.
Long-running Services — Background worker scripts periodically querying databases without leaking system sockets.
Production Databases — Safeguarding the database server from being overloaded with thousands of raw connection handshakes.
Common Mistakes
Configuring pool size too high — Overwhelming the database server maximum connection limits (max_connections in Postgres/MySQL).
Connection leaks — Forgetting to close connections, resulting in connections staying "checked out" forever and starving the pool.
Not recycling connections — Keeping old connections open until the database server forcibly terminates them, causing random connection reset exceptions.