# Postmortem: Spring Bird Count Submission Outage

**Incident date:** May 11, 2026  
**Duration:** 2 hours 47 minutes  
**Severity:** SEV-1  
**Services affected:** Survey submission API, mobile synchronization, reviewer dashboard  
**Status:** Resolved

## Summary

During the peak weekend of the Spring Bird Count, participants were unable to submit new checklists or synchronize observations from the mobile application. The reviewer dashboard also displayed incomplete regional totals.

The incident began when a routine database migration introduced an index that significantly increased write amplification on the observations table. Peak submission traffic exhausted the primary database's available I/O capacity. Application retries then amplified the load, preventing the database from recovering until submissions were temporarily paused and the index was removed.

No confirmed observations were lost. Mobile users who retained their pending surveys were able to synchronize them after recovery. A small number of users discarded local drafts after receiving repeated errors and may need to recreate their submissions.

## Customer Impact

From 09:18 to 12:05 EDT:

- Approximately 18,400 participants encountered delayed or failed submissions.
- 31,762 checklist submission attempts returned errors or timed out.
- 12,906 unique checklists were queued locally and synchronized after recovery.
- An estimated 640 web submissions required users to retry manually.
- Regional coordinators saw totals lag by up to three hours.
- Public species maps and daily leaderboards were stale during the incident.

Existing observations remained readable for most of the outage, although map requests were intermittently slow. Authentication, account management, and historical data exports were unaffected.

## Detection

At 09:21 EDT, automated monitoring alerted on elevated submission latency and database I/O utilization. The on-call engineer acknowledged the alert at 09:24.

The first participant support ticket arrived at 09:20, one minute before the automated alert. Our monitoring detected the infrastructure symptom quickly, but the alert did not identify the recent migration or the retry storm as contributing factors.

## Timeline

All times are in Eastern Daylight Time.

- **08:42** — Database migration `20260511_add_observation_review_index` completes on the production primary.
- **09:00** — Scheduled Spring Bird Count reminder notifications begin.
- **09:12** — Submission traffic rises to approximately four times the normal weekday peak.
- **09:18** — Database write latency exceeds five seconds; submission timeouts begin.
- **09:20** — Support receives the first participant report of failed synchronization.
- **09:21** — Monitoring pages the on-call engineer for submission API latency and database I/O saturation.
- **09:24** — Incident acknowledged; SEV-1 response begins.
- **09:31** — Engineers identify high database disk utilization and a growing queue of retried writes.
- **09:39** — Application capacity is increased. This does not improve availability and adds database connections.
- **09:47** — The team disables background map aggregation to reduce database load.
- **10:02** — Read performance improves, but submission errors continue.
- **10:14** — Query analysis identifies the newly created observation review index as the source of elevated write cost.
- **10:27** — Mobile and web clients are placed in maintenance mode for new submissions. Reads remain available.
- **10:36** — Database load begins to decline after retry traffic is interrupted.
- **10:48** — Engineers start dropping the problematic index.
- **11:16** — Index removal completes; write latency returns to normal.
- **11:25** — Submissions are re-enabled for 10% of traffic.
- **11:38** — Submission traffic is increased to 50%; queued mobile surveys begin synchronizing successfully.
- **11:52** — Submissions are restored for all users.
- **12:05** — Error rates and queue depth return to baseline; incident declared resolved.
- **14:41** — Delayed regional aggregations finish processing.
- **16:10** — Data-integrity checks confirm that all server-accepted checklists are present and consistent.

## Root Cause

The direct cause was a new partial index on the observations table:

```sql
CREATE INDEX CONCURRENTLY observation_review_queue_idx
ON observations (region_id, submitted_at)
WHERE review_status = 'pending';
```

The index was intended to improve the reviewer dashboard. However, nearly all newly submitted observations initially have a `pending` review status, so every incoming observation required an additional index write. The index's leading `region_id` column also produced poor page locality during the nationally distributed traffic spike.

