Connection Pooling

What is a Connection Pool — and What Problem Does It Solve?

Opening a physical connection to a database is expensive. The sequence is: DNS resolution, TCP three-way handshake, TLS negotiation (in production, always), database authentication, session initialization on the server side. On a local network that's 5–20ms. On a cloud network between availability zones it can be 50–200ms. For an API that answers in 10ms, spending 100ms on connection setup per request is the dominant cost — the query itself is the cheap part.

A connection pool solves this by keeping a set of physical connections open and reusing them. When your code calls dataSource.getConnection(), the pool hands you an already-open connection in under 1ms. When you call conn.close(), the connection is not destroyed — it is returned to the pool for the next caller.

// Without a pool — creates a new physical TCP connection every call
public Optional<Order> findById(long id) {
    try (Connection conn = DriverManager.getConnection(url, user, pass)) {
        // TCP + TLS + auth: 50-200ms BEFORE the query even starts
        // Query execution: 1-5ms
        // Total: 51-205ms, dominated by connection setup
    }
}

// With a pool — borrows an already-open connection
public Optional<Order> findById(long id) {
    try (Connection conn = dataSource.getConnection()) {
        // Pool borrow: <1ms
        // Query execution: 1-5ms
        // Total: 2-6ms
    }
    // conn.close() returns it to the pool; no TCP teardown
}

// The pool lifecycle — what actually happens at startup and per request

┌─────────────────────────────────────────────────────────────────────┐
│                         CONNECTION POOL                              │
│                                                                      │
│  At startup (minimumIdle connections pre-created):                  │
│  ┌──────┐  ┌──────┐  ┌──────┐  ┌──────┐  ┌──────┐                  │
│  │ C-01 │  │ C-02 │  │ C-03 │  │ C-04 │  │ C-05 │  ← idle          │
│  └──────┘  └──────┘  └──────┘  └──────┘  └──────┘                  │
│                                                                      │
│  Under load (some connections active):                              │
│  ┌──────┐  ┌──────┐  ┌──────┐  ┌──────┐  ┌──────┐                  │
│  │ C-01 │  │ C-02 │  │ C-03 │  │ C-04 │  │ C-05 │                  │
│  │ BUSY │  │ idle │  │ BUSY │  │ BUSY │  │ idle │                  │
│  └──────┘  └──────┘  └──────┘  └──────┘  └──────┘                  │
│                                                                      │
│  dataSource.getConnection()  → picks an idle connection             │
│  conn.close()                → returns to idle pool                 │
└─────────────────────────────────────────────────────────────────────┘

The pool also has a second, less obvious benefit: it limits the total number of concurrent database connections your application can open. PostgreSQL's default max_connections is 100; MySQL's is 151. With a pool of 20, a Spring Boot application with 500 concurrent HTTP threads still only holds 20 database connections — the other 480 threads wait for one to become available. Without a pool, 500 concurrent requests could open 500 connections, overloading the database server's connection limit and its memory.

HikariCP — The Default, and Why

HikariCP has been the default connection pool in Spring Boot since version 2.0 (2018). It consistently outperforms alternatives on benchmarks, its codebase is small and auditable, and its author's documentation on pool sizing is the most cited reference in the field. In 2026, there is no good reason to choose a different pool for a new project.

Dependency

<!-- HikariCP is included transitively with any Spring Boot data starter -->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>

<!-- Or for JDBC-only projects -->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<!-- Both bring in HikariCP automatically; no separate dependency needed -->

Configuration: Every Parameter That Matters

Spring Boot application.properties

# Essential — the three you must always set
spring.datasource.url=jdbc:postgresql://localhost:5432/shop
spring.datasource.username=app
spring.datasource.password=${DB_PASSWORD}   # never hardcode; use env var or secrets manager

# HikariCP — the settings that actually matter in production
spring.datasource.hikari.pool-name=ShopPool        # appears in logs and metrics; name it meaningfully
spring.datasource.hikari.maximum-pool-size=20       # see Sizing section — don't guess
spring.datasource.hikari.minimum-idle=10            # keep at least this many connections alive
spring.datasource.hikari.connection-timeout=5000    # 5s: how long to wait for a connection from the pool
spring.datasource.hikari.max-lifetime=1740000       # 29 min: MUST be less than DB wait_timeout (see below)
spring.datasource.hikari.keepalive-time=120000      # 2 min: send keepalive query to prevent idle timeout
spring.datasource.hikari.idle-timeout=300000        # 5 min: remove idle connections above minimum-idle

# Leak detection — enable in dev/staging, optional in prod
spring.datasource.hikari.leak-detection-threshold=10000   # warn if a connection is held >10s without being returned

What each parameter actually does

MySQL-specific driver properties

# These properties live on the MySQL JDBC driver, not HikariCP itself.
# They're meaningful for MySQL; don't copy them to a PostgreSQL project.
# PostgreSQL's driver handles statement caching internally.

