HikariCP pool tuning untuk Postgres

HikariCP default config tidak optimal untuk production. Pool 10 connection dengan 50 instance Spring Boot = 500 connection ke Postgres, padahal Postgres max biasanya 100. App akan crash diam-diam saat traffic naik. Snippet ini config production-ready + monitoring metrics.

Kode

# application.yml
spring:
  datasource:
    url: jdbc:postgresql://db.tangerang.id:5432/tokopedia?reWriteBatchedInserts=true&sslmode=require
    username: ${DB_USER}
    password: ${DB_PASSWORD}
    driver-class-name: org.postgresql.Driver

    hikari:
      # ==========================================
      # POOL SIZE — paling penting
      # ==========================================
      # Formula: connections = (cpu_cores * 2) + effective_disk_count
      # Untuk app modern dengan SSD: cpu * 2 + 1 cukup
      # Kalau 4 cpu instance: 4 * 2 + 1 = 9 → bulatkan 10
      maximum-pool-size: 20
      minimum-idle: 5            # Idle minimum supaya warm-up tidak nyiksa

      # ==========================================
      # TIMEOUT — bagaimana app behave saat pool exhausted
      # ==========================================
      # Max wait untuk dapat connection — fail fast kalau pool kepenuhan
      connection-timeout: 3000   # 3 detik

      # Berapa lama connection idle sebelum di-close
      # Trade-off: pendek = nyaman ke DB, panjang = warm pool
      idle-timeout: 600000       # 10 menit

      # Connection lifetime maksimum — workaround firewall NAT idle
      # Cloud Postgres (RDS, Cloud SQL) biasanya kasih 30 menit idle limit
      max-lifetime: 1800000      # 30 menit (< server's idle_in_transaction)

      # Test query — verify connection valid sebelum kasih ke app
      validation-timeout: 3000
      connection-test-query: "SELECT 1"   # Optional untuk pgjdbc-ng

      # ==========================================
      # LEAK DETECTION — penting di staging
      # ==========================================
      # Warning kalau connection dipinjam > 30 detik (kemungkinan leak)
      leak-detection-threshold: 30000

      # ==========================================
      # PROPERTIES Postgres-specific
      # ==========================================
      data-source-properties:
        # Server-side prepared statement caching
        prepareThreshold: 5
        preparedStatementCacheQueries: 256
        preparedStatementCacheSizeMiB: 5

        # TCP keepalive — detect connection mati lebih cepat
        tcpKeepAlive: true

        # Application name muncul di pg_stat_activity — debug friendly
        ApplicationName: tokopedia-api

        # Schema search path
        currentSchema: public,audit

      # Pool name untuk debug
      pool-name: tokopedia-hikari

  jpa:
    properties:
      hibernate:
        jdbc:
          batch_size: 50
          order_inserts: true
          order_updates: true

// HikariMetricsConfig.java — expose metric ke Prometheus
package id.kodekarawaci.config;

import com.zaxxer.hikari.HikariDataSource;
import com.zaxxer.hikari.metrics.micrometer.MicrometerMetricsTrackerFactory;
import io.micrometer.core.instrument.MeterRegistry;
import jakarta.annotation.PostConstruct;

import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Configuration;

@Configuration
public class HikariMetricsConfig {

    @Autowired private DataSource dataSource;
    @Autowired private MeterRegistry meterRegistry;

    @PostConstruct
    public void registerMetrics() {
        if (dataSource instanceof HikariDataSource hikari) {
            hikari.setMetricsTrackerFactory(new MicrometerMetricsTrackerFactory(meterRegistry));
        }
    }
}

// LeakWarningHandler.java — capture leak warning di log
package id.kodekarawaci.config;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.config.BeanPostProcessor;
import org.springframework.stereotype.Component;
import com.zaxxer.hikari.HikariDataSource;

@Component
public class HikariLeakConfig implements BeanPostProcessor {
    private static final Logger LOG = LoggerFactory.getLogger(HikariLeakConfig.class);

    @Override
    public Object postProcessAfterInitialization(Object bean, String beanName) {
        if (bean instanceof HikariDataSource hikari) {
            LOG.info("HikariCP pool initialized: maxPoolSize={}, minIdle={}, connectionTimeout={}ms",
                hikari.getMaximumPoolSize(),
                hikari.getMinimumIdle(),
                hikari.getConnectionTimeout());
        }
        return bean;
    }
}

Pemakaian

# Cek metric pool di Prometheus
curl http://localhost:8080/actuator/prometheus | grep hikari

# hikaricp_connections_active{pool="tokopedia-hikari"} 8
# hikaricp_connections_idle{pool="tokopedia-hikari"} 12
# hikaricp_connections_pending{pool="tokopedia-hikari"} 0
# hikaricp_connections_acquire_seconds_sum{pool="tokopedia-hikari"} 1.23

# Query Prometheus — pool utilization
hikaricp_connections_active{pool="tokopedia-hikari"}
  / hikaricp_connections_max{pool="tokopedia-hikari"} * 100

# Alert kalau pending > 0 selama > 5 menit (pool exhaustion)
hikaricp_connections_pending{pool="tokopedia-hikari"} > 0

-- Cek di Postgres side — ada berapa connection dari app
SELECT
  application_name,
  state,
  COUNT(*) AS conn_count,
  MAX(state_change) AS last_state_change
FROM pg_stat_activity
WHERE application_name LIKE 'tokopedia%'
GROUP BY application_name, state;

-- application_name | state               | conn_count
-- tokopedia-api    | active              | 8
-- tokopedia-api    | idle                | 12
-- tokopedia-api    | idle in transaction | 0    -- ini wajib 0

# Test leak detection di staging
# Set leak-detection-threshold: 5000 (5 detik untuk testing)
# Trigger leak: query tanpa try-with-resources

# Log akan show:
# WARN [HikariPool] tokopedia-hikari - Connection leak detection triggered
#   for connection org.postgresql.jdbc.PgConnection@xxx, stack trace follows
#   java.lang.Exception
#     at OrderService.findOrder(OrderService.java:42)

Kapan dipakai

• Production Spring Boot dengan throughput > 100 req/s.
• Multi-instance deployment — perlu hitung total connection capacity.
• Aplikasi yang punya batch job + web request bareng (potential resource war).
• Setelah migrate ke cloud DB (RDS, Cloud SQL) yang punya idle timeout.

Catatan

• Total connection = pool_size × instance_count — kalau punya 10 instance dengan pool 30, total 300 connection ke Postgres. Cek max_connections Postgres (default 100).
• maximumPoolSize ≠ minimumIdle — pool grow on demand sampai max, shrink ke min saat idle. Set min minimum 5-10 supaya warm-up cepat.
• max-lifetime < server idle timeout — Cloud Postgres punya 30 menit idle timeout. Set HikariCP 25-28 menit supaya rotate dulu sebelum DB tutup paksa.
• leak-detection-threshold wajib di staging — catch leak code lebih awal. Production set ke 60 detik supaya tidak false positive di long query.
• prepareThreshold Postgres-specific — query yang dipanggil > N kali akan di-prepare server-side. Hemat parsing.
• idle in transaction harus 0 di Postgres. Kalau lebih, ada code yang lupa commit/rollback — leak slot.
• Connection vs CPU — lebih banyak connection bukan berarti lebih cepat. DB satu thread per connection — terlalu banyak bikin contention.

“Pool size optimal” dependent on workload. Profile dulu dengan k6 / wrk, cek hikaricp_connections_pending. Kalau > 0 saat load normal, tambah pool size. Kalau selalu 0, kurangi (hemat resource).