HikariCP

spring:
  datasource:
    url: jdbc:postgresql://localhost:5432/mydb
    username: appuser
    password: ${DB_PASSWORD}
    hikari:

      # ── Pool sizing ──────────────────────────────────────────────────
      maximum-pool-size: 10
      # Maximum total connections in the pool (active + idle).
      # This is the most important setting. More is NOT better.
      # Rule: (CPU cores × 2) + effective disk spindles
      # For most cloud VMs: 10 is a good starting point.
      # Database max_connections ÷ number of app instances = per-instance limit.

      minimum-idle: 5
      # Minimum idle connections to maintain when the pool is not busy.
      # HikariCP recommends setting minimum-idle = maximum-pool-size
      # (fixed-size pool) for best performance — eliminates connection
      # creation overhead under sudden load spikes.
      # Set lower only if connection overhead to DB is a concern.

      # ── Timeouts ─────────────────────────────────────────────────────
      connection-timeout: 20000
      # Maximum milliseconds a thread waits for a connection from the pool.
      # Default: 30000 (30s). Minimum: 250ms.
      # If exceeded: SQLTimeoutException thrown to the caller.
      # Reduce to 5000-10000ms in production to fail fast under pool exhaustion.

      idle-timeout: 300000
      # Milliseconds an idle connection is kept in the pool before being closed.
      # Default: 600000 (10m). Minimum: 10000ms.
      # Only applies when connections > minimum-idle.
      # Set below DB server idle timeout (e.g. PostgreSQL idle_in_transaction_session_timeout).

      max-lifetime: 1800000
      # Maximum milliseconds a connection lives regardless of activity.
      # Default: 1800000 (30m). Minimum: 30000ms.
      # CRITICAL: must be set 30-60 seconds below the database server's
      # connection timeout (MySQL wait_timeout, PostgreSQL tcp_keepalives_idle).
      # Prevents "connection reset" errors when DB closes idle connections.

      keepalive-time: 60000
      # Milliseconds between keepalive pings to idle connections.
      # Sends a lightweight query (connection-test-query) to prevent
      # network equipment from closing idle TCP connections.
      # Set to 60000 (1m) for cloud databases with aggressive firewall timeouts.

      # ── Connection validation ─────────────────────────────────────────
      connection-test-query: SELECT 1
      # SQL executed to validate a connection before handing it to a thread.
      # Required for databases that don't support JDBC4 isValid().
      # MySQL, PostgreSQL, H2: omit — JDBC4 isValid() is used automatically.
      # Oracle: SELECT 1 FROM DUAL
      # SQL Server: SELECT 1

      validation-timeout: 3000
      # Maximum milliseconds to wait for connection validation.
      # Default: 5000 (5s). Must be less than connection-timeout.

      # ── Pool identity ─────────────────────────────────────────────────
      pool-name: myapp-db-pool
      # Appears in log messages, JMX, and Actuator metrics.
      # Use a descriptive name — essential when running multiple pools.

      # ── Connection initialisation ──────────────────────────────────────
      connection-init-sql: SET TIME ZONE 'UTC'
      # SQL executed once when a connection is created.
      # Use for session-level settings: timezone, search_path, etc.

      # ── Schema ────────────────────────────────────────────────────────
      schema: myschema
      # Sets the default schema for all connections in the pool.
      # Equivalent to SET search_path = myschema (PostgreSQL).

# ── The pool size formula (from the HikariCP wiki): ──────────────────
#
# pool_size = Tn × (Cm - 1) + 1
#
# Tn = maximum number of threads in the application
# Cm = maximum number of simultaneous database connections per thread
#
# For most Spring Boot REST APIs (one DB call per request thread):
# pool_size = thread_count × 1 = thread_count
#
# Tomcat default threads: 200
# → pool_size = 200 × 1 = 200? NO — this is wrong.
#
# ── Why large pools hurt performance: ────────────────────────────────
# Each DB connection consumes: ~5MB RAM on PostgreSQL, ~1MB on MySQL
# 200 connections = 1GB RAM on the DB server just for connections.
# Database query execution is CPU-bound — more connections = more
# context switching, more lock contention, worse throughput.
#
# ── The correct approach — right-size for the DB's CPU: ───────────────
# Optimal connections ≈ (DB server CPUs × 2) + effective disk count
# 4-core DB server + SSD: (4 × 2) + 1 = ~10 connections
# 8-core DB server + SSD: (8 × 2) + 1 = ~17 connections
#
# Most production applications run well with maximum-pool-size: 10-20.
# Increase only after measuring and confirming pool exhaustion.

