Bird
Raised Fist0

Why NoSQL Exists and When SQL is the Wrong Tool

HLDmediumInterview freq: common
Choose your preparation mode4 modes available
ArchitectureTraceChallenge
🏗️
Why NoSQL Exists and When SQL is the Wrong Tool
Understanding the architectural reasons behind NoSQL adoption and SQL limitations at scale
mediumDatabases - NoSQLFreq: common
⚙️
Functional Requirements
  • Support flexible schema for semi-structured or evolving data models
  • Handle high write throughput with low latency (sub-100ms P99)
  • Provide horizontal scalability across commodity servers
  • Offer eventual consistency or tunable consistency models
  • Support large volume of data with efficient partitioning
  • Enable fast key-based lookups and range queries
📊
Non-Functional Requirements
  • Support 2 billion daily active users (DAU)
  • Sustain 925K writes per second peak write QPS
  • Ensure 99.99% availability with multi-region replication
  • Maintain 100ms P99 latency for reads and writes
  • Data retention of 30 days with replication factor 3
❓
Clarifying Questions to Ask
  • What is the expected data model complexity and schema evolution rate?
  • What consistency guarantees are required for reads and writes?
  • What is the expected read-to-write ratio at peak load?
  • Are multi-row or multi-table transactions required?
  • What is the geographic distribution of users and data?
  • What is the acceptable trade-off between latency and consistency?
Out of Scope
✗ Complex multi-table ACID transactions spanning shards
✗ Full SQL join support and complex ad-hoc queries
✗ Strong consistency with synchronous cross-region replication
✗ Analytical OLAP workloads
📌
Assumptions
  • 2 billion DAU
  • 40 writes per user per day
  • 100 bytes average per write message
  • Read QPS is 3x write QPS due to caching and fan-out
⚡
QPS Calculation
write qps2,000,000,000 × 40 / 86,400 ≈ 925,926 writes/sec
peak write qps925,926 × 1.5 (peak factor) ≈ 1,388,889 writes/sec
read qps1,388,889 × 3 ≈ 4,166,667 reads/sec
💾
Storage Estimate
daily storage gb(925,926 writes/sec × 100 bytes) × 86,400 sec / (1024^3) ≈ 7.46 TB/day
retention 30 days7.46 TB × 30 ≈ 224 TB
with replication factor 3224 TB × 3 ≈ 672 TB total storage
🖥️
Server Estimate
storage nodesAssuming 2 TB usable per node → 672 TB / 2 TB ≈ 336 storage nodes
read replicasTo handle 4.16M reads/sec with 10K reads/sec per node → 417 read replicas
🏗️
Architecture Diagram
Architecture Diagram
          +----------------+
          |  Client Apps   |
          +-------+--------+
                  |
          +-------v--------+
          |  API Gateway   |
          +-------+--------+
                  |
          +-------v--------+
          |  Write Path    |
          | (Kafka Cluster)|
          +-------+--------+
                  |
          +-------v--------+
          |  NoSQL Storage |
          | (Cassandra DB) |
          +-------+--------+
                  |
          +-------v--------+
          |  Read Path     |
          | (Redis Cache)  |
          +----------------+
