HikariCP

spring:
  datasource:
    url: jdbc:postgresql://db.internal:5432/orders
    username: orders_app
    password: ${DB_PASSWORD}
    hikari:
      pool-name: orders-pool
      maximum-pool-size: 10
      # minimum-idle omitted on purpose: defaults to maximum-pool-size (fixed pool)

      connection-timeout: 10000      # ms; default 30000, minimum 250
      validation-timeout: 3000       # ms; must be lower than connection-timeout
      max-lifetime: 1500000          # 25 min; see the maxLifetime section
      keepalive-time: 120000         # 2 min; must be lower than max-lifetime
      idle-timeout: 300000           # only used when minimum-idle < maximum-pool-size

      # Session setup, run once per new physical connection
      connection-init-sql: SET TIME ZONE 'UTC'

# Not needed with a JDBC4 driver. The pool calls Connection.isValid(), which
# is faster than a test query. Only set connection-test-query for a very old
# driver, for example:
#   connection-test-query: SELECT 1 FROM DUAL     # legacy Oracle driver

# Budget example
#   Database max_connections ............ 200
#   Reserved (admin, migrations, BI) .... 20
#   Available to the application ........ 180
#   Instances at peak (rolling deploy) .. 12
#   Per-instance maximum-pool-size ...... 180 / 12 = 15

spring:
  datasource:
    hikari:
      maximum-pool-size: 15

# Fixed pool (default behaviour): the connections are already open
# when a burst arrives.
#
# Elastic pool: only worth it when many instances sit mostly idle and you
# need to save database connections. Idle ones are closed after idle-timeout.
#
#   maximum-pool-size: 20
#   minimum-idle: 5
#   idle-timeout: 300000
#
# Signs the pool is too small, in order of how reliable they are:
#   - hikaricp.connections.pending stays above 0 under normal load
#   - hikaricp.connections.acquire p99 is high while query times are low
#   - SQLTransientConnectionException: ... Connection is not available
#
# Signs it is NOT the pool: pending is 0 but requests are slow. Look at queries.

# Compare the pool's max-lifetime with the real limits on your path.

# MySQL: server-side idle limit
#   SHOW VARIABLES LIKE 'wait_timeout';        -- 28800 s (8 h) by default
# PostgreSQL has no default idle-connection limit, but a proxy or firewall
# in front of it often does.
#   SHOW idle_session_timeout;                 -- 0 (disabled) by default, PG 14+

# Example: a firewall drops TCP flows idle for 5 minutes
#   keepalive-time must be < 5 min, so use 2 min
#   max-lifetime is independent of it; 25-30 min is fine

spring:
  datasource:
    hikari:
      max-lifetime: 1500000      # 25 min
      keepalive-time: 120000     # 2 min

# Example: a proxy that closes ANY connection after 30 minutes of age
#   max-lifetime must be below 30 min with margin, so 25 min as above

# Example: a database role with a hard session limit of 10 minutes
#   max-lifetime: 480000         # 8 min, 2 min of margin

# Check what the pool is actually doing at startup (DEBUG level):
#   HikariPool-1 - configuration:
#   maxLifetime................................1500000
#   keepaliveTime..............................120000

# 1. Turn on the pool's own periodic stats (logs every 30 s at DEBUG)
logging:
  level:
    com.zaxxer.hikari: DEBUG
#   HikariPool-1 - Pool stats (total=10, active=10, idle=0, waiting=5)
#   active=max and waiting>0  -> exhaustion
#   active stuck at a high number with no traffic -> leak

# 2. Leak detection: log a stack trace for any connection held too long
spring:
  datasource:
    hikari:
      leak-detection-threshold: 20000    # ms; minimum 2000, 0 disables
  jpa:
    open-in-view: false

# 3. Metrics through Actuator and Micrometer
#   hikaricp.connections.active      in use right now
#   hikaricp.connections.idle        waiting in the pool
#   hikaricp.connections.pending     threads waiting for a connection
#   hikaricp.connections.max         configured maximum
#   hikaricp.connections.acquire     time to get a connection (timer)
#   hikaricp.connections.usage       time a connection was held (timer)
#   hikaricp.connections.timeout     count of acquire timeouts
#
#   GET /actuator/metrics/hikaricp.connections.pending

// Most common leak in real code: work that does not need the connection
// happens inside the transaction.
@Transactional
public void placeOrder(OrderRequest request) {
    Order order = orders.save(Order.from(request));
    paymentClient.charge(order);          // remote HTTP call, connection held throughout
}

// Narrow the transaction instead: persist, commit, then call out.
public void placeOrder(OrderRequest request) {
    Order order = orderWriter.saveInTransaction(request);   // short tx
    paymentClient.charge(order);                            // no connection held
}

# application.yml
app:
  datasource:
    write:
      url: jdbc:postgresql://primary.internal:5432/orders
      username: orders_rw
      password: ${WRITE_DB_PASSWORD}
      hikari:
        pool-name: write-pool
        maximum-pool-size: 10
        max-lifetime: 1500000
        keepalive-time: 120000
    read:
      url: jdbc:postgresql://replica.internal:5432/orders
      username: orders_ro
      password: ${READ_DB_PASSWORD}
      hikari:
        pool-name: read-pool
        maximum-pool-size: 20
        max-lifetime: 1500000
        keepalive-time: 120000
        read-only: true

// DataSourceConfig.java
@Configuration
public class DataSourceConfig {

    @Bean
    @Primary
    @ConfigurationProperties("app.datasource.write")
    public DataSourceProperties writeProperties() {
        return new DataSourceProperties();
    }

    @Bean
    @Primary
    @ConfigurationProperties("app.datasource.write.hikari")
    public HikariDataSource writeDataSource(
            @Qualifier("writeProperties") DataSourceProperties props) {
        return props.initializeDataSourceBuilder()
                .type(HikariDataSource.class)
                .build();
    }

    @Bean
    @ConfigurationProperties("app.datasource.read")
    public DataSourceProperties readProperties() {
        return new DataSourceProperties();
    }

    @Bean
    @ConfigurationProperties("app.datasource.read.hikari")
    public HikariDataSource readDataSource(
            @Qualifier("readProperties") DataSourceProperties props) {
        return props.initializeDataSourceBuilder()
                .type(HikariDataSource.class)
                .build();
    }
}

// Both pools appear separately in metrics, tagged by pool name:
//   hikaricp.connections.active{pool="write-pool"}
//   hikaricp.connections.active{pool="read-pool"}

spring:
  datasource:
    hikari:
      pool-name: orders-pool
      maximum-pool-size: 10             # budgeted against max_connections
      connection-timeout: 10000         # fail fast; callers usually give up sooner
      validation-timeout: 3000
      max-lifetime: 1500000             # below the shortest limit on the path
      keepalive-time: 120000            # below the firewall idle timeout
      leak-detection-threshold: 20000   # above the longest valid transaction
  jpa:
    open-in-view: false

management:
  endpoints:
    web:
      exposure:
        include: health,metrics,prometheus
  metrics:
    distribution:
      percentiles-histogram:
        hikaricp.connections.acquire: true

# Verify before release:
#   [ ] maximum-pool-size x instances (peak) < max_connections - reserved
#   [ ] max-lifetime < every server, proxy and firewall limit, with margin
#   [ ] keepalive-time < max-lifetime and < TCP idle timeout
#   [ ] pool-name set, metrics scraped, alerts on pending and timeouts
#   [ ] no remote calls inside @Transactional methods
#   [ ] connection-timeout agrees with the HTTP timeout of callers