Bird
Raised Fist0

How to Choose a Database - Decision Framework for Interviews

HLDmediumInterview freq: common
Choose your preparation mode4 modes available
ArchitectureTraceChallenge
🏗️
How to Choose a Database - Decision Framework for Interviews
A structured framework to select the right database technology based on workload and scale
mediumDatabases - SQL and TransactionsFreq: common
⚙️
Functional Requirements
  • Support decision-making for selecting SQL vs NoSQL databases based on workload characteristics
  • Evaluate consistency, availability, and partition tolerance trade-offs
  • Incorporate transaction support and query complexity considerations
  • Provide guidelines for scaling writes and reads at high QPS
  • Include criteria for data model suitability (relational, key-value, document, graph)
  • Recommend database technologies with justification
📊
Non-Functional Requirements
  • Support workloads up to 2 billion daily active users (DAU)
  • Handle peak write throughput of up to 1 million writes per second
  • Ensure 99.99% availability SLA for critical OLTP systems
  • Target 100ms P99 latency for read and write operations
  • Support data retention policies up to 1 year with efficient storage
❓
Clarifying Questions to Ask
  • What is the expected read-to-write ratio for the workload?
  • Is strong consistency or eventual consistency required?
  • What types of queries and transactions need to be supported?
  • What is the expected data size and retention period?
  • Are there multi-region or geo-distribution requirements?
  • What is the tolerance for downtime or data loss?
Out of Scope
✗ Designing a specific database system from scratch
✗ Detailed schema design for a particular application
✗ Network infrastructure or physical data center design
✗ Security and compliance beyond basic data consistency guarantees
📌
Assumptions
  • 2 billion DAU generating 40 messages per user per day
  • Average message size is 100 bytes
  • Peak write traffic is 10x average write QPS
  • Read traffic is 5x write traffic due to caching and analytics
⚡
QPS Calculation
write qps2B × 40 / 86400 ≈ 925,926 writes/sec
peak write qps925,926 × 10 = 9,259,260 writes/sec
read qps925,926 × 5 = 4,629,630 reads/sec
💾
Storage Estimate
daily storage gb(2B × 40 × 100) / (1024^3) ≈ 7,450 GB/day
retention days365
total storage gb7,450 × 365 ≈ 2,719,250 GB (~2.7 PB)
with replication 3x2.7 PB × 3 = 8.1 PB
🖥️
Server Estimate
write db nodesAssuming 100k writes/sec per node (Cassandra), need ~93 nodes
read cache nodesAssuming Redis handles 50k reads/sec per node, need ~93 nodes
🏗️
Architecture Diagram
Architecture Diagram
          +----------------+
          | Client / App   |
          +-------+--------+
                  |
          +-------v--------+
          | API Gateway    |
          +-------+--------+
                  |
          +-------v--------+
          | Load Balancer  |
          +-------+--------+
                  |
    +-------------+--------------+
    |                            |
+---v----+                  +----v---+
| OLTP   |                  | OLAP   |
| DB     |                  | DB     |
+---+----+                  +----+---+
    |                            |
    |                            |
    |                            |
    +------------+---------------+
                 |
          +------v-------+
          | Cache Layer  |
          +--------------+
🧩
Components
API Gateway
Entry point for client requests, routes to appropriate DB or cache
Why thisNginx with Lua scripting enables flexible routing and rate limiting at scale
Why notAWS ALB lacks fine-grained routing and custom logic needed for DB selection
Load Balancer
Distributes requests evenly across DB nodes to prevent hotspots
Why thisHAProxy supports TCP and HTTP load balancing with health checks and stickiness
Why notDNS round robin does not provide real-time health checks or load awareness
OLTP DB (PostgreSQL with Citus)
Handles transactional workloads requiring strong consistency and complex queries
Why thisPostgreSQL with Citus extension supports distributed SQL with ACID transactions and joins
Why notMySQL sharding requires manual partitioning and lacks horizontal scale at 9M writes/sec
OLAP DB (Apache Cassandra)
Stores large volumes of time-series or wide-column data with high write throughput
Why thisCassandra provides linear write scalability and eventual consistency for massive data ingestion
Why notMongoDB's single primary write bottleneck limits write scalability beyond 1M writes/sec
Cache Layer (Redis Cluster)
Accelerates read queries and reduces load on primary databases
Why thisRedis Cluster supports sharding and TTL expiration with sub-millisecond latency
Why notMemcached lacks persistence and advanced data structures needed for complex caching
🔄
Data Flow
1
Client / App → API Gateway
Send request with workload metadata
API Gateway inspects request type and consistency requirements
2
API Gateway → Load Balancer
Route request to OLTP or OLAP DB cluster
Based on query complexity and transaction needs
3
Load Balancer → OLTP DB or OLAP DB
Forward request to appropriate DB node
Load balancer ensures even distribution and health checks
4
OLTP/OLAP DB → Cache Layer
Update cache with fresh data on writes
Cache invalidation or update to maintain consistency
5
Client / App → Cache Layer
Read requests first check cache
Cache hit reduces DB load and latency

