Skip to main content
← PROBLEMS / ENGINEERING
An end-to-end domain learning journey

Sizing & Protecting
HikariCP Connection Pools.

Your inventory Spring Boot service (10 pods, each with HikariCP maximumPoolSize=20, connectionTimeout=30000) runs on an 8-core PostgreSQL primary. Normally GET /items/{id} takes 4 ms at 1,500 RPS. When an admin triggers an unindexed GET /items/search?q=widget query taking 12 seconds at just 25 RPS, the entire service locks up with HikariPool-1 - Connection is not available, request timed out after 30000ms—even for healthy /items/{id} lookups. Diagnose the starvation and harden the pool.
Start the journey5 stages
Loading diagram…
Sizing & Protecting HikariCP Connection Pools architecture sketch diagram
FIRST PRINCIPLES. PRODUCTION SCALE.
The idea to keep with you

Your inventory Spring Boot service (10 pods, each with `HikariCP maximumPoolSize=20`, `connectionTimeout=30000`) runs on an 8-core PostgreSQL primary. Normally `GET /items/{id}` takes 4 ms at 1,500 RPS. When an admin triggers an unindexed `GET /items/search?q=widget` query taking 12 seconds at just 25 RPS, the entire service locks up with `HikariPool-1 - Connection is not available, request timed out after 30000ms`—even for healthy `/items/{id}` lookups. Diagnose the starvation and harden the pool.

From patterns to decisions.

Your inventory Spring Boot service (10 pods, each with `HikariCP maximumPoolSize=20`, `connectionTimeout=30000`) runs on an 8-core PostgreSQL primary. Normally `GET /items/{id}` takes 4 ms at 1,500 RPS. When an admin triggers an unindexed `GET /items/search?q=widget` query taking 12 seconds at just 25 RPS, the entire service locks up with `HikariPool-1 - Connection is not available, request timed out after 30000ms`—even for healthy `/items/{id}` lookups. Diagnose the starvation and harden the pool.

01
ENGINEERING · STAGE 01L4 · FUNDAMENTALS

By Little's Law (L =.

By Little's Law (L = λ × W), 25 RPS × 12s requires 300 concurrent connections—exceeding all 200 connections across 10 pods and leaving zero connections for 4 ms lookups.

ChoiceWhat it buysWhat it costs
Trade-off 1Small pools protect DB CPU, but require strict query latency bounds (`W`).

Step 1

Compute 25 RPS × 12s = 300 required connections > 200 available.

Step 2

Observe that 200 Tomcat threads per pod then block for 30,000 ms (connectionTimeout).

Alternative 1

Route search traffic to a separate read-replica DataSource.

Failure Mode 1

Tomcat threads hang for 30s, triggering upstream 504 Gateway Timeouts.

-- Identify active slow queries starving the pool
SELECT pid, now() - query_start AS duration, state, left(query, 80)
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY duration DESC
LIMIT 10;
02
ENGINEERING · STAGE 02L5 · APPLIED DESIGN

Pair connectionTimeout.

Pair connectionTimeout: 1500 and statement_timeout = 2000 with a 4-call-per-pod bulkhead on /items/search.

ChoiceWhat it buysWhat it costs
Trade-off 1Queries exceeding 2s abort with `QueryTimeoutException` instead of starving the entire pod.

Set `maximumPoolSize

15, connectionTimeout: 1500, leakDetectionThreshold: 5000`.

Step 2

Enforce SET statement_timeout = 2000 on every borrowed connection via connectionInitSql.

Step 3

Cap /items/search concurrency at 4 via Resilience4j Bulkhead.

Alternative 1

Maintain two separate HikariDataSource beans: oltpDataSource (size 15) and searchDataSource (size 4, pointing to replica).

Failure Mode 1

Forgetting socketTimeout leaves JDBC threads stuck if the DB host drops packets silently.

spring:
  datasource:
    hikari:
      maximum-pool-size: 15
      minimum-idle: 15
      connection-timeout: 1500
      leak-detection-threshold: 5000
      connection-init-sql: "SET statement_timeout = 2000"
      data-source-properties:
        socketTimeout: 3
resilience4j:
  bulkhead:
    instances:
      itemSearch:
        maxConcurrentCalls: 4
        maxWaitDuration: 0ms
03
ENGINEERING · STAGE 03L6 · SCALE & TRADE-OFFS

1,000 concurrent queries on an.

1,000 concurrent queries on an 8-core PostgreSQL server cause severe CPU thrashing, lock contention, and memory bloat (~10 MB/backend = 10 GB), making every query—including /items/{id}—take seconds.

ChoiceWhat it buysWhat it costs
Trade-off 1Keeping pools small preserves peak DB throughput, requiring slow queries to be bulkheaded or indexed.

Apply PostgreSQL sizing formula

(8 cores × 2) + 4 = ~20 optimal active connections.

Step 2

Show that 1,000 active backends increases context-switching and L3 cache misses.

Alternative 1

Place PgBouncer in transaction pooling mode in front of PostgreSQL.

Failure Mode 1

Over-sized pools exceed max_connections = 300, causing FATAL: sorry, too many clients already.

