Real Problems
Database Bottleneck Under a Traffic Spike
A campaign multiplied traffic and the Orders API started timing out while its own CPUs sat idle. The database connection pool was the limit. Reproduce it and compare three fixes: an index, read replicas and a cache.
What you will learn
- Recognise connection-pool saturation from latency and queue metrics.
- Find the expensive query before reaching for more hardware.
- Compare indexing, read replicas and caching by capacity gained and cost.
- Plan verification with a load test that reproduces production traffic.
Before you start
Symptoms
- p99 latency of the Orders API rises from ~60 ms to over 400 ms as traffic climbs, then requests time out.
- Application instances show low CPU; adding instances makes things worse.
- Logs:
HikariPool-1 - Connection is not available, request timed out after 3000ms. - Database: all connections active, CPU and I/O high, the same
SELECTdominating the slow query log.
System context
Root cause
The "orders for customer" query had no index on customer_id and scanned the table: ~45 ms per read. With 10 connections the database could serve at most ~10 ÷ 0.045 ≈ 220 reads per second — less once lock and I/O contention grew. When traffic passed that, requests queued for a connection, queue time turned into latency, and latency turned into timeouts. Adding app instances only added more requests competing for the same 10 connections.
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, status, total, created_at
FROM orders
WHERE customer_id = $1
ORDER BY created_at DESC
LIMIT 20;
-- Seq Scan on orders (actual time=0.03..44.9 rows=20 loops=1)
-- Filter: (customer_id = '...')
-- Rows Removed by Filter: 412318Interactive reproduction
Every scenario runs the same ramp: 30 → 120 → 220 → 300 requests per second. Scenario A is the incident. Scenarios B, C and D are the three candidate fixes; compare their p99 and the primary's utilization.
Solution alternatives
| Fix | Capacity gained | Cost and risk |
|---|---|---|
| Add the missing index | ~7× for this query on the same hardware. | A migration (create index concurrently); slightly slower writes. |
| Read replicas | Roughly ×(replicas + 1) for reads. | More servers; replication lag means reads may be stale; the slow query is still slow. |
| Cache hot reads | Reads that hit the cache never reach the database. | Staleness and invalidation; cold-cache risk (see Caching Strategies). |
| Bigger pool | None if the database is already saturated. | More concurrent queries mean more contention — often slower. |
| Bigger database server | Linear-ish, once. | Expensive, a restart, and the query is still wasteful. |
Recommended fix
- Fix the query first: add the index. It is the cheapest change and multiplies capacity.
- Cache the hot, read-mostly data with a short TTL and delete-on-write.
- Add read replicas when reads still outgrow the primary, routing only staleness-tolerant reads to them.
- Keep the connection pool small and set the pool timeout below the request timeout so callers fail fast.
CREATE INDEX CONCURRENTLY idx_orders_customer_created
ON orders (customer_id, created_at DESC);spring:
datasource:
hikari:
maximum-pool-size: 10 # small pools are usually faster; see HikariCP pool sizing
connection-timeout: 500 # ms: fail fast instead of queueing behind a saturated DB
leak-detection-threshold: 2000Advanced A larger pool rarely helps a saturated database: the database has a fixed number of cores and disks, and more concurrent queries just contend for them (the simulation models this as extra latency per concurrent query). HikariCP's guidance starts from connections ≈ cores × 2 + effective spindles on the database side.
Verification strategy
- Compare
EXPLAIN ANALYZEbefore and after: an Index Scan with a few buffer reads instead of a Seq Scan. - Replay production traffic at 2–3× peak with a load tool (Gatling, k6) and assert p99 and pool wait time stay within SLO.
- Watch pool metrics during the next campaign: pending connections should stay at zero.
Monitoring and prevention
- Alert on connection pool pending threads and acquisition time (
hikaricp_connections_pending,hikaricp_connections_acquire_seconds). - Track the top queries by total time (
pg_stat_statements) and review new queries in code review withEXPLAIN. - Capacity-test before marketing launches, not after.
Lessons learned
- The database is usually the first shared bottleneck; app servers scale, the primary does not.
- Look for a missing index before buying hardware.
- Pool wait time is the most direct signal of database saturation.