Practice

(1/5)
1. In the SLO error budget tracking system, at step 4 the Error Budget Calculator receives aggregated error counts and total request counts. What state does it maintain immediately after processing these inputs?
easy
A. It resets the error budget to full capacity regardless of recent errors
B. It discards error counts if the total request count is below a threshold
C. It queues the error counts for asynchronous processing by the alerting system
D. It updates the current error budget remaining by subtracting recent error rate from total budget

Solution

  1. Step 1: Understand the role of the Error Budget Calculator at step 4

    It updates the remaining error budget by subtracting the recent error rate from the total allowed errors, reflecting current SLO compliance.

  2. Step 2: Exclude other states that contradict error budget tracking logic

    Resetting the budget ignores recent errors; queuing is done later by alerting; discarding counts would lose accuracy.

  3. Final Answer:

    Option D -> Option D

  4. Quick Check:

    Error Budget Calculator updates remaining budget after aggregation [check]
Hint: Error budget updates subtract recent errors, not reset or discard [OK]
Common Mistakes:
2. Without the Outbox Pattern in an event-driven saga, what exact failure occurs when a service crashes after committing its local transaction but before publishing the event?
medium
A. The saga coordinator retries the event automatically ensuring eventual delivery
B. The local transaction is rolled back automatically, so no inconsistency occurs
C. The event is published twice causing duplicate side effects downstream
D. The event is lost, causing downstream services to never receive the update and saga to stall

Solution

  1. Step 1: Trace failure without Outbox Pattern

    Service commits DB transaction but crashes before event publish, so event never reaches message broker.

  2. Step 2: Consequence of lost event

    Downstream services never receive update; saga waits indefinitely or inconsistently proceeds.

  3. Final Answer:

    Option D -> Option D

  4. Quick Check:

    Crash after commit but before publish -> event lost, saga stalls [check]
Hint: Crash after DB commit but before event publish loses event [check]
Common Mistakes:
3. If the Kafka cluster is unavailable, what exactly breaks in the log aggregation pipeline?
medium
A. Logs are lost immediately as producers drop messages without retry
B. Log processors continue processing old logs but cannot enrich new logs
C. The storage layer stops accepting writes but ingestion continues
D. Log producers block on send calls, causing backpressure and delayed ingestion

Solution

  1. Step 1: Identify Kafka's role

    Kafka buffers logs between producers and processors, enabling decoupling.

  2. Step 2: Trace failure impact

    If Kafka is down, producers may retry or block, but processors cannot consume new logs, halting enrichment.

  3. Final Answer:

    Option B -> Option B

  4. Quick Check:

    Kafka down -> processors stall on new logs, old logs processed [check]
Hint: Kafka outage stalls processors, not immediate producer loss [check]
Common Mistakes:
4. You must choose between a monolithic architecture and microservices for a system with rapidly evolving features and a small team. Which choice best justifies your design decision to the interviewer?
medium
A. Choose microservices because they always scale better regardless of team size
B. Choose monolith to reduce operational complexity and speed up feature delivery for a small team
C. Choose microservices to isolate failures even if it increases deployment overhead
D. Choose monolith because it requires no testing or CI/CD pipelines

Solution

  1. Step 1: Analyze team size and feature velocity constraints

    Small teams benefit from simpler architectures to reduce overhead and speed delivery.

  2. Step 2: Evaluate operational complexity of microservices

    Microservices add deployment and operational complexity, which can slow small teams.

  3. Final Answer:

    Option B -> Option B

  4. Quick Check:

    Monolith reduces complexity, speeds delivery for small teams [check]
Hint: Small teams prefer monoliths for simplicity and speed [check]
Common Mistakes:
5. At 10x scale, which component in the AI Code Assistant system is most likely to fail first due to stateful connection limits, and why?
hard
A. The API gateway, because it cannot handle the increased request routing throughput
B. The code generator service, because GPU resources saturate under concurrent requests
C. The WebSocket servers maintaining user sessions, because stateful connections cannot be sharded easily
D. The vector search engine, because its index size grows linearly and slows queries

Solution

  1. Step 1: Analyze vector search scaling

    Vector search scales with index size but can shard horizontally; query latency grows but not immediate failure (A incorrect).

  2. Step 2: Assess code generator scaling

    GPU saturation is a bottleneck but can be mitigated by batching and autoscaling (B less immediate).

  3. Step 3: Evaluate API gateway

    API gateways are stateless and horizontally scalable (C incorrect).

  4. Step 4: Identify WebSocket server bottleneck

    WebSocket servers hold stateful connections bound to server memory; sharding existing connections is complex, causing first failure at 10x scale (D correct).

  5. Final Answer:

    Option C -> Option C

  6. Quick Check:

    Stateful WebSocket connections limit server scaling [check]
Hint: Stateful WebSocket servers bottleneck scaling first [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