Explain Connection Pooling in Databases

14 / 66

performance

Connection Pooling

Reuse a fixed set of physical DB connections — opening new ones per request is too expensive and the DB has a hard limit.

Opening a database connection is surprisingly expensive — a TCP handshake, authentication, TLS negotiation, and session setup — often tens of milliseconds, which is an eternity to pay on every request. Worse, a database can only sustain a limited number of concurrent connections, each consuming memory and a backend process or thread, so letting every request open its own connection both wastes time and threatens to overwhelm the server. Connection pooling solves both problems by maintaining a fixed set of already-open physical connections that requests borrow and return.

A taxi rank of pre-warmed cars vs calling for a new car each time. Way faster, but only if you size the rank correctly.

Key Concepts

A pool — HikariCP is the de facto standard in the Java world — opens a configured number of connections at startup and keeps them alive. When code needs the database it borrows a connection from the pool, uses it, and returns it (ideally via try-with-resources, so "closing" really means returning it to the pool rather than tearing it down). The expensive setup is paid once per physical connection and amortised across thousands of requests. The pool also acts as a throttle: its maximum size caps how many database operations run concurrently, protecting the database from overload, and requests that arrive when all connections are busy wait briefly for one to free up rather than piling unbounded load onto the server.

Sizing the pool is the part interviews focus on, and the counter-intuitive truth is that bigger is not better. A pool far larger than the database's core count just creates contention and context-switching; a common starting formula is roughly (cores × 2) plus effective spindle count, then tuned by measurement. Too small a pool starves the application and shows up as connection-acquisition timeouts; too large overwhelms the database. Other essentials are setting sensible connection and validation timeouts, watching for connection leaks (borrowed connections never returned, which slowly exhaust the pool), and ensuring the pool's maximum stays within the database's connection limit across all application instances.