Connection Pooling

# ── Without pooling (each request opens a new connection): ───────────
# Request → open TCP → TLS handshake → auth → query → close TCP
# Cost: 20-100ms overhead per request
# 1000 req/s = 1000 connection open/close cycles per second

# ── With pooling (connections reused): ────────────────────────────────
# Startup: open N connections → put in pool
# Request → borrow connection → query → return connection
# Cost: microseconds to borrow from pool
# 1000 req/s = same N connections, reused continuously

# ── Connection lifecycle in a pool: ───────────────────────────────────
# 1. Pool created at startup — minIdle connections opened immediately
# 2. Request arrives — pool hands out an idle connection
# 3. Request completes — connection returned to pool (not closed)
# 4. Idle connection ages past idleTimeout — pool closes and removes it
# 5. Remaining connections < minIdle — pool opens new connections
# 6. Connection ages past maxLifetime — pool closes and replaces it
# 7. All connections in use and request arrives — request waits up to
#    connectionTimeout, then throws SQLTimeoutException

# ── What happens without proper pool configuration: ───────────────────
# Pool too small: requests queue up → high latency under load
# Pool too large: database overwhelmed → OOM, context switching overhead
# maxLifetime too long: stale connections after DB restart or firewall timeout
# No keepalive: DB closes idle connections → "Communications link failure"

# ── Pool states: ─────────────────────────────────────────────────────
#
#  ┌─────────────────────────────────────────────────────────────┐
#  │                    Connection Pool                           │
#  │                                                             │
#  │  ACTIVE connections    IDLE connections    PENDING borrows   │
#  │  ┌──────────────┐     ┌──────────────┐    ┌─────────────┐  │
#  │  │ conn1 (busy) │     │ conn4 (free) │    │ thread A    │  │
#  │  │ conn2 (busy) │     │ conn5 (free) │    │ thread B    │  │
#  │  │ conn3 (busy) │     └──────────────┘    └─────────────┘  │
#  │  └──────────────┘                          waiting for a    │
#  │                                            free connection  │
#  └─────────────────────────────────────────────────────────────┘
#
#  maximumPoolSize = max(active + idle)
#  minimumIdle     = target number of idle connections to maintain
#  connectionTimeout  = max time a thread waits for a connection
#
# ── What happens when pool is exhausted: ─────────────────────────────
# All N connections are active. New request arrives.
# → Request queues (waits up to connectionTimeout)
# → A connection is returned before timeout → request proceeds
# → Timeout expires before a connection is returned
#   → com.zaxxer.hikari.pool.HikariPool$PoolInitializationException
#      or java.sql.SQLTimeoutException

# ── Pool sizing rule of thumb: ────────────────────────────────────────
# PostgreSQL paper (from Percona):
#   optimal pool size = ((core_count * 2) + effective_spindle_count)
# For most cloud VMs (4-8 cores, SSD): pool size of 10-20 is typical.
# Above 20-30 connections: performance usually degrades (lock contention).
# For read replicas or write-heavy workloads: tune separately.

# ── Pool selection priority: ─────────────────────────────────────────
# 1. HikariCP         — default, included with spring-boot-starter-data-jpa
# 2. Tomcat JDBC      — if HikariCP not present
# 3. DBCP2            — if neither above
# 4. Oracle UCP       — if Oracle JDBC + UCP on classpath

# ── Force a specific pool (override auto-selection): ─────────────────
spring:
  datasource:
    type: com.zaxxer.hikari.HikariDataSource          # HikariCP
    # type: org.apache.tomcat.jdbc.pool.DataSource    # Tomcat JDBC
    # type: org.apache.commons.dbcp2.BasicDataSource  # DBCP2

# ── Verify which pool is active (check startup logs): ─────────────────
logging:
  level:
    com.zaxxer.hikari: DEBUG          # HikariCP configuration logs
    org.springframework.boot.autoconfigure.jdbc: DEBUG

# ── Startup log confirms pool type: ──────────────────────────────────
# HikariPool-1 - Starting...
# HikariPool-1 - Added connection com.mysql.cj.jdbc.ConnectionImpl@1a2b3c4d
# HikariPool-1 - Start completed.

# ── Excluding HikariCP to use Tomcat JDBC: ────────────────────────────
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
    <exclusions>
        <exclusion>
            <groupId>com.zaxxer</groupId>
            <artifactId>HikariCP</artifactId>
        </exclusion>
    </exclusions>
</dependency>
<dependency>
    <groupId>org.apache.tomcat</groupId>
    <artifactId>tomcat-jdbc</artifactId>
