Definition
- Connection pool exhaustion – A cascading failure where slow queries hold their database connections far longer than normal, draining the finite pool so unrelated fast requests block waiting to acquire a connection.
- Symptom signature – High latency and request pile-up across the whole application, with low database CPU, because the bottleneck is connection availability, not query compute.
- Root cause – A query that used to run in 50 ms now takes 10 seconds due to a missing index, stale statistics, a lock wait, or data growth.
- Key principle – The pool is a small shared resource; protecting its availability protects the entire application.
The National Bank of India branch has only ten teller windows (ten connections). Normally each customer is served in fifty seconds and the queue flows. One day a single customer's reconciliation takes ten minutes at a window. One by one, slow customers occupy all ten windows, and now Rajesh, who just wants a fifty-second balance check, stands in a frozen queue. The branch is not out of money or clerks: it is out of free windows.
Understanding the Concepts
What is connection pool exhaustion?
Connection pool exhaustion happens when all connections in a fixed-size pool are borrowed and held for too long, leaving no connections available for new requests. A pool like HikariCP keeps a small number of connections, typically 5 to 20, because each connection is expensive on the database side. Every incoming request borrows a connection, runs its query, and returns it. When queries are fast, ten connections easily serve thousands of requests per second because each is held for only milliseconds.
package com.bank.config;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import javax.sql.DataSource;
public class DataSourceConfig {
public DataSource createPool() {
HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:postgresql://localhost:5432/nbi_ledger");
config.setUsername("nbi_app");
config.setPassword("secret");
config.setMaximumPoolSize(10); // Only 10 connections
config.setConnectionTimeout(3000); // Wait max 3 seconds
config.setLeakDetectionThreshold(5000); // Warn if held > 5 seconds
return new HikariDataSource(config);
}
}
With maximumPoolSize of 10 and connectionTimeout of 3 seconds, a borrower gets a connection instantly when the pool is healthy. When all 10 are held, the next borrower waits up to 3 seconds and then fails.
Why does one slow query drag the whole application down?
The failure begins when one query's holding time explodes. If a query now takes 10 seconds instead of 50 milliseconds because of a missing index, stale statistics, or data growth, it holds its connection 200 times longer. A handful of such concurrent queries occupy every connection in the pool.
package com.bank.service;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.sql.DataSource;
public class StatementService {
private final DataSource dataSource;
public StatementService(DataSource dataSource) {
this.dataSource = dataSource;
}
// This query USED to be fast (50ms) with 100K rows
// After data grew to 10M rows and the index was dropped, it takes 10 seconds
public BigDecimal getMonthlyTotal(String accountId, String month) throws SQLException {
String sql = "SELECT SUM(amount) FROM transactions "
+ "WHERE account_id = ? AND txn_month = ?";
try (Connection conn = dataSource.getConnection(); // Holds connection 10 seconds!
PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setString(1, accountId);
ps.setString(2, month);
try (ResultSet rs = ps.executeQuery()) {
return rs.next() ? rs.getBigDecimal(1) : BigDecimal.ZERO;
}
}
}
}
Once all 10 connections are held by slow queries, every new request blocks. Even a trivial balance check that takes 1 millisecond cannot get a connection.
How does the cascade happen?
Once the pool is empty, HikariCP blocks up to connectionTimeout and then throws SQLTransientConnectionException: Connection is not available, request timed out after 3000ms. Now logs fill with timeout errors for fast queries that were never slow themselves. The application appears frozen, latency across all endpoints spikes, and requests pile up.
package com.bank.service;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.sql.DataSource;
public class BalanceService {
private final DataSource dataSource;
public BalanceService(DataSource dataSource) {
this.dataSource = dataSource;
}
// This query is fast (1ms) but it CANNOT get a connection
// because the pool is drained by slow StatementService queries
public BigDecimal getBalance(String accountId) throws SQLException {
String sql = "SELECT balance FROM accounts WHERE account_id = ?";
try (Connection conn = dataSource.getConnection()) { // BLOCKS here!
// Eventually throws: Connection is not available, request timed out
try (PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setString(1, accountId);
try (ResultSet rs = ps.executeQuery()) {
return rs.next() ? rs.getBigDecimal("balance") : BigDecimal.ZERO;
}
}
}
}
}
Rajesh's balance check is a victim. The database CPU is low because the connections are idle-waiting on a sequential scan, not computing.
How do you diagnose pool exhaustion?
Diagnosis starts with identifying the slow query and confirming the pool is the bottleneck.
-- Step 1: Check PostgreSQL for long-running queries
SELECT pid, now() - pg_stat_activity.query_start AS duration,
query, state
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '5 seconds'
ORDER BY duration DESC;
-- Step 2: Run EXPLAIN ANALYZE on the culprit
EXPLAIN ANALYZE
SELECT SUM(amount) FROM transactions
WHERE account_id = 'SAV2001' AND txn_month = '2024-06';
-- Look for: Seq Scan instead of Index Scan
// Step 3: Check HikariCP metrics
// hikariPool.getHikariPoolMXBean().getActiveConnections()
// hikariPool.getHikariPoolMXBean().getPendingThreads()
// If activeConnections == maximumPoolSize and pendingThreads > 0,
// the pool is exhausted
The HikariCP metric pendingThreads shows how many threads are waiting for a connection. A sustained non-zero value confirms exhaustion.
How do you fix the root cause?
Fix or add the right index so the query returns to milliseconds. This is always the most important step.
-- The query was doing a sequential scan on 10 million rows
-- Add a composite index matching the filter
CREATE INDEX idx_txn_acct_month
ON transactions(account_id, txn_month);
-- Verify with EXPLAIN ANALYZE
EXPLAIN ANALYZE
SELECT SUM(amount) FROM transactions
WHERE account_id = 'SAV2001' AND txn_month = '2024-06';
-- Now shows Index Scan, 2ms instead of 10 seconds
How do you add guardrails to prevent future exhaustion?
Even after fixing the current slow query, add guardrails so the next slow query cannot starve the pool.
package com.bank.config;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import javax.sql.DataSource;
public class ResilientDataSourceConfig {
public DataSource createPool() {
HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:postgresql://localhost:5432/nbi_ledger");
config.setMaximumPoolSize(10);
config.setConnectionTimeout(3000); // Fail fast: 3 seconds
config.setLeakDetectionThreshold(5000); // Warn if held > 5 seconds
// Add connection-level properties
config.addDataSourceProperty("socketTimeout", "10"); // Kill after 10s
return new HikariDataSource(config);
}
}
-- Database-side guardrail: kill any statement running longer than 5 seconds
ALTER DATABASE nbi_ledger SET statement_timeout = '5s';
package com.bank.service;
import org.springframework.transaction.annotation.Transactional;
import java.math.BigDecimal;
public class TransferServiceWithTimeout {
// Application-side guardrail: transaction timeout
@Transactional(timeout = 2) // 2 seconds max for this transaction
public void transfer(String from, String to, BigDecimal amount) {
// If the transfer takes longer than 2 seconds, it is rolled back
// and the connection is returned to the pool
}
}
What are the rules for keeping transactions short?
Never make external HTTP calls while holding a connection or a transaction open. The connection is held for the entire duration of the network call, which can be seconds.
package com.bank.service;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import javax.sql.DataSource;
public class LoanApprovalService {
private final DataSource dataSource;
private final CreditBureauClient creditClient;
public LoanApprovalService(DataSource dataSource, CreditBureauClient creditClient) {
this.dataSource = dataSource;
this.creditClient = creditClient;
}
// WRONG: external call inside the database connection scope
public void approveLoanBad(String accountId, BigDecimal amount) throws SQLException {
try (Connection conn = dataSource.getConnection()) {
// Connection is borrowed here
int score = creditClient.getScore(accountId); // 2 second HTTP call!
// Connection held idle for 2 seconds doing nothing DB-related
try (PreparedStatement ps = conn.prepareStatement(
"INSERT INTO loans(account_id, amount, score) VALUES(?, ?, ?)")) {
ps.setString(1, accountId);
ps.setBigDecimal(2, amount);
ps.setInt(3, score);
ps.executeUpdate();
}
}
}
// CORRECT: external call OUTSIDE the database connection scope
public void approveLoanGood(String accountId, BigDecimal amount) throws SQLException {
int score = creditClient.getScore(accountId); // HTTP call first, no connection held
try (Connection conn = dataSource.getConnection()) {
// Connection borrowed only for the fast INSERT
try (PreparedStatement ps = conn.prepareStatement(
"INSERT INTO loans(account_id, amount, score) VALUES(?, ?, ?)")) {
ps.setString(1, accountId);
ps.setBigDecimal(2, amount);
ps.setInt(3, score);
ps.executeUpdate();
}
}
}
}
interface CreditBureauClient {
int getScore(String accountId);
}
Why is making the pool larger NOT the right fix?
Sizing the pool larger only delays exhaustion and can overwhelm the database. If the query takes 10 seconds, doubling the pool to 20 just means 20 connections get stuck instead of 10. The database now has twice the overhead, and the problem reappears under slightly higher load.
Pool size 10, slow query 10s: 10 concurrent slow queries → pool exhausted in 1 batch Pool size 20, slow query 10s: 20 concurrent slow queries → pool exhausted in 1 batch Plus: database now has 20 backend processes consuming memory Pool size 10, query fixed to 50ms: 10 connections serve 200 fast queries/second each = 2000 qps Pool never exhausts because connections cycle quickly
The real fix is always reducing how long each connection is held.
Visual Overview
┌─────────────────────────────────────────────────────────────┐ │ Connection Pool Exhaustion Cascade │ ├─────────────────────────────────────────────────────────────┤ │ │ │ Normal State (queries take 50ms): │ │ ┌──────────────────────────────────────┐ │ │ │ Pool: [C1][C2][C3]...[C10] │ All available │ │ │ Request → borrow → query(50ms) → return │ │ │ 10 connections serve 2000+ qps │ │ │ └──────────────────────────────────────┘ │ │ │ │ Failure State (one query takes 10s): │ │ ┌──────────────────────────────────────┐ │ │ │ Pool: [BUSY][BUSY][BUSY]...[BUSY] │ All held │ │ │ Slow query holds C1 for 10 seconds │ │ │ │ Slow query holds C2 for 10 seconds │ │ │ │ ... │ │ │ │ Fast balance check → WAIT → TIMEOUT │ │ │ │ "Connection not available" │ │ │ └──────────────────────────────────────┘ │ │ │ │ Diagnosis: │ │ ┌──────────────┐ ┌───────────────┐ ┌────────────────┐ │ │ │ pg_stat │ │ HikariCP │ │ EXPLAIN │ │ │ │ _activity │ │ metrics │ │ ANALYZE │ │ │ │ (long-run) │→ │ (pending > 0) │→ │ (Seq Scan!) │ │ │ └──────────────┘ └───────────────┘ └────────────────┘ │ │ │ │ Fix: index → statement_timeout → transaction timeout │ └─────────────────────────────────────────────────────────────┘
Key Takeaway
One slow query drags the whole application down because the database connection pool is a small, shared, finite resource. Every request must borrow a connection from a pool of, say, ten. When queries are fast and held for fifty milliseconds, ten connections serve thousands of requests easily. But when one query starts taking ten seconds due to a missing index or data growth, it holds its connection 200 times longer, and a handful of such queries occupy every connection in the pool. After that, even Rajesh's trivial balance check cannot get a connection: it waits and then fails with a timeout error. The application appears frozen while database CPU is low, because the connections are stuck waiting rather than computing.
Diagnosis follows a clear path. Rajesh checks PostgreSQL's pg_stat_activity for long-running statements, examines HikariCP's pendingThreads metric to confirm the pool is the bottleneck, and runs EXPLAIN ANALYZE on the culprit to find the root cause, which is usually a missing or dropped index, stale statistics, or data growth causing a sequential scan.
The fix addresses both the root cause and the guardrails. Rajesh adds the right composite index so the query returns to milliseconds, sets a statement_timeout at the database level so no query can run indefinitely, adds a @Transactional(timeout = ...) in the application so transactions are bounded, and restructures code so external HTTP calls happen outside the transaction boundary. He does not just make the pool bigger, because a larger pool only delays exhaustion and adds database overhead.
At the National Bank of India, the yearly report endpoint degraded after data growth and started timing out unrelated balance checks even though the database CPU was low. Pankaj moved the report to a read replica, added a two-second transaction timeout on the transfer path, set a statement timeout on the database, and fixed the underlying index. The pool stopped draining and fast requests became responsive again.
Remember one sentence: the connection pool is a shared finite resource, so one slow query that hogs a connection starves every other request behind it, and the fix is reducing hold time, not increasing pool size.
---