spring.datasource.hikari.data-source-properties.cachePrepStmts=true
spring.datasource.hikari.data-source-properties.prepStmtCacheSize=250
spring.datasource.hikari.data-source-properties.prepStmtCacheSqlLimit=2048
spring.datasource.hikari.data-source-properties.useServerPrepStmts=true
spring.datasource.hikari.data-source-properties.rewriteBatchedStatements=true

Pool Sizing: The Counter-Intuitive Reality

The most common mistake with connection pools is making them too large. The instinct — "more connections means more throughput" — is wrong. Database servers are I/O-bound, not CPU-bound. Adding more connections beyond what the CPU and I/O subsystem can serve concurrently causes context-switching overhead and memory pressure on the database side, which reduces throughput.

The HikariCP formula

// Formula from HikariCP's author (Brettw):
// connections = (core_count × 2) + effective_spindle_count
//
// For a 4-core server with SSD (spindle_count = 1):
// connections = (4 × 2) + 1 = 9 → round to 10
//
// For an 8-core server with SSD:
// connections = (8 × 2) + 1 = 17 → round to 20
//
// Why so few?
// The database server is what processes your queries.
// If the database server has 8 cores, it can run 8 queries in parallel.
// A pool of 100 connections competing for 8 cores means 92 are waiting —
// they add scheduling overhead and memory usage without adding throughput.

Sizing for your actual workload

// Little's Law: connections_needed = request_rate × avg_query_time
//
// Example:
//   500 requests/second
//   Average time a connection is held: 8ms (includes query + result mapping)
//   connections_needed = 500 × 0.008 = 4
//
// Add 2-3× buffer for spikes: pool size = 10-12
//
// This is dramatically smaller than most developers expect.
// A well-tuned application handles thousands of requests/second with 10-20 connections.

// Common scenarios with reasonable starting points

// Scenario A: typical REST API, fast queries (<10ms), 4-core app server
spring.datasource.hikari.maximum-pool-size=10
spring.datasource.hikari.minimum-idle=5

// Scenario B: report-heavy API, queries 50-500ms, 8-core app server
spring.datasource.hikari.maximum-pool-size=20
spring.datasource.hikari.minimum-idle=10

// Scenario C: batch processing, long-running queries, low concurrency
spring.datasource.hikari.maximum-pool-size=5
spring.datasource.hikari.minimum-idle=2
spring.datasource.hikari.connection-timeout=60000   # longer tolerance for batch

Monitoring: What to Watch and What the Numbers Mean

Pool exhaustion is the most common database-related production incident that isn't actually a database problem. Understanding which metrics to watch and what they indicate lets you diagnose the root cause instead of just increasing the pool size (which often masks rather than fixes the real issue).

Spring Boot Actuator metrics

# application.properties — expose the metrics endpoints
management.endpoints.web.exposure.include=health,metrics
management.endpoint.health.show-details=always

# The three HikariCP metrics that matter:

GET /actuator/metrics/hikaricp.connections.active
# How many connections are currently in use by application code.
# Normal: varies with load. Alarming: consistently at maximum-pool-size.

GET /actuator/metrics/hikaricp.connections.pending
# How many threads are waiting for a connection from an exhausted pool.
# Normal: 0. Any sustained non-zero value means your pool is too small
# OR you have a connection leak OR you have slow queries holding connections.

GET /actuator/metrics/hikaricp.connections.acquire
# Time spent waiting to acquire a connection.
# Normal: <1ms. Degrading: 10-50ms. Critical: >100ms or timeouts.

Reading the HikariCP log output

# Enable at INFO for pool lifecycle events; DEBUG for per-connection events
logging.level.com.zaxxer.hikari=INFO

# Periodic pool stats (logged every 30s by default at DEBUG):
# HikariPool-ShopPool - Pool stats (total=20, active=3, idle=17, waiting=0)
#                                   ↑ max size  ↑ in use  ↑ ready  ↑ should be 0

# What to alarm on:
# waiting > 0  → threads blocked waiting for a connection
# active = total for sustained periods → pool fully saturated
# active = 0 but application is slow → not a pool problem; look at query performance

Diagnosing the three most common failures

// FAILURE 1: "Connection is not available, request timed out after 5000ms"
// (HikariPoolTimeoutException)
//
// This means the pool was exhausted for 5+ seconds. Root causes in order of frequency:
//
// A) Connection leak — code obtained a connection and never returned it.
//    Fix: enable leak detection. The next leak will log a stack trace showing where.
spring.datasource.hikari.leak-detection-threshold=5000   // log if held >5s
//
// B) Slow queries holding connections for too long.
//    Fix: find and optimize the slow queries with EXPLAIN ANALYZE.
//    Check: logging.level.org.hibernate.SQL=DEBUG + format_sql=true
//
// C) Pool genuinely too small for the load.
//    Fix: increase maximum-pool-size — but only after ruling out A and B.
//    Increasing pool size with a leak just makes the leak slower to manifest.