</dependency>

# ── application.yml — two datasources: ───────────────────────────────
spring:
  datasource:
    primary:
      url: jdbc:postgresql://primary-db:5432/mydb
      username: appuser
      password: ${PRIMARY_DB_PASSWORD}
      hikari:
        pool-name: primary-pool
        maximum-pool-size: 10
    reporting:
      url: jdbc:postgresql://reporting-db:5432/reports
      username: reporter
      password: ${REPORTING_DB_PASSWORD}
      hikari:
        pool-name: reporting-pool
        maximum-pool-size: 5
        read-only: true    # hint to pool — connections opened read-only

// ── Configuration class — declare both pools: ─────────────────────────
@Configuration
public class DataSourceConfig {

    // Primary datasource — @Primary used by all default auto-configuration:
    @Bean
    @Primary
    @ConfigurationProperties("spring.datasource.primary")
    public DataSourceProperties primaryDataSourceProperties() {
        return new DataSourceProperties();
    }

    @Bean
    @Primary
    @ConfigurationProperties("spring.datasource.primary.hikari")
    public HikariDataSource primaryDataSource(
            @Qualifier("primaryDataSourceProperties")
            DataSourceProperties properties) {
        return properties.initializeDataSourceBuilder()
            .type(HikariDataSource.class)
            .build();
    }

    // Reporting datasource:
    @Bean
    @ConfigurationProperties("spring.datasource.reporting")
    public DataSourceProperties reportingDataSourceProperties() {
        return new DataSourceProperties();
    }

    @Bean
    @ConfigurationProperties("spring.datasource.reporting.hikari")
    public HikariDataSource reportingDataSource(
            @Qualifier("reportingDataSourceProperties")
            DataSourceProperties properties) {
        return properties.initializeDataSourceBuilder()
            .type(HikariDataSource.class)
            .build();
    }
}

// ── Using the secondary datasource: ──────────────────────────────────
@Repository
public class ReportingRepository {

    private final JdbcTemplate reportingJdbc;

    public ReportingRepository(
            @Qualifier("reportingDataSource") DataSource reportingDataSource) {
        this.reportingJdbc = new JdbcTemplate(reportingDataSource);
    }

    public List<Map<String, Object>> getMonthlyRevenue() {
        return reportingJdbc.queryForList(
            "SELECT month, SUM(total) FROM monthly_revenue GROUP BY month");
    }
}

# ── application.yml — expose pool metrics: ───────────────────────────
management:
  endpoints:
    web:
      exposure:
        include: health,metrics,prometheus
  metrics:
    tags:
      application: myapp    # tag all metrics with app name

# ── Key HikariCP metrics (available at /actuator/metrics/...): ────────
# hikaricp.connections              — total connections in pool
# hikaricp.connections.active       — currently in use
# hikaricp.connections.idle         — available for use
# hikaricp.connections.pending      — threads waiting for a connection
# hikaricp.connections.acquire      — time to acquire a connection (histogram)
# hikaricp.connections.creation     — time to create a new connection
# hikaricp.connections.usage        — time connection held by a thread
# hikaricp.connections.timeout.total — total connection timeout events
# hikaricp.connections.max          — maximumPoolSize setting
# hikaricp.connections.min          — minimumIdle setting

# ── Prometheus scrape endpoint — connect to Grafana: ──────────────────
# GET /actuator/prometheus
# hikaricp_connections_active{pool="myapp-pool"}
# hikaricp_connections_pending{pool="myapp-pool"}
# hikaricp_connections_acquire_seconds_max{pool="myapp-pool"}

# ── Alert thresholds (Grafana/Prometheus rules): ──────────────────────
# WARN when:  hikaricp_connections_pending > 0 for > 30 seconds
# ALERT when: hikaricp_connections_pending > 5 for > 60 seconds
# WARN when:  hikaricp_connections_acquire_seconds_max > 1.0 (1 second)
# ALERT when: hikaricp_connections_timeout_total increases > 0

// ── Programmatic pool inspection: ────────────────────────────────────
@Component
@RequiredArgsConstructor
public class PoolMonitor {

    private final DataSource dataSource;

    public void logPoolStats() {
        if (dataSource instanceof HikariDataSource hikari) {
            HikariPoolMXBean pool = hikari.getHikariPoolMXBean();
            log.info("Pool stats — active: {}, idle: {}, pending: {}, total: {}",
                pool.getActiveConnections(),
                pool.getIdleConnections(),
                pool.getThreadsAwaitingConnection(),
                pool.getTotalConnections());
        }
    }
}