Database Connection Pool Exhaustion

When the bouncer stops letting people into the club.

The idea

Opening a TCP connection to a database is extremely slow (SSL handshakes, authentication, etc). To keep web requests fast, the App Server maintains a "Pool" of pre-opened connections. When a web thread needs the DB, it borrows one. But if a traffic spike hits, and 500 web threads ask for a connection, but the pool only has 50... 450 threads get stuck in a queue. If they wait too long, they time out, and your users see a 502 Bad Gateway.

How it works (The Wait Queue)

Connection Pools (like HikariCP) use Semaphores. When the pool is empty, threads block (sleep) until another thread returns its connection. To prevent infinite hanging, pools enforce a connectionTimeout. If a thread waits longer than this, the pool throws an exception.

// Example: HikariCP Configuration
HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:postgresql://localhost/mydb");

// The maximum number of physical connections to keep open
config.setMaximumPoolSize(50); 

// How long a thread will wait in the queue before crashing
config.setConnectionTimeout(5000); // 5 seconds

Cost

Why not just set MaximumPoolSize to 10,000? Because Databases (especially Postgres) allocate a heavy chunk of RAM and a dedicated OS process for every connection. If you open 10,000 connections, the Database will run out of memory, crash, and take down the entire company. A small, saturated pool is better than a dead database.

Watch out for

• Missing Indexes: The most common cause of Exhaustion isn't high traffic; it's slow queries. If a query takes 10ms, a connection serves 100 requests per second. If you miss an index and the query takes 10 seconds, that connection serves 0.1 req/s. The pool drains instantly.