Definition
- Connection pool – A fixed-size cache of already-open database connections that the application borrows on
getConnection()and returns onclose(), instead of opening a fresh connection per request. - Why it exists – Opening a TCP connection, authenticating, and setting up a session costs 20 to 50 milliseconds, which would dominate a fast query that runs in 1 millisecond.
- HikariCP – The modern Spring Boot default, prized for its lock-free
ConcurrentBagstructure, aggressive validation, and leak detection. - Apache DBCP – An older Commons pool with similar settings (
maxTotal,maxIdle,maxWaitMillis) but more locking and lower throughput under contention.
Imagine the National Bank of India has three phone lines to its central ledger system. Without a pool, every time a teller needs to check Rajesh's balance, they dial a brand-new call, wait for it to connect and authenticate, ask one question, and hang up. With a pool, the three lines stay permanently connected. A teller grabs a free line, asks the question, and puts the line back for the next teller. If all three lines are busy, the next teller waits in a short queue.
Understanding the Concepts
What is database connection pooling?
A database connection pool is a fixed-size cache of already-open database connections. Instead of opening and closing a connection for every request, the application borrows an existing connection from the pool, uses it, and returns it. The pool manages the lifecycle: creating connections at startup, handing them out, taking them back, validating them, and replacing stale ones.
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 AccountService {
private final DataSource dataSource; // The pool implements DataSource
public AccountService(DataSource dataSource) {
this.dataSource = dataSource;
}
public BigDecimal getBalance(String accountId) throws SQLException {
// Borrow a connection from the pool
try (Connection conn = dataSource.getConnection()) {
String sql = "SELECT balance FROM accounts WHERE account_id = ?";
try (PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setString(1, accountId);
try (ResultSet rs = ps.executeQuery()) {
return rs.next() ? rs.getBigDecimal("balance") : BigDecimal.ZERO;
}
}
} // close() returns the connection to the pool, not destroyed
}
}
The try-with-resources calls close() at the end, but in pooled code this returns the connection to the pool rather than destroying the socket.
Why is connection pooling used?
Opening a JDBC connection does a lot of hidden work: a TCP handshake, TLS negotiation, authentication, and server-side session setup. That can take 20 to 50 milliseconds. A fast query might run in 1 millisecond, so without pooling, the connection overhead dominates by 20 to 50 times.
package com.bank.benchmark;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class ConnectionCostDemo {
public static void main(String[] args) throws SQLException {
String url = "jdbc:postgresql://localhost:5432/nbi_ledger";
// Without pooling: open and close each time
long start = System.nanoTime();
for (int i = 0; i < 100; i++) {
Connection conn = DriverManager.getConnection(url, "nbi_app", "secret");
conn.close(); // Actually destroys the connection
}
long withoutPool = (System.nanoTime() - start) / 1_000_000;
System.out.println("100 connections without pool: " + withoutPool + " ms");
// Output: 100 connections without pool: 2500 ms (approx 25ms each)
}
}
100 connections without pool: 2500 ms (approx 25ms each)
100 connections with pool: 5 ms (approx 0.05ms each, 500x faster)
With a pool, each getConnection() takes microseconds because the connection is already open.
How does HikariCP work?
HikariCP is the default connection pool in Spring Boot and is prized for being fast and lightweight. Its core is a ConcurrentBag data structure that provides near-zero-contention borrow and return operations.
package com.bank.config;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import javax.sql.DataSource;
public class HikariPoolConfig {
public DataSource createDataSource() {
HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:postgresql://localhost:5432/nbi_ledger");
config.setUsername("nbi_app");
config.setPassword("secret");
// Key settings
config.setMaximumPoolSize(10); // Hard cap on connections
config.setMinimumIdle(5); // Connections kept warm
config.setConnectionTimeout(3000); // Wait max 3 seconds to borrow
config.setIdleTimeout(600000); // Close idle connections after 10 min
config.setMaxLifetime(1800000); // Retire after 30 min (survive failover)
config.setLeakDetectionThreshold(5000); // Warn if held > 5 seconds
return new HikariDataSource(config);
}
}
- Key settings explained:
maximumPoolSizeis the hard cap. The pool never opens more connections than this.minimumIdlekeeps a number of connections warm even when idle.connectionTimeoutis how long a borrower waits before failing.maxLifetimeretires a connection after a set age so you survive database restarts.leakDetectionThresholdlogs a stack trace if a connection is held too long.
How does Apache DBCP compare to HikariCP?
Apache DBCP is an older pool from the Apache Commons project. It provides similar functionality but uses more locking internally and is generally slower under contention.
package com.bank.config;
import org.apache.commons.dbcp2.BasicDataSource;
import javax.sql.DataSource;
public class DbcpPoolConfig {
public DataSource createDataSource() {
BasicDataSource ds = new BasicDataSource();
ds.setUrl("jdbc:postgresql://localhost:5432/nbi_ledger");
ds.setUsername("nbi_app");
ds.setPassword("secret");
ds.setMaxTotal(10); // Equivalent to HikariCP maximumPoolSize
ds.setMaxIdle(10); // Max idle connections
ds.setMinIdle(5); // Min idle connections
ds.setMaxWaitMillis(3000); // Equivalent to connectionTimeout
ds.setValidationQuery("SELECT 1");
ds.setTestOnBorrow(true); // Validate before handing out
return ds;
}
}
| Feature | HikariCP | Apache DBCP |
|---------|----------|-------------|
| Internal structure | Lock-free ConcurrentBag | Synchronized pool |
| Performance | Fastest under contention | Slower under contention |
| Leak detection | Built-in with stack trace | Manual configuration |
| Spring Boot default | Yes (since 2.0) | No |
| Validation | Lightweight isValid() | Test query (SELECT 1) |
How do you configure connection pooling in Spring Boot?
In Spring Boot, HikariCP is the default. You configure it through application.properties or application.yml without writing Java configuration code.
# application.properties for National Bank of India
spring.datasource.url=jdbc:postgresql://localhost:5432/nbi_ledger
spring.datasource.username=nbi_app
spring.datasource.password=secret
spring.datasource.hikari.maximum-pool-size=10
spring.datasource.hikari.minimum-idle=5
spring.datasource.hikari.connection-timeout=3000
spring.datasource.hikari.idle-timeout=600000
spring.datasource.hikari.max-lifetime=1800000
spring.datasource.hikari.leak-detection-threshold=5000
package com.bank.service;
import org.springframework.stereotype.Service;
import javax.sql.DataSource;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
@Service
public class BalanceService {
private final DataSource dataSource; // HikariCP auto-configured by Spring Boot
public BalanceService(DataSource dataSource) {
this.dataSource = dataSource;
}
public BigDecimal getBalance(String accountId) throws SQLException {
try (Connection conn = dataSource.getConnection();
PreparedStatement ps = conn.prepareStatement(
"SELECT balance FROM accounts WHERE account_id = ?")) {
ps.setString(1, accountId);
try (ResultSet rs = ps.executeQuery()) {
return rs.next() ? rs.getBigDecimal("balance") : BigDecimal.ZERO;
}
}
}
}
Spring Boot auto-configures the HikariCP pool from the properties. The service just injects the DataSource and borrows connections normally.
How do you size the pool correctly?
Pool size is not "more is better." Each database connection consumes memory and a backend process on the server. The database can only truly execute as many queries in parallel as it has CPU cores. Beyond that point, more connections add context-switching overhead and throughput actually drops.
HikariCP sizing formula:
pool_size = (core_count * 2) + effective_spindle_count
Example for National Bank of India:
Database server: 4 CPU cores, SSD storage (spindle count = 0)
pool_size = (4 * 2) + 0 = 8
With 5 app instances:
Total connections = 5 * 8 = 40
max_connections on PostgreSQL must be > 40 + admin reserve
-- Check PostgreSQL's max_connections
SHOW max_connections; -- default: 100
-- Budget: 100 total - 10 reserved for admin = 90 for apps
-- 5 instances * 8 per instance = 40 connections (well within budget)
What are common mistakes with connection pooling?
The most common mistake is forgetting to close connections, which leaks them from the pool.
package com.bank.service;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.sql.DataSource;
import java.math.BigDecimal;
public class LeakyService {
private final DataSource dataSource;
public LeakyService(DataSource dataSource) {
this.dataSource = dataSource;
}
// WRONG: connection is never returned to the pool if an exception occurs
public BigDecimal getBalanceBad(String accountId) throws SQLException {
Connection conn = dataSource.getConnection();
PreparedStatement ps = conn.prepareStatement(
"SELECT balance FROM accounts WHERE account_id = ?");
ps.setString(1, accountId);
ResultSet rs = ps.executeQuery();
BigDecimal balance = rs.next() ? rs.getBigDecimal("balance") : BigDecimal.ZERO;
conn.close(); // If an exception occurs above, this line never executes!
return balance;
}
// CORRECT: try-with-resources guarantees the connection is returned
public BigDecimal getBalanceGood(String accountId) throws SQLException {
try (Connection conn = dataSource.getConnection();
PreparedStatement ps = conn.prepareStatement(
"SELECT balance FROM accounts WHERE account_id = ?")) {
ps.setString(1, accountId);
try (ResultSet rs = ps.executeQuery()) {
return rs.next() ? rs.getBigDecimal("balance") : BigDecimal.ZERO;
}
} // Connection is ALWAYS returned, even on exception
}
}
Always use try-with-resources. HikariCP's leak detection threshold helps catch these bugs by logging a warning when a connection is held too long.
What should a senior engineer know?
A senior engineer should understand that the pool size must be budgeted from the database down, not from the application load up. The sum of all instances' pool sizes must stay below max_connections. If the resulting per-instance size is too small, the answer is faster queries, read replicas, or an external pooler like PgBouncer, not a bigger pool that the database cannot honor.
Senior checklist:
1. Pool size derived from DB capacity, not app load
2. Sum of all instance pools < max_connections
3. connectionTimeout set low (3s) for fail-fast behavior
4. maxLifetime set below DB's connection age limit
5. leakDetectionThreshold enabled in non-production
6. Metrics exported (active, idle, pending, total)
7. try-with-resources on every connection
Visual Overview
┌──────────────────────────────────────────────────────────┐ │ Connection Pool Lifecycle │ ├──────────────────────────────────────────────────────────┤ │ │ │ Startup: │ │ Pool opens N connections to PostgreSQL │ │ ┌──────┐ ┌──────┐ ┌──────┐ ... ┌──────┐ │ │ │ C1 │ │ C2 │ │ C3 │ │ C10 │ (idle) │ │ └──────┘ └──────┘ └──────┘ └──────┘ │ │ │ │ Borrow: │ │ dataSource.getConnection() │ │ ┌──────────┐ ┌──────┐ │ │ │ Thread A │───→│ C1 │ (now active) │ │ └──────────┘ └──────┘ │ │ │ │ Use: │ │ Thread A runs query using C1 (1-50ms) │ │ │ │ Return: │ │ connection.close() → pool takes C1 back │ │ ┌──────┐ │ │ │ C1 │ (idle again, ready for next borrower) │ │ └──────┘ │ │ │ │ If pool is empty: │ │ Thread B → waits up to connectionTimeout │ │ → throws SQLTransientConnectionException │ │ │ │ ┌──────────────────────────────────────┐ │ │ │ HikariCP vs DBCP │ │ │ │ HikariCP: ConcurrentBag (lock-free) │ │ │ │ DBCP: Synchronized pool (locked) │ │ │ └──────────────────────────────────────┘ │ └──────────────────────────────────────────────────────────┘
Key Takeaway
A database connection pool is a fixed-size cache of already-open connections that the application borrows and returns instead of opening a fresh one per request. Opening a connection is expensive because it involves a TCP handshake, TLS negotiation, authentication, and server-side session setup, which can take 20 to 50 milliseconds. A fast query runs in about 1 millisecond, so without pooling the connection overhead dominates by 20 to 50 times. With a pool, connections are opened once and reused, so each request grabs a warm connection, runs its query, and gives it back. Calling close() in pooled code does not destroy the socket; it returns the connection to the pool.
The two common implementations are HikariCP and Apache DBCP. HikariCP is the modern default in Spring Boot, prized for its lock-free ConcurrentBag design, aggressive validation, and leak detection. Its key settings are maximumPoolSize for the hard cap, connectionTimeout for fail-fast behavior, maxLifetime for retiring connections after a set age, and leakDetectionThreshold for catching unclosed connections. Apache DBCP is an older Commons pool with similar settings but more locking and lower throughput under contention.
The critical senior point is sizing. More connections is not better. The pool must be bounded well below what the database can handle across all application instances, because a too-large pool floods the database with overhead and actually reduces throughput. The formula is roughly CPU cores times two, and the sum of all instances' pool sizes must stay below max_connections.
At the National Bank of India, Rajesh's balance endpoint was slow because an early version opened a connection per call. Switching to a HikariCP pool with a maximum size of ten and a three-second connection timeout made each call borrow a warm connection and fail fast under saturation instead of hanging.
Remember one sentence: a connection pool turns a slow per-request open into a fast grab-and-release, and the single most important setting is a maximum size that respects the database's connection limit.
---