Configuring the Connection Pool

Size and tune HikariCP so requests do not queue for connections and the database is not swamped by every replica at once.

Syntax

spring.datasource.url=jdbc:postgresql://host:5432/db
spring.datasource.hikari.maximum-pool-size=10
# Hikari time values are milliseconds
spring.datasource.hikari.connection-timeout=3000
spring.datasource.hikari.max-lifetime=1500000

Opening a database connection takes a TCP handshake, TLS and authentication: tens of milliseconds. A pool keeps a set of open connections and lends one to each transaction. Spring Boot uses HikariCP by default whenever it is on the classpath, which every JDBC and JPA starter arranges, and binds its settings from spring.datasource.hikari.*. These bind straight onto Hikari's own setters, so every time value is a number of milliseconds, not a duration such as 3s.

The settings that matter

Property	Hikari default	What it controls
`maximum-pool-size`	10	Upper limit of open connections
`minimum-idle`	same as maximum	Idle connections kept ready
`connection-timeout`	30 seconds	How long a caller waits for a free connection
`max-lifetime`	30 minutes	Age at which a connection is retired
`leak-detection-threshold`	off	Logs a stack trace for connections held too long

Bigger is not faster

The instinct under load is to raise the pool to 100. The database has a limited number of cores and disks; past a point, more concurrent queries only add contention and context switching, and each query gets slower. A small pool with a short queue usually has better throughput than a large one. Start near the default, measure, and grow slowly.

Do the sum across the whole system. Six replicas with 20 connections each is 120 connections, and PostgreSQL's default max_connections is 100. During a rolling deploy old and new pods overlap, so leave headroom or the new pods fail to start.

Waiting and failing

When every connection is busy, callers wait up to connection-timeout and then get an SQLTransientConnectionException. Thirty seconds is longer than most clients wait, so a saturated pool shows up as a pile of timed-out requests. Lower it to two or three seconds for an API and you fail fast with a clear error.

Long transactions are the usual cause of exhaustion: a remote HTTP call made inside @Transactional holds a connection the whole time. Keep calls to other services outside the transaction. Virtual threads make this sharper: you can have thousands of concurrent requests but still only ten connections, so the pool becomes the real limit.

Lifetime and watching it

Set max-lifetime a little shorter than any timeout imposed by the database, a proxy or a firewall, so Hikari retires connections before something else silently cuts them. With Actuator, Boot publishes pool metrics such as hikaricp.connections.active and hikaricp.connections.pending. A steady pending count above zero is the signal that the pool, or the queries holding it, needs attention.

Example

spring:
  datasource:
    url: jdbc:postgresql://${DB_HOST:localhost}:5432/bookings?ApplicationName=bookings-api
    username: ${DB_USER}
    password: ${DB_PASSWORD}
    hikari:
      pool-name: bookings
      maximum-pool-size: 15
      minimum-idle: 15
      connection-timeout: 3000        # milliseconds
      max-lifetime: 1500000          # 25 minutes
      keepalive-time: 300000         # 5 minutes
      leak-detection-threshold: 20000 # 20 seconds

When to use it

Stop an API from hanging for 30 seconds under load by lowering connection-timeout and returning 503.
Size pools for a service that scales from 3 to 10 replicas against one PostgreSQL server.
Find a code path that holds connections too long with leak-detection-threshold during a load test.

More examples

Fail fast when the pool is exhausted

With a short connection-timeout, a saturated pool surfaces as Hikari's SQLTransientConnectionException, wrapped by Spring. Plain JdbcClient or JdbcTemplate calls outside a transaction see CannotGetJdbcConnectionException; inside @Transactional the connection is taken when the transaction begins, so it arrives as CannotCreateTransactionException, with JPA as well. Walking the cause chain covers both. Returning 503 tells load balancers and clients that retrying later is reasonable, instead of a generic 500 that looks like a bug.

import java.sql.SQLTransientConnectionException;

import org.springframework.http.HttpStatus;
import org.springframework.http.ProblemDetail;
import org.springframework.jdbc.CannotGetJdbcConnectionException;
import org.springframework.transaction.CannotCreateTransactionException;
import org.springframework.web.bind.annotation.ExceptionHandler;
import org.springframework.web.bind.annotation.RestControllerAdvice;

@RestControllerAdvice
public class DatabaseBusyHandler {

    @ExceptionHandler({CannotGetJdbcConnectionException.class, CannotCreateTransactionException.class})
    ProblemDetail poolExhausted(RuntimeException ex) {
        boolean timedOut = causedByPoolTimeout(ex);
        ProblemDetail problem = ProblemDetail.forStatusAndDetail(
                HttpStatus.SERVICE_UNAVAILABLE,
                timedOut ? "The service is busy, please retry shortly." : "The database is unavailable.");
        problem.setProperty("retryable", true);
        return problem;
    }

    private static boolean causedByPoolTimeout(Throwable ex) {
        for (Throwable t = ex; t != null; t = t.getCause()) {
            if (t instanceof SQLTransientConnectionException) {
                return true;
            }
        }
        return false;
    }
}

Reading pool state for a status endpoint

Hikari starts its pool lazily, so the MXBean is null until the first connection is requested; the check avoids a NullPointerException. Useful while load testing to see whether threads queue for connections. In production prefer the Actuator metrics, which are already exported to your monitoring system and keep history; if you keep an endpoint like this, secure it as an internal one.

import java.util.Map;

import javax.sql.DataSource;

import com.zaxxer.hikari.HikariDataSource;
import com.zaxxer.hikari.HikariPoolMXBean;

import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

@RestController
public class PoolStatusController {

    private final DataSource dataSource;

    public PoolStatusController(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    @GetMapping("/internal/pool")
    Map<String, Integer> pool() {
        if (!(dataSource instanceof HikariDataSource hikari)) {
            return Map.of();
        }
        HikariPoolMXBean pool = hikari.getHikariPoolMXBean();
        if (pool == null) {
            return Map.of("max", hikari.getMaximumPoolSize());
        }
        return Map.of(
                "active", pool.getActiveConnections(),
                "idle", pool.getIdleConnections(),
                "waiting", pool.getThreadsAwaitingConnection(),
                "max", hikari.getMaximumPoolSize());
    }
}

Budgeting connections across replicas

The pool size is a share of a fixed budget, not a per-service guess. Counting surge pods is the step people miss: Kubernetes starts new pods before stopping old ones, so the peak is higher than the steady replica count. If the budget is too small, PgBouncer in front of PostgreSQL lets many client connections share fewer server connections.

PostgreSQL max_connections        = 200
reserved for admin and migrations = 10
other services sharing the server = 40
-----------------------------------------
available to bookings-api         = 150

replicas during a rolling deploy  = 6 running + 2 surging = 8
maximum-pool-size per replica     = 150 / 8  -> 18, round down to 15

Discussion

Be the first to comment on this lesson.