Bird
Raised Fist0

GitHub Database Failover Incident - What Went Wrong and Prevention

HLDhardInterview freq: medium
Choose your preparation mode4 modes available
🎯
GitHub Database Failover Incident - What Went Wrong and Prevention
45 min · senior
senior

GitHub experienced a major database failover incident impacting millions of developers worldwide. The system must handle 50M daily active users with a P99 latency under 200ms for critical operations. Data encryption at rest and in transit is mandatory. The failover mechanism must ensure zero data loss and minimal downtime (<30s). The system must support multi-region active-active writes with strong consistency guarantees.

  • P99 latency under 200ms for user-facing database operations
  • End-to-end encryption (TLS + at-rest encryption) mandatory
  • Failover downtime must be under 30 seconds with zero data loss
  • Support multi-region active-active writes with strong consistency
  • Incident detection and failover automation must be fully auditable
📋
Guided Steps
1/5
Step 1
Identify Root Causes of the GitHub Database Failover Incident
What architectural or operational failures led to the failover incident? Consider scale, replication, and monitoring.
💡 Hint
Focus on replication lag, failover detection delays, and single points of failure in the database cluster.
Use PostgreSQL with Patroni for HA and synchronous multi-region replication to minimize failover lag and avoid split-brain.
Mid: Names PostgreSQL replication and mentions replication lag issues. Senior: Explains synchronous replication trade-offs and Patroni's leader election to prevent split-brain scenarios.
Step 2
Design a Failover Detection and Automation Mechanism
How would you detect database failures quickly and automate failover while ensuring zero data loss?
💡 Hint
Consider heartbeat monitoring, consensus-based leader election, and automated failover orchestration tools.
Implement Patroni with Etcd for distributed consensus and health checks, triggering automated failover within 10 seconds.
Mid: Mentions heartbeat checks and basic failover triggers. Senior: Details Etcd consensus, fencing mechanisms, and automated failover orchestration with minimal downtime.
Step 3
Ensure Strong Consistency and Zero Data Loss Across Multi-Region Writes
How do you architect multi-region active-active writes with strong consistency and encryption?
💡 Hint
Look into distributed consensus protocols, conflict resolution, and encryption at rest and in transit.
Use CockroachDB with Raft consensus for distributed ACID transactions and TLS + AES-256 encryption for data security.
Mid: Suggests multi-region replication and encryption basics. Senior: Explains Raft consensus guarantees, transaction latencies, and encryption compliance in detail.
Step 4
Design Client APIs for Failover Awareness and Retry Logic
What API endpoints enable clients to detect failover status and retry requests safely?
💡 Hint
Consider health check endpoints, error reporting, and idempotent retry mechanisms.
Expose /failover/status (GET), /failover/report (POST), and /failover/retry (POST) endpoints with JWT authentication and idempotency keys.
Mid: Names health check endpoint and basic retry. Senior: Details secure reporting, idempotent retries, JWT authentication, and client-side exponential backoff.
Step 5
Implement Monitoring, Auditing, and Alerting for Failover Events
How do you monitor failover events and ensure full auditability for compliance and postmortem analysis?
💡 Hint
Think about centralized logging, immutable audit trails, and real-time alerting tools.
Use Prometheus for metrics, ELK stack for immutable logs, and PagerDuty for alerting with audit logs stored in AWS S3 with encryption.
Mid: Mentions monitoring dashboards and alerting basics. Senior: Explains immutable audit trails, encrypted log storage, automated alert escalation, and compliance requirements.
🔌
API Design Challenge
Sketch 3 core API endpoints to support failover detection, status reporting, and client retry logic during database failover.
  • How does the client report database errors or latency spikes?
  • How does the system expose failover status and health to clients?
  • How do clients retry or redirect requests during failover?