Under ordinary traffic, staging tests and post-deployment monitoring showed only a modest increase in write latency. During the Spring Bird Count peak, the additional random writes saturated the database's provisioned I/O. Submission transactions slowed and began timing out.

Both the API and mobile synchronization worker retried timed-out requests with insufficient backoff. Because some timeouts occurred while the database was still processing the original transaction, retries increased concurrent work and connection usage. This feedback loop kept the primary database saturated after the initial traffic spike.

## Contributing Factors

- The migration was scheduled on the morning of a known high-traffic survey weekend.
- Load tests used average daily traffic and did not model notification-driven bursts.
- Staging data did not reflect the production distribution of pending observations by region.
- The migration review focused on read-query improvement and storage size, not write amplification.
- API retries used fixed delays without jitter and did not enforce a global retry budget.
- Scaling application instances increased pressure on the already constrained database.
- The reviewer dashboard and participant submissions shared the same primary database resources.
- The incident runbook did not include a fast procedure for suspending writes while preserving local mobile drafts.

## Resolution and Recovery

We stopped new submissions to break the retry loop, disabled nonessential aggregation jobs, and removed the new index. Once database latency stabilized, we gradually restored submission traffic while monitoring write latency, connection count, and retry volume.

Queued mobile submissions synchronized automatically. Web users whose requests failed before server acknowledgement were prompted to retry. We ran reconciliation checks against API request logs, submission receipts, and stored checklists; these checks found no loss of server-accepted data and no duplicate checklists created by retries.

## What Went Well

- Automated monitoring detected the outage within three minutes.
- Mobile clients retained most failed submissions locally.
- Submission identifiers made retries idempotent and prevented duplicate checklists.
- Disabling background aggregation reduced read contention during recovery.
- Gradual traffic restoration allowed the team to verify database stability before full reopening.
- Regional coordinators helped communicate status to participants through established channels.

## What Went Poorly

- A production database migration was allowed immediately before a major survey event.
- Initial application scaling worsened database connection pressure.
- Dashboards showed database saturation but not write amplification by index.
- Participant-facing error messages implied that submissions might have been lost.
- The public status page was not updated until 43 minutes after the incident began.
- Support staff lacked guidance for users with locally queued mobile surveys.

## Action Items

| Action | Owner | Priority | Due Date |
|---|---|---:|---:|
| Add a change freeze covering 48 hours before and during major survey events | Engineering Manager | P0 | May 18, 2026 |
| Replace fixed submission retries with exponential backoff, jitter, and retry budgets | API Team | P0 | May 25, 2026 |
| Add database write-amplification checks to migration review and CI | Data Platform | P0 | June 1, 2026 |
| Load-test notification-driven submission bursts at twice the forecast peak | Reliability Team | P0 | June 8, 2026 |
| Add an operational switch that pauses server writes while preserving client drafts | Mobile and Web Teams | P1 | June 15, 2026 |
| Separate reviewer analytics workloads from the transactional primary | Data Platform | P1 | July 20, 2026 |
| Alert on per-index write rate, I/O queue depth, and retry amplification | Observability Team | P1 | June 5, 2026 |
| Cap application database connections independently of instance count | API Team | P1 | May 29, 2026 |
| Update submission errors to clearly distinguish queued, rejected, and unknown states | Product Team | P1 | June 12, 2026 |
| Add status-page updates to the SEV-1 checklist with a 15-minute target | Incident Management | P1 | May 20, 2026 |
| Publish support guidance for offline and queued survey submissions | Participant Support | P2 | May 22, 2026 |
| Run a quarterly recovery exercise using peak survey traffic and a degraded database | Reliability Team | P2 | August 1, 2026 |

## Lessons Learned

For this service, event timing is part of system capacity. A change that is safe under ordinary traffic may be unsafe during a coordinated survey window, especially when reminders create abrupt submission bursts.

We also need to treat retries as additional load, not merely as a resilience mechanism. Future capacity tests and incident dashboards will include retry volume and database work per accepted submission so that feedback loops are visible before they become outages.