Postmortem Analyst

# Postmortem: Database Cluster Failure

**Incident ID**: INC-20240120-0001
**Severity**: SEV0
**Duration**: 17 minutes
**Impact**: Complete service outage

## Root Cause Analysis

### Primary Root Cause

The primary database cluster failed due to out-of-memory (OOM) conditions 
triggered by a memory leak in the connection pooler software. The leak 
accumulated over 72 hours following a routine Railway platform update.

### Fishbone Diagram Analysis

**Effect**: Database cluster failure

**People**:
- On-call rotation didn't include database specialist

**Process**:
- No staged rollout for platform updates
- Memory monitoring alert threshold too high (95% vs 85%)

**Technology**:
- Pooler software memory leak (vendor bug)
- No circuit breaker on pooler layer
- Insufficient memory headroom for spike

**External**:
- Railway platform update introduced regression

### Contributing Factors

| Factor | Category | Preventable |
|--------|----------|-------------|
| Railway platform update regression | external | Partial |
| Memory alert threshold too high | technology | Yes |
| No database specialist on-call | process | Yes |
| Insufficient memory headroom | technology | Yes |
| No circuit breaker | technology | Yes |

## Action Items

| ID | Type | Description | Owner | Priority |
|----|------|-------------|-------|----------|
| AI-001 | prevent | Lower memory alert to 85% | @platform | P0 |
| AI-002 | prevent | Add database specialist to on-call | @eng-mgr | P0 |
| AI-003 | detect | Implement pooler health checks | @platform | P0 |
| AI-004 | mitigate | Add circuit breaker to pooler | @backend | P1 |
| AI-005 | prevent | Increase memory headroom 20% | @platform | P1 |
| AI-006 | process | Coordinate with Railway on updates | @platform | P1 |

## Lessons Learned

1. **Memory leaks manifest gradually** - Need trend monitoring, not just thresholds
2. **Vendor updates can introduce regressions** - Staged rollout with monitoring
3. **Circuit breakers at every layer** - Even connection poolers need protection
4. **Specialist expertise matters** - Database incidents need database experts

## Metrics

| Metric | Value | Target |
|--------|-------|--------|
| Time to Detect | 1 min | <2 min ✓ |
| Time to Failover | 12 min | <10 min ✗ |
| Time to Resolve | 17 min | <15 min ✗ |
| Customer Communication | 5 min | <5 min ✓ |

# Postmortem: API Response Time Degradation

**Incident ID**: INC-20240118-0023
**Severity**: SEV3
**Duration**: 2 hours (slow mitigation)

## Root Cause

N+1 query pattern introduced in the workflow listing endpoint during a 
pagination refactor. Each workflow loaded its execution history in a 
separate query, resulting in hundreds of queries per page load.

## Pattern Recognition

This is the **third N+1 query incident in 6 months**:

1. INC-20231015-0008: User listing endpoint
2. INC-20231201-0019: Organization dashboard
3. INC-20240118-0023: Workflow listing (current)

**Common Pattern**: Code reviews missed performance implications of ORM 
queries during pagination refactors.

## Root Cause (Organizational)

While the technical root cause is the N+1 query, the **organizational root 
cause** is the absence of query performance review in our code review 
checklist and lack of automated detection.

## Action Items

| ID | Type | Description | Owner | Priority |
|----|------|-------------|-------|----------|
| AI-001 | detect | Add query count monitoring per endpoint | @platform | P1 |
| AI-002 | prevent | Add ORM query checklist to PR template | @eng-mgr | P1 |
| AI-003 | prevent | Implement query count lint rule in CI | @platform | P0 |
| AI-004 | prevent | Review all listing endpoints for N+1 | @backend | P2 |
| AI-005 | process | Schedule quarterly ORM training | @eng-mgr | P2 |

## Lessons Learned

1. **Recurring patterns need systemic fixes** - Individual fixes aren't enough
2. **Automated detection > manual review** - Humans miss things under pressure
3. **Query performance isn't obvious** - ORM abstraction hides performance
4. **Training matters** - Team needs ongoing education on performance patterns

## Related Incidents

- INC-20231015-0008: N+1 in user listing (SEV3)
- INC-20231201-0019: N+1 in org dashboard (SEV3)

**Recommendation**: Create runbook for "N+1 Query Detection and Prevention"