newkayak12 Connection Pool Tuner
When to Use / When Not to Use
Use when:
- Seeing
Connection timeoutorpool exhaustionerrors under load - API latency spikes correlate with database traffic
- Many idle connections are visible at the database server
- Setting up a new application and need to size the pool correctly
Do not use when:
- The root cause is slow SQL queries (use
sql-pro) - The issue is server-level memory/IO config (use
database-optimizer)
Process
- Identify the symptom — Match to the diagnosis table below
- Apply the sizing formula — Calculate target pool size from DB server specs
- Configure primary settings —
maximumPoolSize,minimumIdle,connectionTimeout,maxLifetime - Enable leak detection — Set
leakDetectionThresholdto 2× P99 query time - Add monitoring — Expose
hikaricp.connections.*metrics; alert onpending > 0sustained - Validate — Watch pool utilization under load; target 60–80% at peak
If sequential-thinking is available, use it to enforce diagnosis before recommending pool size — the most common failure is jumping to maximumPoolSize before applying the formula.
Output Template
For each pool tuning task, provide:
- Recommended property values with rationale
- The sizing formula applied to actual server specs
application.propertiesorpgbouncer.inisnippet- Monitoring query / metrics to track
- Common mistake check against the current config
What Claude Does / What You Do
| Claude | You |
|---|---|
| Applies sizing formula to your DB server specs | Provide DB server core count and disk type |
Recommends maximumPoolSize, minimumIdle, timeouts |
Apply settings and observe under real load |
| Identifies config mistakes from your current settings | Confirm recovery time from your runbooks |
| Generates HikariCP properties or pgBouncer ini snippet | Test with production-scale traffic patterns |
| Recommends Micrometer metrics to alert on | Wire alerts to your monitoring stack |
The Fundamental Sizing Formula
pool_size = (core_count * 2) + effective_spindle_count
core_count: CPU cores available to the DB servereffective_spindle_count: 1 for SSD, 0 if data fits in RAM
Example: 4-core SSD server → (4 * 2) + 1 = 9 → round to 10.
A server handling 1000 concurrent requests does NOT need 1000 DB connections. It needs ~10, with the rest queued.
Symptom Diagnosis
| Symptom | Likely Cause | Investigation |
|---|---|---|
Connection timeout under load |
Pool too small | Check pool active vs. max; look for slow queries holding connections |
| Many idle connections at DB | Pool max too large | Check idleTimeout and minimumIdle settings |
| Slow response at traffic spikes | New connections created under load | Reduce minimumIdle gap or pre-warm the pool |
Connection leak detected warnings |
Code not returning connections | Enable leak detection; audit acquisition paths |
| DB CPU spikes with few active connections | N+1 queries holding connections | Profile queries; check for transactions spanning HTTP requests |
| Errors after DB failover | Stale connections in pool | Enable connection validation; set keepaliveTime |
HikariCP Key Properties
# application.properties (Spring Boot)
spring.datasource.hikari.maximum-pool-size=10
spring.datasource.hikari.minimum-idle=5
spring.datasource.hikari.connection-timeout=30000 # ms: max wait for a pool connection
spring.datasource.hikari.idle-timeout=600000 # ms: idle connections live 10 min
spring.datasource.hikari.max-lifetime=1800000 # ms: max connection lifetime 30 min
spring.datasource.hikari.keepalive-time=60000 # ms: ping to survive firewall NAT
spring.datasource.hikari.leak-detection-threshold=60000 # ms: warn if connection held > 60s
pgBouncer Modes
| Mode | Behavior | Use Case |
|---|---|---|
transaction |
Connection returned after each transaction | Most web applications |
session |
Connection held for entire client session | Temp tables, advisory locks, prepared statements |
statement |
Returned after each statement | Avoid — breaks multi-statement transactions |
Common Mistakes
- Pool size equals thread count — threads spend most time not waiting on the database
minimumIdlemuch lower thanmaximumPoolSize— creates connections under load when latency is criticalmaxLifetimenot set shorter than firewall timeout — silent TCP drops cause cryptic errors on first query- Leak detection disabled in production — one missing
close()eventually exhausts the pool - One pool for all workloads — separate OLTP (short queries) from reporting (long queries)
Related Skills
database-optimizer— server-level tuning after pool config is correctsql-pro— slow query rewriting when pool is correctly sized but queries are still slowcircuit-breaker-tuner— add circuit breakers alongside pool to prevent cascading failure