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.
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.
| Choice | What it buys | What it costs |
|---|---|---|
| Trade-off 1 | Small 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;
Pair connectionTimeout.
Pair connectionTimeout: 1500 and statement_timeout = 2000 with a 4-call-per-pod bulkhead on /items/search.
| Choice | What it buys | What it costs |
|---|---|---|
| Trade-off 1 | Queries 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
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.
| Choice | What it buys | What it costs |
|---|---|---|
| Trade-off 1 | Keeping 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);
Implementation & Guardrails (4).
Insert PgBouncer in transaction pooling mode between the 50 pods and PostgreSQL, multiplexing 750 client connections onto 32 server connections.
| Choice | What it buys | What it costs |
|---|---|---|
| Trade-off 1 | Transaction 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
Create a pg_trgm GIN index.
Create a pg_trgm GIN index using CREATE INDEX CONCURRENTLY (or offload LIKE '%widget%' to OpenSearch/read replica).
| Choice | What it buys | What it costs |
|---|---|---|
| Trade-off 1 | GIN 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_timeoutand JDBCsocketTimeout, 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
connectionInitSqlwith PgBouncer transaction mode leaksstatement_timeoutacross tenants unless set at the DB role level.
Sizing Hikaricp Connection Pools — Normal → Load / Fault → Recovery
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.
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.
Putting Sizing & Protecting HikariCP Connection Pools Together
- Set
maximumPoolSize: 15,connectionTimeout: 1500,leakDetectionThreshold: 5000. - Enforce
SET statement_timeout = 2000on every borrowed connection viaconnectionInitSql. - Cap
/items/searchconcurrency at 4 via Resilience4jBulkhead.
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.
Make the trade-off yourself.
Practice Check
Test your understanding
When is ACID Transaction Fundamentals the right default for a production team?
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.