📊
Scoring Rubric
▼ Show
Must Have - 60%
  • Automated failover with distributed consensus (Patroni + Etcd)
  • Synchronous replication to prevent data loss
  • Failover detection within 10 seconds
  • Encrypted data in transit and at rest
  • Client APIs for failover status and retry
Good to Have - 30%
  • Multi-region active-active writes with strong consistency (CockroachDB)
  • Immutable audit logs with encryption
  • Real-time monitoring and alerting integration
Bonus - 10%
  • Idempotent client retry mechanisms
  • Exponential backoff for client retries
  • Detailed postmortem analysis tooling
Asynchronous replication only - causes data loss during failover due to replication lag and stale replicas
Manual failover processes - increase downtime and human error, delaying recovery
No failover status API - clients cannot adapt, causing cascading failures and poor user experience
Ephemeral or unencrypted logs - lose critical forensic data after failover and violate compliance requirements
Ignoring encryption requirements - exposes sensitive data to breaches and regulatory penalties
✅
Model Answer
▼ Reveal

The incident was caused by asynchronous replication lag and manual failover delays. The solution uses Patroni with Etcd for automated failover, synchronous multi-region PostgreSQL replication for zero data loss, and CockroachDB for active-active writes with strong consistency. Failover APIs enable clients to detect and retry safely, while Prometheus and ELK provide monitoring and immutable audit logs.

  • Patroni with Etcd ensures automated failover orchestration with distributed consensus to avoid split-brain
  • Synchronous multi-region PostgreSQL replication guarantees zero data loss during failover
  • Failover detection and automated switchover occur within 10 seconds to minimize downtime
  • CockroachDB with Raft consensus enables multi-region active-active writes with strong consistency
  • TLS 1.3 and AES-256 encryption protect data in transit and at rest to meet security requirements
  • API endpoints /failover/status, /failover/report, and /failover/retry are secured with JWT and idempotency keys to support safe client retries
  • Prometheus and ELK stack provide real-time monitoring and immutable, encrypted audit logs for compliance
  • PagerDuty integration automates alerting and escalation for failover events
  • Audit logs are stored in AWS S3 with server-side encryption and versioning for long-term retention
  • Patroni with Etcd for distributed leader election and automated failover orchestration
  • PostgreSQL with synchronous multi-region replication to guarantee zero data loss
  • CockroachDB for multi-region active-active writes with Raft consensus ensuring strong consistency
  • TLS 1.3 for in-transit encryption and AES-256 for at-rest encryption of all database data
  • API endpoints /failover/status, /failover/report, and /failover/retry secured with JWT and idempotency keys
  • Prometheus for real-time failover metrics and ELK stack for immutable, encrypted audit logs
  • PagerDuty integration for automated alerting and escalation on failover events
  • AWS S3 with server-side encryption and versioning for long-term audit log retention

Practice

(1/5)
1. A Pastebin-like service must store millions of short text snippets with automatic expiration after a configurable TTL. Which storage component best handles this requirement efficiently?
easy
A. Use a relational database with periodic batch jobs to delete expired snippets
B. Use a distributed file system to store snippets as files and run a daily cleanup script
C. Use a NoSQL document store without TTL and rely on application logic to filter expired snippets
D. Use a key-value store like Redis with built-in TTL support for automatic expiration

Solution

  1. Step 1: Identify storage with native TTL support

    Redis supports automatic expiration of keys with TTL, avoiding manual cleanup overhead.
  2. Step 2: Evaluate alternatives

    Relational DB with batch jobs (A) adds latency and complexity; file systems (C) lack efficient expiration; NoSQL without TTL (D) requires expensive filtering.
  3. Final Answer:

    Option D -> Option D
  4. Quick Check:

    Redis TTL automates expiration, reducing operational complexity [check]
Hint: Use storage with native TTL for automatic expiration [check]
Common Mistakes:
2. A microservices architecture requires low-latency, strongly typed RPC calls between internal services with automatic code generation. Which technology best fits this requirement?
easy
A. SOAP with XML for strict contract enforcement
B. REST over HTTP with JSON payloads for flexibility
C. GraphQL for flexible querying of service data
D. gRPC with Protocol Buffers for efficient binary serialization