// FAILURE 2: "Connection reset" or "An I/O error occurred while sending to the backend"
//
// A connection in the pool has gone stale — the database closed it
// but HikariCP didn't know.
//
// Root cause: max-lifetime is longer than the database's idle connection timeout.
// Fix: set max-lifetime shorter than the database's wait_timeout.
//      Also set keepalive-time to periodically validate idle connections.
spring.datasource.hikari.max-lifetime=1680000      // 28 min if DB timeout is 30 min
spring.datasource.hikari.keepalive-time=60000      // 1 min keepalive ping

// FAILURE 3: "FATAL: sorry, too many clients already" (PostgreSQL)
// or "ERROR 1040 (HY000): Too many connections" (MySQL)
//
// The total connections from all application instances exceed the database limit.
// Fix: reduce pool size per instance or increase max_connections on the database.
//
// On PostgreSQL, check current connections:
SELECT count(*), state FROM pg_stat_activity GROUP BY state;
-- active: currently executing a query
-- idle: connected but waiting (your pool's idle connections)
-- idle in transaction: connected with an open transaction — investigate these

Programmatic pool inspection (when Actuator isn't available)

@Component
public class PoolHealthChecker {

    private final HikariDataSource dataSource;

    public PoolHealthChecker(DataSource dataSource) {
        this.dataSource = (HikariDataSource) dataSource;
    }

    public PoolStats stats() {
        HikariPoolMXBean pool = dataSource.getHikariPoolMXBean();
        return new PoolStats(
            pool.getTotalConnections(),
            pool.getActiveConnections(),
            pool.getIdleConnections(),
            pool.getThreadsAwaitingConnection()
        );
    }
}

public record PoolStats(int total, int active, int idle, int waiting) {
    public boolean isExhausted() { return waiting > 0; }
    public double utilizationRate() { return total == 0 ? 0 : (double) active / total; }
}

Multiple DataSources: Primary and Read Replica

A common production pattern is routing read-only queries to a read replica and writes to the primary database. This requires two separate pools. Spring Boot's auto-configuration only handles one DataSource; for multiple you configure them manually.

# application.properties — two datasources
# Primary (read-write)
app.datasource.primary.url=jdbc:postgresql://primary-db:5432/shop
app.datasource.primary.username=app_rw
app.datasource.primary.password=${DB_PRIMARY_PASSWORD}
app.datasource.primary.hikari.maximum-pool-size=20
app.datasource.primary.hikari.pool-name=PrimaryPool

# Read replica (read-only)
app.datasource.replica.url=jdbc:postgresql://replica-db:5432/shop
app.datasource.replica.username=app_ro
app.datasource.replica.password=${DB_REPLICA_PASSWORD}
app.datasource.replica.hikari.maximum-pool-size=30   # replicas handle more read load
app.datasource.replica.hikari.pool-name=ReplicaPool

@Configuration
public class DataSourceConfig {

    @Bean
    @Primary
    @ConfigurationProperties("app.datasource.primary.hikari")
    public DataSource primaryDataSource(
            @Value("${app.datasource.primary.url}") String url,
            @Value("${app.datasource.primary.username}") String username,
            @Value("${app.datasource.primary.password}") String password) {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl(url);
        config.setUsername(username);
        config.setPassword(password);
        config.setReadOnly(false);
        return new HikariDataSource(config);
    }

    @Bean
    @ConfigurationProperties("app.datasource.replica.hikari")
    public DataSource replicaDataSource(
            @Value("${app.datasource.replica.url}") String url,
            @Value("${app.datasource.replica.username}") String username,
            @Value("${app.datasource.replica.password}") String password) {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl(url);
        config.setUsername(username);
        config.setPassword(password);
        config.setReadOnly(true);   // prevents accidental writes to the replica
        return new HikariDataSource(config);
    }
}

// Using the replica explicitly in a read-only repository
@Repository
public class ProductReportRepository {

    private final JdbcTemplate replicaJdbc;

    public ProductReportRepository(@Qualifier("replicaDataSource") DataSource replica) {
        this.replicaJdbc = new JdbcTemplate(replica);
    }

    public List<ProductSalesReport> topSellersByRevenue(int limit) {
        return replicaJdbc.query("""
            SELECT p.sku, p.name, SUM(oi.quantity * oi.unit_price) AS revenue
            FROM products p
            JOIN order_items oi ON oi.product_id = p.id
            JOIN orders o ON o.id = oi.order_id AND o.status = 'PAID'
            GROUP BY p.id, p.sku, p.name
            ORDER BY revenue DESC
            LIMIT ?
            """, this::mapRow, limit);
    }
}

Best Practices and Common Pitfalls

Interview Questions