// Optimal pool sizing rule of thumb (PostgreSQL wiki / HikariCP)
int optimalFleetConnections = (dbCores * 2) + effectiveSpindleCount;
int perPodPoolSize = Math.max(5, optimalFleetConnections / appPodCount);
04
ENGINEERING · STAGE 04L6 · SCALE & TRADE-OFFS

Implementation & Guardrails (4).

Insert PgBouncer in transaction pooling mode between the 50 pods and PostgreSQL, multiplexing 750 client connections onto 32 server connections.

ChoiceWhat it buysWhat it costs
Trade-off 1Transaction pooling prevents session-level `SET` commands unless using `SET LOCAL` inside transactions.

Step 1

Deploy PgBouncer with pool_mode = transaction, max_client_conn = 2000, default_pool_size = 32.

Step 2

Disable server-side prepared statements in JDBC (prepareThreshold=0) if using older PgBouncer.

Alternative 1

Use AWS RDS Proxy or PostgreSQL 15+ connection pooling.

Failure Mode 1

Using session-scoped connectionInitSql with PgBouncer transaction mode leaks statement_timeout across tenants unless set at the DB role level.

# pgbouncer.ini
pool_mode = transaction
max_client_conn = 2000
default_pool_size = 32
reserve_pool_size = 8
reserve_pool_timeout = 2
05
ENGINEERING · STAGE 05L7 · STAFF / PRINCIPAL

Create a pg_trgm GIN index.

Create a pg_trgm GIN index using CREATE INDEX CONCURRENTLY (or offload LIKE '%widget%' to OpenSearch/read replica).

ChoiceWhat it buysWhat it costs
Trade-off 1GIN trigram indexes speed up `%substring%` queries from 12s to ~15 ms, at the cost of slower `INSERT`/`UPDATE`.

Step 1

Run EXPLAIN (ANALYZE, BUFFERS) to confirm the Seq Scan.

Step 2

Enable pg_trgm and build CREATE INDEX CONCURRENTLY with lock_timeout = '2s'.

Alternative 1

Sync items to Elasticsearch/OpenSearch via Debezium CDC.

Failure Mode 1

Building a GIN index without CONCURRENTLY blocks all writes on items.

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX CONCURRENTLY idx_items_name_trgm ON items USING gin (name gin_trgm_ops);

Theory & Tips and Tricks

Practical heuristics & interview traps

  • 01Interview anchor: Enforce strict DB statement_timeout and JDBC socketTimeout, size pools using Little's Law ((cores * 2) + spindles) rather than inflating them, and isolate slow endpoints with bulkheads.
  • 02Trade-off tip: Keeping pools small preserves peak DB throughput, requiring slow queries to be bulkheaded or indexed.
  • 03Watch out for: Using session-scoped connectionInitSql with PgBouncer transaction mode leaks statement_timeout across tenants unless set at the DB role level.
Behavioral Progression · Sizing Hikaricp Connection Pools

Sizing Hikaricp Connection Pools — Normal → Load / Fault → Recovery

Send Request
1. Normal Request Ingress
Traffic: 1x baseline · Latency (p99): 4 ms
Process State
2. Core Execution & State Lookup
Traffic: 1x baseline · Latency (p99): 9 ms
Inject 10x Spike
3. Scale Spike / Failure Injection
Traffic: 10x burst · Latency (p99): 185 ms

Enforce strict DB statementtimeout and JDBC socketTimeout, size pools using Little's Law ((cores 2) + spindles) rather than inflating them, and isolate slow endpoints with bulkheads.

Synthesis / One connected design

Putting Sizing & Protecting HikariCP Connection Pools Together

Enforce strict DB `statement_timeout` and JDBC `socketTimeout`, size pools using Little's Law (`(cores * 2) + spindles`) rather than inflating them, and isolate slow endpoints with bulkheads.

A candidate architecture, not a universal answer

Putting Sizing & Protecting HikariCP Connection Pools Together

Step 1
Step 2
Step 3
  1. Set maximumPoolSize: 15, connectionTimeout: 1500, leakDetectionThreshold: 5000.
  2. Enforce SET statement_timeout = 2000 on every borrowed connection via connectionInitSql.
  3. Cap /items/search concurrency at 4 via Resilience4j Bulkhead.
Insert **PgBouncer** in `transaction` pooling mode between the 50 pods and PostgreSQL, multiplexing 750 client connections onto 32 server connections.
Interview Readiness Check

Insert PgBouncer in transaction pooling mode between the 50 pods and PostgreSQL, multiplexing 750 client connections onto 32 server connections.

Walk through bottlenecks, back-of-the-envelope math, and failure recovery.

Practice / Explain the consequences

Make the trade-off yourself.

Practice Check

Test your understanding

Question 1 of 3

When is ACID Transaction Fundamentals the right default for a production team?

Key takeaway

Sizing & Protecting HikariCP Connection Pools

Enforce strict DB statement_timeout and JDBC socketTimeout, size pools using Little's Law ((cores * 2) + spindles) rather than inflating them, and isolate slow endpoints with bulkheads.