A slow query from an analytics job held connections open just long enough to starve the production API of its own connection pool.

At 03:14 on February 24th, api-gateway started returning 503s to roughly a third of requests. The cause wasn't the gateway at all — it was a nightly analytics job that had started taking 40x longer than usual and was quietly holding database connections the API needed.

Timeline

03:00:00 — the nightly analytics-rollup job starts, as it does every night, normally completing in 90 seconds. 03:01:30 — instead of finishing, it's still running, because a newly added JOIN against a table that had grown 5x since the query was written is now taking minutes instead of seconds per batch. 03:05:00 — the job's connections (12 of them, held for the query's duration) are part of the same shared pool used by api-gateway. 03:12:00 — pool utilization crosses 95%. 03:14:00 — api-gateway requests start timing out waiting for a connection; 503 rate crosses 30%. 03:14:40 — on-call paged. 03:19:00 — responder identifies the analytics job as the long-held-connection culprit via HelixQL and kills it manually. 03:20:30 — pool recovers, 503s stop.

Root cause

Analytics and production-serving workloads shared one connection pool with no priority separation. A single query that got slow — not even erroring, just slow — was enough to starve latency-sensitive traffic, because both workloads competed for the same fixed number of connections with no isolation.

The fix

We identified the long-held connections with:

from traces
| where span.name == "db.query" and duration > 10s
| summarize count(), max(duration) by service, query.fingerprint
| sort by max_duration desc

analytics-rollup's join query stood out immediately at 4.2 minutes, versus a normal ceiling of 3 seconds for anything else in that pool. We split the pool: production-serving traffic now gets a dedicated pool with a hard 5-second statement timeout, and batch/analytics workloads get a separate pool with its own, much larger, timeout budget.

What changed

We also added a statement_timeout at the database role level for the api-gateway service account, so a future slow query can no longer hold a connection indefinitely regardless of which pool it's in. And we added an alert on pool utilization itself, not just on downstream request errors — utilization crossed 80% four minutes before anything else noticed.

  • Never share a connection pool between latency-sensitive serving traffic and batch/analytics workloads.
  • Set a hard statement_timeout at the database role level as a backstop, not just in application code.
  • Alert on connection pool utilization directly — it leads request-level symptoms by minutes.
  • Re-review batch job query plans whenever their source tables cross a size threshold.