# ── Per-instance sizing with multiple app instances: ──────────────────
# If the DB allows max 100 connections and you run 5 app instances:
# Per instance: 100 / 5 = 20 max connections
# Leave headroom for migrations, admin, monitoring: 20 - 5 = 15

# ── Fixed pool (minimum-idle = maximum-pool-size): ────────────────────
spring:
  datasource:
    hikari:
      maximum-pool-size: 10
      minimum-idle: 10      # same as max — fixed pool size
      # HikariCP team recommends this for consistent performance.
      # Connection creation happens at startup, not under load.

# ── Elastic pool (minimum-idle < maximum-pool-size): ─────────────────
spring:
  datasource:
    hikari:
      maximum-pool-size: 20
      minimum-idle: 5       # idle connections maintained between bursts
      idle-timeout: 300000  # close extra connections after 5m idle
      # Use when traffic is highly bursty and connection cost to DB is low.

# ── The stale connection problem: ────────────────────────────────────
# 1. App opens connection → stays idle in pool for 8 hours
# 2. MySQL closes idle connection after wait_timeout (default: 8 hours)
# 3. Next request borrows the connection from the pool
# 4. App sends query → "Communications link failure / Broken pipe"
#
# ── Database server timeout values: ──────────────────────────────────
# MySQL:       wait_timeout = 28800 (8 hours)
# PostgreSQL:  no default idle timeout (but TCP keepalives apply)
# Oracle:      IDLE_TIME profile parameter (varies)
# SQL Server:  no idle timeout by default
# Azure SQL:   30 minutes idle timeout on some tiers
# Cloud firewalls: often close TCP connections idle > 4-15 minutes

# ── Rule: max-lifetime must be LESS than the DB's timeout: ────────────
# MySQL (wait_timeout = 28800s / 8h):
spring:
  datasource:
    hikari:
      max-lifetime: 1740000   # 29 minutes (< MySQL 30m safe zone)
      # Or more conservatively:
      max-lifetime: 600000    # 10 minutes — safe for most environments

# AWS RDS / Aurora: proxy closes connections idle > 15 minutes:
spring:
  datasource:
    hikari:
      max-lifetime: 840000    # 14 minutes

# Azure SQL Database (30min timeout):
spring:
  datasource:
    hikari:
      max-lifetime: 1740000   # 29 minutes

# Cloud with aggressive firewall (4min idle TCP timeout):
spring:
  datasource:
    hikari:
      max-lifetime: 180000    # 3 minutes
      keepalive-time: 60000   # ping every 1 minute to keep TCP alive

# ── HikariCP retires connections gracefully: ──────────────────────────
# When a connection reaches maxLifetime, HikariCP:
# 1. Waits for the connection to be returned to the pool (not killed mid-query)
# 2. Closes it
# 3. Opens a new replacement connection
# This is transparent to the application — no errors.

# ── PROBLEM 1: Pool exhaustion (all connections in use) ──────────────
# Symptom: java.sql.SQLTimeoutException: Timeout after 30000ms
#          or high hikaricp.connections.pending metric
# Cause A: Pool too small for the request rate
# Cause B: Slow queries holding connections too long
# Cause C: Connection leak — connection borrowed but never returned

# Diagnose:
logging:
  level:
    com.zaxxer.hikari: DEBUG
# Look for: "HikariPool-1 - Pool stats (total=10, active=10, idle=0, waiting=5)"

# Fix A: increase maximum-pool-size (up to DB limit)
# Fix B: optimise slow queries (add indexes, fix N+1)
# Fix C: enable leak detection

# ── PROBLEM 2: Connection leak ────────────────────────────────────────
# Symptom: Connections slowly lost; pool never returns to idle
# Cause: @Transactional not applied; manual connection not closed;
#        exception thrown before returning connection

# Enable leak detection (set threshold in ms — try 5s in dev):
spring:
  datasource:
    hikari:
      leak-detection-threshold: 5000
# Logs: "Connection leak detection triggered for connection ..."
#        with the stack trace of where the connection was borrowed.
# Set to 0 to disable (production); enable only when investigating.

# ── PROBLEM 3: Stale connections ─────────────────────────────────────
# Symptom: "Communications link failure", "Broken pipe",
#          or "Connection is closed" after periods of low traffic
# Cause: max-lifetime > DB server's connection timeout

