Dispatch API partial outage in the EU region, 2026-08-27
A migration added a column that a new assignment query filtered on without an index. Under afternoon peak the query scanned the full jobs table, exhausted the database connection pool, and 38% of assignment requests failed for 41 minutes.
Issue summary
Between 14:02 and 14:43 UTC on 2026-08-27, the Dispatch API in the EU region returned HTTP 503 for 38% of POST /v2/jobs/{id}/assign requests and for 11% of all other write requests. Technicians using the mobile app in the region could not receive new assignments; dispatchers saw "assignment pending" for jobs they had already routed. Reads were unaffected. No data was lost or corrupted.
Deploy 2026.08.27-3 shipped migration 0412_add_jobs_assigned_at and a new query in the assignment service that filtered on the new column. The column had no index. At EU afternoon peak the query took 2.1 to 4.8 seconds per call, held connections for the duration, and saturated the 60-connection pool. Mitigation was an index created concurrently at 14:31 UTC; the pool recovered by 14:38 UTC.
The fault began at 14:02 UTC. The first alert fired at 14:06 UTC and was acknowledged at 14:11 UTC. Nine minutes elapsed between fault and acknowledgement; the pool-saturation threshold of 95% is addressed in action A-3.
Timeline
All times UTC. Sources: deploy log, PagerDuty timeline, incident channel #inc-2026-0827.
PR #4187 merged: adds
jobs.assigned_atand a query inAssignmentService.pending_for_region()filteringWHERE assigned_at IS NULL AND region_id = $1. Review noted the column; nobody noted the missing index.Deploy
2026.08.27-3to canary (US-West, 5% traffic). Canary p95 for the assignment endpoint rises from 140 ms to 210 ms; within the 300 ms budget, so promotion continues.Deploy reaches EU. The
jobstable in EU holds 9.4 million rows versus 1.1 million in US-West canary.EU afternoon peak begins. Assignment query latency climbs to 2.1 to 4.8 s. First 503 responses as the connection pool (60 connections, 30 s checkout timeout) fills.
Alert
DispatchDBPoolSaturation > 95% for 3mfires and pages the on-call.On-call acknowledges. Incident channel opened; SEV-2 declared at 14:14.
pg_stat_statementsshows one query accounting for 71% of total execution time since 14:00 (see RCA-004). Query plan confirms a sequential scan onjobs.Decision: create the index concurrently rather than roll back, because rollback would also revert migration 0412 and the mobile client already writes the new field. Estimated index build 3 to 5 minutes.
CREATE INDEX CONCURRENTLY jobs_region_unassigned_idx ON jobs (region_id) WHERE assigned_at IS NULL;started.Index build completes. Query latency drops to 8 ms. Pool checkouts begin to succeed.
Pool utilisation below 60%. Mobile clients retrying with backoff drain the backlog.
Error rate below 0.1% for five consecutive minutes. Impact window closed.
Incident closed. Same index applied to US-West, US-East, and APAC ahead of their peaks.
Migration
0413adds the index declaratively so fresh environments match production.
Root cause findings
Five whys
- Why did assignment requests fail?The application could not obtain a database connection within the 30 s checkout timeout and returned 503.
- Why were no connections available?Each call to
pending_for_region()held a connection for 2 to 5 s instead of milliseconds, so 60 connections served roughly 15 requests per second against a peak demand of 40. - Why was the query slow?It filtered on
assigned_at IS NULL, a column added in the same deploy with no supporting index, forcing a sequential scan of 9.4 million rows. - Why did the missing index reach production?Code review has no checklist item for query plans, and the canary region's table is one ninth the size of EU's, so the canary stayed inside its latency budget.
- Why does canary not represent production data volume?Canary was designed to catch crashes and error-rate regressions, not data-dependent performance; no stage in the pipeline runs new queries against a production-sized dataset.
Findings
New query filtered on an unindexed column
Root cause- Component
dispatch-api·AssignmentService.pending_for_region()· migration0412- Significance
- Directly produced the 2 to 5 s query time that exhausted the pool. Without it, the remaining findings would not have caused an outage.
The query SELECT id, customer_id, window_start FROM jobs WHERE assigned_at IS NULL AND region_id = $1 ORDER BY window_start LIMIT 200 used the existing jobs_region_idx to narrow by region, then filtered 1.6 million EU rows by assigned_at on the heap. A partial index on (region_id) WHERE assigned_at IS NULL reduces the candidate set to the roughly 3,000 unassigned jobs at any moment.
Pool checkout timeout amplified the failure
Contributing factor- Component
dispatch-apiconnection pool configuration (DB_POOL_SIZE=60,DB_POOL_TIMEOUT=30s)- Significance
- Requests waited the full 30 s before failing, so mobile clients held open sockets and dispatch consoles appeared frozen rather than showing a fast error and retrying.
A 30 s checkout timeout suits batch jobs, not an interactive endpoint with a 300 ms budget. With a 2 s timeout the same fault would have produced faster failures, a clearer error signature, and roughly 40% fewer queued requests during the window.
Canary data volume does not represent the largest region
Contributing factor- Component
- Deploy pipeline · canary stage (US-West, 5%)
- Significance
- The regression was measurable in canary (p95 140 ms to 210 ms) but stayed within budget because the table was small. Promotion proceeded automatically.
The canary budget is absolute (300 ms) rather than relative. A relative rule ("p95 must not rise more than 25% versus the previous deploy") would have held promotion for review.
pg_stat_statements during the window
Evidence- Evidence type
- Database query statistics
- Source
- EU primary, captured 14:19 UTC by the on-call
queryid calls total_exec_time mean_exec_time rows
----------- ----- --------------- -------------- ------
8812043771 1147 3,204,118 ms 2,793.5 ms 229,400 <- pending_for_region
2210984510 48211 401,022 ms 8.3 ms 48,211
...
Seq Scan on jobs (cost=0.00..412884.10 rows=3121 width=32)
Filter: ((assigned_at IS NULL) AND (region_id = 'eu'))
Rows Removed by Filter: 1,598,204Alert fired four minutes after the fault began
Evidence- Evidence type
- Metric and alert timeline
- Source
- Grafana panel Dispatch DB pool; PagerDuty incident P-77120
Pool utilisation crossed 80% at 14:02:40 and 95% at 14:03:10. The alert requires 95% for three minutes, so it fired at 14:06:10. An 80%-for-two-minutes warning would have fired at 14:04:40 with a lower-urgency page.
Impact analysis
Systems affected
- Dispatch API (EU): 38% of assignment writes and 11% of other writes returned 503 for 41 minutes.
- Technician mobile app: assignment feed stale; the app retried with backoff and recovered without user action.
- Dispatcher console: "assignment pending" state; no incorrect assignments were displayed.
- Other regions: none. Reporting and billing unaffected.
Users affected
- 1,912 technicians in the EU region did not receive new assignments during the window; 640 of them had at least one job whose start time fell inside it.
- 214 customer accounts; 31 contacted support. Two accounts on premium SLAs are eligible for credits (Finance notified 2026-08-28).
Data impact
- Modified: no. Failed writes were rejected before any statement ran.
- Lost: no. Clients retried; 100% of queued assignments landed by 14:52 UTC, verified by reconciling client request IDs against
jobs.assigned_at. - Corrupted: no.
Corrective actions
| # | Action | Addresses | Owner | Due | Status |
|---|---|---|---|---|---|
| A-1 | Add partial index jobs_region_unassigned_idx in all regions; add migration 0413 | RCA-001 | Tomasz W. | 2026-08-28 | Done |
| A-2 | Lower DB_POOL_TIMEOUT to 2 s for interactive services; keep 30 s for batch workers | RCA-002 | Priya N. | 2026-09-05 | Done |
| A-3 | Add 80%-for-2-minutes warning alert on pool utilisation; keep the 95% page | RCA-005 | Priya N. | 2026-09-05 | Done |
| A-4 | Canary promotion rule: hold if p95 rises more than 25% versus the previous deploy, in addition to the absolute budget | RCA-003 | Ines F. | 2026-09-19 | In progress |
| A-5 | CI job runs EXPLAIN for new or changed queries against a production-sized snapshot and fails on sequential scans over tables above one million rows | RCA-001, RCA-003 | Tomasz W. | 2026-10-03 | Planned |
| A-6 | Add "query plan reviewed" to the PR template for changes touching db/ or repository classes | RCA-001 | Ines F. | 2026-09-12 | Done |
Gaps and open questions
Whether the 11% failure rate on non-assignment writes shared the same cause
Open- Description
- Other write endpoints use the same pool, so pool exhaustion explains most of it, but
PATCH /v2/jobs/{id}shows a 14:00 to 14:02 latency rise that predates saturation. - Impact
- Low. Does not change the root cause; may reveal a second slow query.
- Resolution
- Yuki Tanabe to replay the window from query logs by 2026-09-09.
Assumptions
Client retry reconciliation is complete
- Assumption
- Every assignment attempted during the window was retried by the mobile client and landed by 14:52 UTC.
- Impact if wrong
- Some jobs would show no technician despite a dispatcher having routed them; support would see "unassigned" complaints after 14:52.
- Validation
- Reconciled 100% of client request IDs logged between 14:00 and 14:45 against
jobs.assigned_at; zero unmatched. Support ticket search for the following 48 hours found none. - Related items
- RCA-002