Connection Pooling
Practice this topic in a realistic system design interview
Database connections are not free.
When an application connects to a database, both sides have work to do. They may need to open a network connection, check security, authenticate the user, create session state, and reserve memory or a worker on the database server.
Doing all of that for every request is wasteful. For small queries, opening the connection can take more time than running the query itself. Under heavy traffic, too many new connections can hurt the database before the queries become the real problem.
Connection pooling fixes this by keeping a small group of database connections open and reusing them across requests.
The pool size matters. It is not just a speed setting. It also limits how much database work the application can send at the same time.
In this chapter, we will look at how connection pooling works, how to size a pool, and how it protects the database during traffic spikes.
1. The Problem Without Pooling
Without pooling, every request may repeat the full connection setup:
- Open a TCP connection.
- Negotiate TLS if enabled.
- Authenticate with the database.
- Allocate database-side session state.
- Run the query.
- Close the connection.
For one request, this may be fine. At scale, it becomes painful.
Problems include:
- Slower responses: opening the connection may take longer than the query.
- More database work: every connection uses memory and server resources.
- Connection storms: a traffic spike can create a sudden flood of new connections.
- Resource leaks: forgotten cleanup can leave connections stuck.
- Connection limits: the database rejects new clients after it reaches its limit.
The database can only do so much work at once. Creating thousands of connections does not make it run thousands of queries efficiently. It usually creates waiting, lock waits, memory pressure, and slower responses.