# Fix: reduce max-lifetime and add keepalive:
spring:
  datasource:
    hikari:
      max-lifetime: 600000       # 10 minutes
      keepalive-time: 60000      # ping idle connections every 1 minute

# ── PROBLEM 4: Slow connection acquisition ────────────────────────────
# Symptom: hikaricp.connections.acquire p99 > 100ms
# Cause: Pool too small; or DB is slow to accept connections

# Diagnose via Actuator:
# GET /actuator/metrics/hikaricp.connections.acquire
# Check: count, total, max, percentile values

# Fix: use fixed pool (minimum-idle = maximum-pool-size) so
#      connections are always warm, never created under load.

# ── application.yml — two separate pools: ────────────────────────────
app:
  datasource:
    write:
      url: jdbc:postgresql://write-db:5432/mydb
      username: writer
      password: ${WRITE_DB_PASSWORD}
      hikari:
        pool-name: write-pool
        maximum-pool-size: 10
        minimum-idle: 10
        max-lifetime: 600000
        keepalive-time: 60000
    read:
      url: jdbc:postgresql://read-replica:5432/mydb
      username: reader
      password: ${READ_DB_PASSWORD}
      hikari:
        pool-name: read-pool
        maximum-pool-size: 20     # more connections for read traffic
        minimum-idle: 10
        max-lifetime: 600000
        keepalive-time: 60000
        read-only: true           # all connections opened in read-only mode

// ── Configuration class: ──────────────────────────────────────────────
@Configuration
public class DataSourceConfig {

    @Bean
    @Primary
    @ConfigurationProperties("app.datasource.write.hikari")
    public HikariDataSource writeDataSource(
            @Value("${app.datasource.write.url}") String url,
            @Value("${app.datasource.write.username}") String username,
            @Value("${app.datasource.write.password}") String password) {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl(url);
        config.setUsername(username);
        config.setPassword(password);
        config.setPoolName("write-pool");
        config.setMaximumPoolSize(10);
        config.setMinimumIdle(10);
        config.setMaxLifetime(600_000);
        config.setKeepaliveTime(60_000);
        return new HikariDataSource(config);
    }

    @Bean
    @ConfigurationProperties("app.datasource.read.hikari")
    public HikariDataSource readDataSource(
            @Value("${app.datasource.read.url}") String url,
            @Value("${app.datasource.read.username}") String username,
            @Value("${app.datasource.read.password}") String password) {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl(url);
        config.setUsername(username);
        config.setPassword(password);
        config.setPoolName("read-pool");
        config.setMaximumPoolSize(20);
        config.setMinimumIdle(10);
        config.setReadOnly(true);
        config.setMaxLifetime(600_000);
        config.setKeepaliveTime(60_000);
        return new HikariDataSource(config);
    }
}

spring:
  datasource:
    hikari:

      # ── Non-negotiable settings: ──────────────────────────────────────

      pool-name: myapp-pool           # REQUIRED: name for logs and metrics

      maximum-pool-size: 10           # TUNE: start at 10, increase only if
                                      # pool exhaustion is measured

      minimum-idle: 10                # RECOMMENDED: = maximum-pool-size
                                      # for fixed pool (consistent performance)

      max-lifetime: 600000            # CRITICAL: 10 minutes — must be less
                                      # than DB server's connection timeout.
                                      # Prevents stale connection errors.

      keepalive-time: 60000           # REQUIRED for cloud DBs: ping idle
                                      # connections every 1 minute to prevent
                                      # firewall from closing TCP connections.

      connection-timeout: 10000       # TUNE: 10s — fail fast under pool
                                      # exhaustion rather than blocking for 30s

      # ── Optional but recommended: ─────────────────────────────────────

      idle-timeout: 300000            # 5 minutes — only relevant if
                                      # minimum-idle < maximum-pool-size

      validation-timeout: 3000        # 3 seconds — must be < connection-timeout

      # ── Dev only — disable in prod: ───────────────────────────────────

      leak-detection-threshold: 0     # 0 = disabled; set to 5000 temporarily
                                      # when investigating connection leaks

      # ── Verify these settings are correct: ───────────────────────────
      # max-lifetime < DB wait_timeout - 60 seconds
      # keepalive-time < network firewall TCP idle timeout
      # maximum-pool-size × app_instances < DB max_connections × 0.8
      # connection-timeout is short enough to fail fast, long enough to absorb
      #   brief pool exhaustion spikes