Alert info
- Severity
- Critical
- Detected by
- Prometheus JMX Exporter + Grafana
- Alert
- HikariCP active connections at 100% (50/50) for > 2 minutes
- Time
- 2026-03-29 10:22 IST
- Service
- user-service (3-node VM cluster)
HikariCP pools hit 50/50 on app-server-01, 02, and 03 during the morning peak. Investigate the cause.
Missing database index after schema migration caused 42-second queries (from 50ms). Connection pool across 3 app servers exhausted during morning peak, returning HTTP 503 for all users.
- Active Conns
- 50/50
- Query Time
- 42s (was 50ms)
- HTTP 503s
- 2,891
Entities identified
Infrastructure
HikariCP
pool (max 50/node)
Database connection pool for the app cluster.
Flyway V32
add region column
Flyway migration adding region column.
Service
app-server-01
user-service
App server 01 running user-service.
app-server-02
user-service
App server 02 running user-service.
app-server-03
user-service
App server 03 running user-service.
Data
db-primary-01
PostgreSQL 15
PostgreSQL primary instance.
users table
5M rows
PostgreSQL table for users.
Gateway
load-balancer
ingress
Fronts the 3-node cluster.
How they relate
The same relationships as text
- load-balancer routes app-server-01
- load-balancer routes app-server-02
- load-balancer routes app-server-03
- app-server-01 borrows conns HikariCP. Borrows connections from the pool.
- app-server-02 borrows conns HikariCP. Borrows connections from the pool.
- app-server-03 borrows conns HikariCP. Borrows connections from the pool.
- HikariCP connects db-primary-01. Connections to PostgreSQL.
- db-primary-01 getUsersByRegion users table. Queries executed against the users table.
- Flyway V32 altered users table. Migration altered the users table.
Hypotheses tree
Ruled-out branches are collapsed. Open any one to read the check that eliminated it.
Connection leak: app borrows connections and never releases themRuled out
Inspect pg_stat_activity connection states and HikariCP idle counts to verify if connections are actively executing.
All 150 connections are in 'active' state executing queries, not idle or leaked. After the index fix, pools drained within 2 minutes, proving connections were released normally.
Every pooled connection is actively running a query; none are idle-in-transaction.
state | count
-------+-------
active | 150
idle | 0
Not a leak. Connections are busy, not abandoned. Whatever they run is slow.
Connection pool simply sized too small (config problem)Ruled out
Evaluate connection demand at baseline latencies against pool capacity.
At 50ms/query, 85 req/sec needs only ~4 concurrent connections, far inside 150. The pool is not the constraint. After the index dropped query time to 12ms the pool drained immediately.
Demand at the correct query latency is a tiny fraction of capacity.
query time req/sec conns needed pool verdict
50 ms 85 ~4 150 ample
42 s 85 ~3,570 150 exhausted
The pool is correctly sized for 50ms queries. The 42s query, not pool size, is the problem.
Traffic spike alone overwhelmed the serviceRuled out
Compare request rates across different time windows to determine the impact of load.
The spike only mattered because it exposed a latent regression. At 50ms, 85 req/sec is trivial for a 150-connection pool. Ruled out as a standalone cause; it is the trigger, not the cause.
Off-peak demand survived in a degraded state; peak demand tipped it over.
period req/sec conns needed (42s) status
off-peak (night) 12 ~504 degraded
morning peak 85 ~3,570 down
The query was already slow since Mar 22; peak traffic only surfaced it. Trigger, not root cause.
A query regressed and is holding connections far too longConfirmed
Query pg_stat_statements to identify slow queries and compare execution times against baselines.
getUsersByRegion averages 42,341 ms, up from a 50ms baseline. At 42s each, every connection is held 840x longer than normal, exhausting the pool. Confirmed; identify why the query regressed next.
One query pattern dominates total execution time and far exceeds baseline.
query calls mean_exec_time
SELECT * FROM users WHERE region = $1 12,847 42,341 ms
AND status = $2 ORDER BY last_login (was 50 ms)
A single query regressed by ~840x. It pins each connection for 42s.
The query does a full sequential scan on the users tableConfirmed
Execute EXPLAIN ANALYZE on the slow query and check available indexes.
The planner chooses a Seq Scan on 5M rows (41,847 ms), removing 4,755,000 rows by filter. pg_indexes shows no index on the region column. The leading filter column is unindexed.
Full table scan, not an index scan; almost all rows discarded post-scan.
Seq Scan on users (rows=245000 width=312)
Filter: (region = 'us-east' AND status = 'active')
Rows Removed by Filter: 4755000
Execution Time: 41,847 ms
A 5M-row scan per call. No index is being used for the leading filter.
Indexes exist for pkey, email, and status, but none for region.
indexname column
users_pkey id
idx_users_email email
idx_users_status status
(no index on region)
region, the new leading filter, has no index, so the planner falls back to Seq Scan.
Flyway V32 added region without an index while the query was changed to filter by itRoot cause
Compare Flyway migrations and codebase changes to identify schema and query mismatches.
V32 added the region column and backfilled but the CREATE INDEX line was omitted. The query was simultaneously changed to filter by region first, bypassing idx_users_status and forcing a Seq Scan on 5M rows (42s). Pool exhausted, 2,891 HTTP 503s.
Column added and backfilled, but the index statement is commented out.
ALTER TABLE users ADD COLUMN region VARCHAR(50);
UPDATE users SET region = 'us-east' WHERE ...; -- backfill
-- CREATE INDEX idx_users_region ON users(region); <-- OMITTED
The index that the new query depends on was never created.
Filter order changed so an unindexed column leads the predicate.
- WHERE u.status = :status (used idx_users_status)
+ WHERE u.region = :region AND u.status = :status (region unindexed)
Filtering by the unindexed region first defeats idx_users_status and triggers the Seq Scan. Root cause.
Database statistics are stale causing poor query plansRuled out
Run ANALYZE on the users table and re-run EXPLAIN.
Running ANALYZE did not change the query plan; the issue is structurally a missing index, not stale stats.
Final RCA
Flyway V32 added region without an index while the query was changed to filter by it
V32 added the region column and backfilled but the CREATE INDEX line was omitted. The query was simultaneously changed to filter by region first, bypassing idx_users_status and forcing a Seq Scan on 5M rows (42s). Pool exhausted, 2,891 HTTP 503s.
Remediation
# Create the missing composite index (CONCURRENTLY to avoid locks)
ssh db-primary-01 "psql -U postgres -d userdb -c \"
CREATE INDEX CONCURRENTLY idx_users_region_status
ON users(region, status, last_login);\""
# Verify query plan uses the new index
ssh db-primary-01 "psql -U postgres -d userdb -c \"
EXPLAIN ANALYZE SELECT * FROM users
WHERE region = 'us-east' AND status = 'active'
ORDER BY last_login;\""
# Expected: Index Scan using idx_users_region_status
# Expected execution time: ~12ms- Create composite index on (region, status,
last_login) immediately to resolve the sequential scan. - Add a Flyway migration review checklist: any new WHERE clause column must have a corresponding index.
- Add
pg_stat_statementsmonitoring: alert when any querymean_exec_timeexceeds 1 second. - Add HikariCP connection wait time alerting (> 5s) as an early warning before full pool exhaustion.
- Consider adding connection pool metrics to the deployment smoke test: if pool utilization spikes after deploy, auto-rollback.
- Review all queries in the user-service for other missing indexes now that the region column is in use.
The regression shipped because the schema migration and the query change that depended on it were reviewed as two separate, unremarkable changes rather than as one unit: a new leading filter column with no index behind it. A migration checklist that requires an index for any column a WHERE clause references would have caught this before merge. Because the query was slow but not failing for a full week before the morning peak exposed it, alerting on pg_stat_statements mean_exec_time and on HikariCP connection wait time would surface a regression like this in minutes instead of days, well before it has a chance to line up with a traffic peak and take down the pool.
Frequently asked questions
Why did a schema migration cause a connection pool exhaustion incident?
A Flyway migration named V32 added a region column to the users table but never created an index on it. The application query in the same sprint was changed to filter by region first, so PostgreSQL fell back to a full sequential scan across 5 million rows, taking about 42 seconds instead of the previous 50 milliseconds baseline.
How did one slow query exhaust connection pools on three separate app servers?
Each of the 42-second queries held a HikariCP connection open for roughly 840 times longer than the 50ms baseline. Three app servers with a 50-connection pool each provide 150 total connections; at 85 requests per second during the morning peak that translates to roughly 3,570 concurrent connections needed, so the pool of 150 was exhausted almost immediately.
Was the connection pool simply too small for this workload?
No. pg_stat_activity showed all 150 connections in an active state executing queries, none idle or leaked, and connection demand at the correct 50ms query latency was only about 4 concurrent connections against a 150-connection pool. After the missing index was added the pools drained within two minutes, confirming the query, not the pool size, was the constraint.
What actually fixed the 42-second query?
CREATE INDEX CONCURRENTLY on the (region, status, last_login) columns, run without locking the table, dropped the query from 42 seconds to about 12 milliseconds and let the connection pools drain within two minutes. Longer term, a Flyway migration checklist that requires an index for any new WHERE clause column would prevent this exact regression from recurring.