🧩
Components
API Gateway
Entry point for client requests, handles authentication, routing, and throttling
Why thisNginx with Lua scripting for high concurrency and flexible routing
Why notAWS ALB lacks fine-grained routing and custom logic needed at 1.3M QPS
Kafka Cluster
Durable, distributed write log to buffer and order writes before storage
Why thisKafka supports high throughput (millions of writes/sec), partitioning, and consumer groups for scalability
Why notRabbitMQ does not scale linearly beyond 100K msg/sec and lacks partitioning
Cassandra DB
Primary NoSQL storage for high write throughput and horizontal scaling
Why thisCassandra’s masterless architecture supports 1M+ writes/sec with tunable consistency and automatic sharding
Why notPostgreSQL requires manual sharding and B-tree writes bottleneck at 925K writes/sec
Redis Cache
In-memory cache for low-latency reads and hot key acceleration
Why thisRedis TTL natively expires stale data, supports sub-millisecond lookups, and scales with clustering
Why notMemcached lacks persistence and advanced eviction policies needed for consistency
Replication & Multi-Region Sync
Ensures 99.99% availability and disaster recovery with asynchronous replication
Why thisCassandra’s built-in multi-datacenter replication supports eventual consistency and failover
Why notMySQL Group Replication has higher write latency and limited multi-region scalability
🔄
Data Flow
1
Client Apps → API Gateway
Send write/read requests
API Gateway authenticates and routes requests
2
API Gateway → Kafka Cluster
Writes are appended to Kafka topics
Kafka buffers and orders writes for durability and replay
3
Kafka Cluster → Cassandra DB
Kafka consumers batch and write data to Cassandra
Cassandra stores data with tunable consistency and partitions by key
4
Client Apps → API Gateway
Read requests routed to API Gateway
API Gateway forwards reads to Redis cache first
5
API Gateway → Redis Cache
Attempt to serve read from cache
Cache hit returns data immediately
6
Redis Cache → API Gateway
Return cached data if hit
Sub-100ms P99 latency for hot keys
7
API Gateway → Cassandra DB
On cache miss, query Cassandra
Cassandra serves consistent data with partition key lookup
8
Cassandra DB → API Gateway
Return data to client
API Gateway updates Redis cache asynchronously
🗄️
UserActivity
PK: user_id
user_id (UUID)activity_timestamp (timestamp)activity_type (text)metadata (map<text,text>)
Stores user activity logs with flexible schema
🗄️
UserProfile
PK: user_id
user_id (UUID)profile_version (int)profile_data (map<text,text>)
Stores user profile data with evolving schema
🔌
POST
/writeActivity
Request
{
  "user_id": "UUID",
  "activity_type": "string",
  "metadata": "map<string,string>"
}
Response
{
  "status": "success|failure",
  "message": "string"
}
Writes user activity asynchronously via Kafka to Cassandra
🔌
GET
/getUserProfile
Request
{
  "user_id": "UUID",
  "profile_version": "optional int"
}
Response
{
  "profile_data": "map<string,string>",
  "profile_version": "int"
}
Reads user profile from Redis cache or Cassandra on cache miss
🔌
GET
/readActivity
Request
{
  "user_id": "UUID",
  "start_time": "timestamp",
  "end_time": "timestamp"
}
Response
{
  "activities": "list of activity records"
}
Fetches user activities within time range with low latency

Practice

(1/5)
1. A payment gateway must reliably process millions of payment requests per second and ensure durability despite transient failures. Which component is best suited to decouple the payment request ingestion from downstream processing to achieve this?
easy
A. Use a distributed log system like Kafka to persist and stream payment events
B. Use an in-memory cache like Redis to queue payment requests for fast access
C. Use a relational database with auto-increment IDs to store payment requests synchronously
D. Use a REST API gateway that directly calls payment processors synchronously

Solution

  1. Step 1: Identify the role of the decoupling component

    The component must provide durability and handle high throughput without blocking the ingestion path.

  2. Step 2: Evaluate Kafka's suitability

    Kafka provides a distributed, durable log with partitioning and replication, enabling asynchronous processing and replay.

  3. Final Answer:

    Option A -> Option A

  4. Quick Check:

    Kafka decouples ingestion and processing with durability and scale [check]
Hint: Durable logs decouple ingestion from processing [check]
Common Mistakes:
2. A system must handle millions of short video uploads per day, ensuring fast ingestion and metadata extraction without blocking the user. Which component is best suited to decouple upload ingestion from processing?
easy
A. Use a distributed message queue like Kafka to buffer uploads for asynchronous processing
B. Use a relational database to store uploads synchronously during user upload
C. Use a CDN edge cache to store uploads temporarily before processing
D. Use a REST API gateway that directly triggers video transcoding synchronously