Solution

  1. Step 1: Identify serialization and transport needs

    Low-latency and strongly typed RPC require efficient binary serialization and HTTP/2 transport.
  2. Step 2: Evaluate technology fit

    gRPC uses Protocol Buffers for compact binary serialization and HTTP/2 for multiplexed streams, meeting requirements. REST with JSON is flexible but slower and less type-safe. GraphQL is query-focused, not RPC. SOAP is verbose and outdated.
  3. Final Answer:

    Option D -> Option D
  4. Quick Check:

    gRPC + Protobuf -> low-latency, strongly typed RPC [check]
Hint: Binary + HTTP/2 + codegen = gRPC [check]
Common Mistakes:
3. A production system needs to continuously measure its reliability against defined error budgets and alert when the budget is close to exhaustion. Which component is primarily responsible for aggregating error rates and calculating remaining error budget in real time?
easy
A. A distributed log like Kafka that stores raw event data for offline batch processing
B. A metrics aggregation service that computes SLO compliance and error budget burn rates
C. A tracing system that collects detailed request traces for root cause analysis
D. A dashboard visualization tool that displays historical uptime percentages

Solution

  1. Step 1: Identify the component responsible for real-time error budget calculation

    The metrics aggregation service continuously processes incoming metrics to compute SLO compliance and error budget burn rates, enabling timely alerts.
  2. Step 2: Eliminate other options based on their roles

    Kafka stores raw data but does not compute error budgets in real time; tracing systems focus on request-level diagnostics, not aggregate error rates; dashboards visualize data but do not perform calculations.
  3. Final Answer:

    Option B -> Option B
  4. Quick Check:

    Metrics aggregation service -> real-time error budget tracking [check]
Hint: Metrics aggregation calculates error budgets in real time [OK]
Common Mistakes:
4. A high-throughput web service must handle millions of requests per second with minimal latency and no session affinity. Which architectural component best supports this requirement?
easy
A. Stateful application servers maintaining user sessions in memory
B. Stateless application servers behind a load balancer with external session storage
C. Sticky sessions on load balancer directing requests to the same server
D. Monolithic server with local session cache for each user

Solution

  1. Step 1: Identify stateless server benefits

    Stateless servers allow horizontal scaling without session affinity, reducing latency and complexity.
  2. Step 2: Evaluate session management

    External session storage decouples session state from servers, enabling load balancer to distribute requests evenly.
  3. Final Answer:

    Option B -> Option B
  4. Quick Check:

    Stateless servers + external session store -> scalable, low-latency [check]
Hint: Stateless servers + external session store scale best [check]
Common Mistakes:
5. For storing user and item embeddings in a recommendation system, which storage choice best balances low-latency reads and frequent updates at scale?
medium
A. Use a document store like MongoDB with embedding documents
B. Use a distributed key-value store like Cassandra with tunable consistency
C. Use a file system with periodic batch loading into memory
D. Use a relational database with normalized tables for embeddings

Solution

  1. Step 1: Identify storage requirements

    Embedding storage requires low-latency reads and frequent writes at scale with high availability.
  2. Step 2: Evaluate storage options

    Relational DB (A) struggles with write throughput and scaling; Cassandra (B) offers tunable consistency but may add complexity; file system batch loading (D) introduces stale data and high latency. Document store (C) supports flexible schema and fast reads with embedded documents, balancing latency and update frequency.
  3. Final Answer:

    Option A -> Option A
  4. Quick Check:

    Document store balances flexible schema and read/write latency [check]
Hint: Document store balances schema flexibility and latency [check]
Common Mistakes:
Recommended

Ready to prove it?

Pass the test and this pattern moves unknown strong while earning +1 Grip toward your certificate.

10 questions~5 mineasy hard