Bird
Raised Fist0

How to Choose a Database - Decision Framework for Interviews

HLDmediumInterview freq: common
Choose your preparation mode4 modes available
ArchitectureTraceChallenge
▶
Request Trace
Step 1/5
API Gateway

Receives client request

API Gateway parses request to determine query type (read/write/analytics)

Protocol:HTTP/REST
BeforeClient request is sent over HTTP to API Gateway
AfterAPI Gateway identifies query type and forwards request to Load Balancer
Load Balancer

Routes request

Load Balancer forwards request to Cache Layer for read queries or OLTP DB for writes

Protocol:Internal RPC
BeforeRequest is at Load Balancer with query type known
AfterRequest is sent to Cache Layer for reads or OLTP DB for writes
Cache Layer (Redis Cluster)

Checks cache for requested data

Cache Layer returns data if present or forwards read request to OLAP DB

Protocol:Redis GET/SET
BeforeCache Layer receives read request
AfterCache hit returns data; cache miss forwards analytical read request to OLAP DB
OLTP DB (PostgreSQL with Citus)

Processes write or real-time read queries

OLTP DB executes SQL queries with distributed sharding via Citus

Protocol:SQL Query
BeforeWrite or real-time read request received
AfterData is durably stored or read results returned to Load Balancer
OLAP DB (Apache Cassandra)

Handles analytical queries on large datasets

OLAP DB executes wide-column queries optimized for analytics

Protocol:CQL
BeforeAnalytical read request forwarded from Cache Layer after cache miss
AfterAggregated results returned to Cache Layer for caching and then to client
🏗️
Architecture Diagram
Architecture Diagram
┌───────────────────┐
│   API Gateway     │
└─────────┬─────────┘
          │ HTTP/REST
          ▼
┌───────────────────┐
│  Load Balancer    │
└─────────┬─────────┘
          │ Internal RPC
          ▼
┌───────────────────────────────┐
│   Cache Layer (Redis Cluster) │
└─────────────┬─────────────────┘
              │ Redis GET/SET
              ▼
      ┌───────────────┐
      │ OLTP DB       │
      │ (PostgreSQL   │
      │  with Citus)  │
      └──────┬────────┘
             │ SQL Query
             ▼
      ┌───────────────┐
      │ OLAP DB       │
      │ (Apache       │
      │  Cassandra)   │
      └───────────────┘
Design Rationale

This architecture illustrates a decision framework for choosing databases based on query type. The API Gateway first classifies requests, routing them through a Load Balancer to backend components. Reads first check the Cache Layer (Redis Cluster) for fast access. Cache misses for analytical queries are forwarded to the OLAP DB (Apache Cassandra), optimized for large-scale analytics. Writes and real-time reads go to the OLTP DB (PostgreSQL with Citus), which supports distributed transactions and sharding. This layered approach balances latency and durability, demonstrating how different databases fit specific workloads in a unified system.

Primary Focus

✓

Practice

(1/5)
1. In a cache system using LFU eviction, at step 4 the eviction process selects a candidate key for removal. What is the state of the candidate's frequency count at this step?
easy
A. The candidate has the highest frequency count among all keys
B. The candidate's frequency count is ignored and eviction is random
C. The candidate's frequency count is reset to zero before eviction
D. The candidate has the lowest frequency count among all keys

Solution

  1. Step 1: Understand LFU eviction criteria

    LFU evicts the cache entry with the lowest access frequency to keep frequently used items.

  2. Step 2: Analyze eviction candidate state at step 4

    The candidate selected for eviction must have the lowest frequency count; frequency is not reset or ignored.

  3. Final Answer:

    Option D -> Option D

  4. Quick Check:

    LFU evicts lowest frequency count keys [check]
Hint: LFU evicts lowest frequency, not highest or random [OK]
Common Mistakes:
2. In a system using asynchronous replication, at step 4 the Replication Worker reads the write-ahead log entry for a committed transaction. What is the state of the replica after this step if the worker crashes before applying the entry?
easy
A. Replica has applied the transaction but not acknowledged it
B. Replica has not applied the transaction and will retry on restart
C. Replica has partially applied the transaction causing inconsistency
D. Replica has acknowledged the transaction despite crash

Solution

  1. Step 1: Trace Replication Worker reading WAL entry

    Worker reads the log but crashes before applying, so replica state unchanged.

  2. Step 2: Understand retry behavior on restart

    Since transaction not applied, worker retries applying on restart ensuring consistency.

  3. Final Answer:

    Option B -> Option B

  4. Quick Check:

    Unapplied transaction retried after crash [check]
Hint: Unapplied logs retried after crash, no partial state [check]
Common Mistakes:
3. In the failover incident trace, at Step 4 the Database Proxy receives a client query after the primary node crashed. What state does the proxy enter before forwarding the query?
easy
A. Marks the primary as unavailable and routes query to the new leader after election
B. Returns an error to the client without retrying
C. Queues the query indefinitely until the old primary recovers
D. Immediately forwards query to the old primary node, causing timeout

Solution

  1. Step 1: Trace Database Proxy behavior at Step 4

    Upon detecting primary failure, the proxy marks it unavailable and waits for leader election to complete before routing queries to the new leader.

  2. Step 2: Eliminate other options

    Forwarding immediately to old primary (A) causes timeouts; queuing indefinitely (C) stalls clients; returning errors without retry (B) reduces availability.

  3. Final Answer:

    Option A -> Option A

  4. Quick Check:

    Proxy reroutes queries only after leader election [check]
Hint: Proxy reroutes only after leader election completes
Common Mistakes:
4. For a key-value store that must handle concurrent writes with minimal coordination, which approach is better to resolve conflicts and why?
medium
A. Use vector clocks to detect and merge conflicting versions asynchronously
B. Use synchronous quorum writes to enforce a single authoritative version
C. Use a centralized lock manager to serialize all writes
D. Use consistent hashing to route conflicting writes to the same node

Solution

  1. Step 1: Analyze conflict resolution approaches

    Vector clocks allow detection of concurrent conflicting versions without blocking writes, enabling asynchronous conflict resolution.

  2. Step 2: Eliminate other options

    Centralized locking serializes writes, causing high latency and bottlenecks. Synchronous quorum writes enforce consistency but increase latency and reduce availability. Consistent hashing routes keys but does not resolve conflicts.

  3. Final Answer:

    Option A -> Option A

  4. Quick Check:

    Vector clocks enable asynchronous conflict detection and merging [check]
Hint: Vector clocks detect conflicts without blocking writes [OK]
Common Mistakes:
5. At 10x scale, which component in the RTMP to HLS pipeline is most likely to fail first due to stateful connection limits, and why?
hard
A. The CDN edge nodes, because cache invalidation overhead grows exponentially
B. The transcoder cluster, because CPU-intensive video encoding can't be parallelized further
C. The Kafka cluster, because partition count cannot increase beyond a fixed limit
D. The RTMP ingest servers, because each maintains a stateful TCP connection per broadcaster limiting horizontal scaling

Solution

  1. Step 1: Identify stateful bottlenecks

    RTMP ingest servers maintain stateful TCP connections per broadcaster, limiting how many connections each server can handle.

  2. Step 2: Compare scaling constraints

    Transcoders (A) can be scaled horizontally with more instances. Kafka (C) can add partitions but has practical limits. CDN edges (D) scale well with caching. RTMP ingest (B) hits connection limits first due to TCP statefulness.

  3. Final Answer:

    Option D -> Option D

  4. Quick Check:

    Stateful TCP connections on RTMP ingest servers limit horizontal scaling [check]
Hint: Stateful TCP connections limit RTMP ingest server 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