Solution

  1. Step 1: Identify ingestion decoupling need

    Relational DB (A) blocks user upload due to synchronous writes; CDN (C) caches for delivery, not ingestion; REST API (D) triggers blocking transcoding.

  2. Step 2: Choose buffering component

    Kafka (B) buffers uploads asynchronously, enabling fast user response and reliable processing.

  3. Final Answer:

    Option A -> Option A

  4. Quick Check:

    Kafka buffers uploads asynchronously for scalable ingestion [check]
Hint: Buffer uploads asynchronously with Kafka queue [OK]
Common Mistakes:
3. For a system launching with unpredictable hot keys, which cache warming strategy balances freshness and resource usage best?
medium
A. Use a hybrid approach combining historical access logs with real-time monitoring to selectively warm keys
B. Preload all possible keys regardless of traffic patterns to ensure zero misses
C. Only warm keys that were hot in the previous launch period, ignoring recent changes
D. Rely solely on lazy loading to avoid unnecessary warming overhead

Solution

  1. Step 1: Analyze warming strategies for unpredictable hot keys

    Hybrid approach uses historical and real-time data to adaptively warm relevant keys.

  2. Step 2: Eliminate extremes

    Preloading all keys wastes resources; ignoring recent changes misses new hot keys; lazy loading causes latency.

  3. Final Answer:

    Option A -> Option A

  4. Quick Check:

    Hybrid warming adapts to traffic changes balancing freshness and cost [check]
Hint: Hybrid warming adapts to traffic changes balancing freshness and cost [check]
Common Mistakes:
4. For ranking posts in a Reddit-like system with millions of votes per minute, which architecture better balances real-time freshness and scalability?
medium
A. Use a streaming pipeline (Kafka + Flink) to incrementally update scores asynchronously
B. Use batch jobs every hour to recompute rankings from raw vote data
C. Use a single centralized database to compute ranking scores on every vote synchronously
D. Use client-side ranking computation to reduce server load

Solution

  1. Step 1: Evaluate synchronous centralized DB approach

    Use a single centralized database to compute ranking scores on every vote synchronously causes write bottlenecks and high latency, unsuitable for millions of votes per minute.

  2. Step 2: Compare streaming vs batch and client-side

    Batch (Use batch jobs every hour to recompute rankings from raw vote data) has high latency, unsuitable for real-time freshness. Client-side (Use client-side ranking computation to reduce server load) can't guarantee consistent global ranking. Streaming pipeline (Use a streaming pipeline (Kafka + Flink) to incrementally update scores asynchronously) provides near real-time incremental updates and scales horizontally.

  3. Final Answer:

    Option A -> Option A

  4. Quick Check:

    Streaming pipelines enable scalable, near real-time ranking updates [check]
Hint: Streaming pipelines scale and update rankings near real-time [check]
Common Mistakes:
5. A team must decide between using a detailed blame-focused report or a blameless timeline with action items for their post-mortem. Which approach is best for fostering continuous improvement in a high-trust engineering culture?
medium
A. Use blameless timelines with actionable steps to encourage learning and improvement
B. Use blame-focused reports to enforce accountability and prevent future errors
C. Use only automated logs without human analysis to avoid bias
D. Skip post-mortems entirely to save time and avoid conflict

Solution

  1. Step 1: Evaluate cultural impact of blame vs blameless approaches

    Blame-focused reports create fear and reduce openness, harming continuous improvement.

  2. Step 2: Assess effectiveness of blameless timelines with action items

    They foster trust, encourage learning, and drive actionable improvements aligned with high-trust culture.

  3. Final Answer:

    Option A -> Option A

  4. Quick Check:

    Blameless + action items -> continuous improvement [check]
Hint: Blameless + action items build trust and improve systems [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