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
45 min · senior
senior

You are designing a backend data storage system for a 50 million daily active user (DAU) social media startup. The system must handle high write throughput, flexible schema evolution, and provide low-latency reads with P99 latency under 200ms. Data encryption at rest and in transit is mandatory. The system must support offline data delivery and eventual consistency for some use cases. Traditional relational databases have started to show performance bottlenecks and schema rigidity issues.

Constraints
  • Support 50M DAU with peak write throughput of 1M writes/sec
  • P99 read latency under 200ms for user timeline queries
  • Data encryption at rest and in transit is mandatory
  • Schema must evolve frequently without downtime
  • Support offline data delivery with eventual consistency guarantees
📋
Guided Steps
1/5
Step 1
Identify the primary scalability bottleneck with SQL at this scale
What are the key limitations of traditional SQL databases when handling 50M DAU with 1M writes/sec and frequent schema changes?
💡 Hint
Consider write throughput, schema rigidity, and distributed transaction overhead.
Key Decision
Use Apache Cassandra for write-heavy workloads due to its linear write scalability with LSM-tree storage and flexible schema evolution.
Interviewer Expectation
Mid-level: Names Cassandra for scalability. Senior: Explains how Cassandra's partition key design enables linear write scaling and why relational ACID transactions fail at this scale.
⚠️Common Wrong Answer
Using a single MySQL cluster with sharding - leads to complex cross-shard transactions and schema migrations causing downtime.
Step 2
Choose the data model to support flexible schema and offline delivery
Which data model best supports evolving schemas and offline data synchronization with eventual consistency?
💡 Hint
Think about document vs relational models and how they handle schema changes and partial updates.
Key Decision
Adopt a document-oriented NoSQL model (e.g., MongoDB or Cassandra's wide-column model) to allow schema flexibility and partial updates without downtime.
Interviewer Expectation
Mid-level: Mentions document store for flexibility. Senior: Details how document models avoid costly migrations and enable offline sync with versioned updates.
⚠️Common Wrong Answer
Rigid relational schema - causes frequent costly migrations and breaks offline sync due to strict schema enforcement.
Step 3
Design for low-latency reads under 200ms P99
How do you ensure P99 read latency under 200ms for user timeline queries at 50M DAU?
💡 Hint
Consider caching layers and data partitioning strategies that reduce read amplification.
Key Decision
Use Redis as a caching layer with TTL-based invalidation combined with Cassandra for storage; partition data by user_id to localize reads and reduce latency.
Interviewer Expectation
Mid-level: Suggests caching with Redis. Senior: Explains Redis TTL invalidation strategy and why user_id partitioning reduces cross-node reads and hotspots.
⚠️Common Wrong Answer
Relying solely on Cassandra reads - leads to high read latency due to disk I/O and compaction stalls.
Step 4
Ensure data encryption and compliance
How do you implement encryption at rest and in transit without impacting performance significantly?
💡 Hint
Look for native encryption support in storage and transport layers and hardware acceleration options.
Key Decision
Enable TLS 1.3 for all client-server and inter-node communication and use Cassandra's Transparent Data Encryption (TDE) with hardware security modules (HSM) for key management.
Interviewer Expectation
Mid-level: Mentions TLS and encryption. Senior: Details TLS 1.3 benefits, Cassandra TDE implementation, and HSM integration for secure key lifecycle.
⚠️Common Wrong Answer
Encrypting data at application layer only - causes performance bottlenecks and complicates key management.
Step 5
Handle offline data delivery and eventual consistency
How do you design the system to support offline clients syncing data with eventual consistency guarantees?
💡 Hint
Consider conflict resolution strategies and data versioning mechanisms.
Key Decision
Implement vector clocks or timestamps for versioning in Cassandra, combined with client-side conflict resolution and background reconciliation workers.
Interviewer Expectation
Mid-level: Mentions eventual consistency and conflict resolution. Senior: Explains vector clocks usage, how Cassandra's lightweight transactions are avoided, and background reconciliation workflows.
⚠️Common Wrong Answer
Using strong consistency with synchronous replication - causes high write latency and offline sync failures.
🔌
API Design Challenge
Sketch 3 core API endpoints that illustrate how clients interact with the data storage system, focusing on write, read, and sync operations.
Hints
  • How does the client submit new data or updates?
  • How does the client retrieve data efficiently under low latency constraints?
  • How does the client synchronize offline changes or receive updates?
📊
Scoring Rubric
▼ Show
Must Have - 60%
  • Identifies Cassandra for write scalability and schema flexibility
  • Chooses document-oriented data model for schema evolution
  • Includes Redis caching for low-latency reads
  • Implements encryption at rest and in transit with TLS and TDE
Good to Have - 30%
  • Explains vector clocks or versioning for offline sync
  • Describes background reconciliation for eventual consistency
  • Details partition key choice to avoid hotspots
  • Mentions hardware security modules for key management
Bonus - 10%
  • Discusses trade-offs of strong vs eventual consistency
  • Explains why relational databases fail at this scale
  • Provides detailed API design endpoints
Anti-patterns to Avoid
Using monolithic SQL databases causing write bottlenecks and downtime during schema migrations, which leads to system unavailability and poor user experience
Relying on HTTP polling for offline sync leading to massive reconnect storms at 50M DAU, causing system overload and degraded performance
Encrypting data only at application layer causing performance degradation and complex key management, resulting in slower response times and security risks
Ignoring partition key design causing hotspots and uneven load distribution, which creates bottlenecks and reduces system scalability
Using synchronous cross-shard transactions causing high latency and poor availability, negatively impacting write throughput and user experience
Not implementing conflict resolution leading to data loss or corruption during offline sync, compromising data integrity and user trust
✅
Model Answer
▼ Reveal

At 50M DAU with 1M writes/sec and strict latency and encryption requirements, traditional SQL databases fail due to rigid schemas and transaction overhead. Using Apache Cassandra with partition_key=user_id enables linear write scalability and efficient range queries. A document-oriented data model supports flexible schema evolution and partial updates without downtime. Redis caching with TTL-based invalidation ensures P99 read latency under 200ms. TLS 1.3 secures all network communication with minimal latency overhead, while Cassandra's Transparent Data Encryption (TDE) combined with hardware security modules (HSM) protects data at rest. Vector clocks enable versioning for offline data delivery and eventual consistency, supported by background reconciliation workers to merge offline changes asynchronously. Avoiding relational ACID transactions prevents bottlenecks at this scale.

Key Decisions
  • Choosing Apache Cassandra for its linear write scalability and flexible schema with LSM-tree storage
  • Adopting a document-oriented data model to allow schema evolution without downtime
  • Implementing Redis caching to meet low-latency read requirements
  • Enabling TLS 1.3 and Cassandra TDE with HSM for secure encryption at rest and in transit
  • Using vector clocks for versioning to support offline data synchronization with eventual consistency
  • Avoiding relational ACID transactions to prevent write bottlenecks at high throughput
  • Apache Cassandra with partition_key=user_id for linear write scalability and efficient range queries
  • Document-oriented data model to support flexible schema evolution and partial updates
  • Redis caching layer with TTL-based invalidation to achieve P99 read latency under 200ms
  • TLS 1.3 for all network communication ensuring encryption in transit with minimal latency overhead
  • Cassandra Transparent Data Encryption (TDE) with HSM for secure encryption at rest and key management
  • Vector clocks for versioning enabling offline data delivery and eventual consistency with conflict resolution
  • Background reconciliation workers to merge offline changes asynchronously without blocking writes
  • Avoidance of relational ACID transactions to prevent bottlenecks at 1M writes/sec scale

Practice

(1/5)
1. A microservice calls a downstream service that occasionally fails or responds slowly. To prevent cascading failures and allow quick recovery, which component should be introduced?
easy
A. Implement a circuit breaker to stop calls after failure threshold is reached
B. Use a retry mechanism with exponential backoff on every request
C. Add a load balancer to distribute requests evenly across instances
D. Use a message queue to buffer all requests asynchronously

Solution

  1. Step 1: Identify failure containment mechanism

    Retries alone (A) can worsen cascading failures by increasing load; load balancers (C) distribute load but don't prevent failures; message queues (D) buffer but don't stop calls to failing services.

  2. Step 2: Recognize circuit breaker role

    Circuit breakers (B) detect failure thresholds and open to stop calls, preventing cascading failures and allowing recovery.

  3. Final Answer:

    Option A -> Option A

  4. Quick Check:

    Circuit breaker stops calls after failures to prevent cascade [check]
Hint: Circuit breaker stops calls after failures to prevent cascade [OK]
Common Mistakes:
2. In the request flow of a global load balancing system, at Step 3 the GSLB receives a DNS query for a user in Europe. What does the GSLB do at this step?
easy
A. Returns the IP of the closest data center without checking health status
B. Checks data center health and latency metrics before returning the IP address
C. Forwards the DNS query to the local data center's DNS server for resolution
D. Caches the previous DNS response indefinitely to reduce load

Solution

  1. Step 1: Understand GSLB behavior at DNS query

    GSLB does not blindly return IPs (Returns the IP of the closest data center without checking health status) or forward queries (Forwards the DNS query to the local data center's DNS server for resolution). It actively checks health and latency to pick the best endpoint.

  2. Step 2: Evaluate caching behavior

    Indefinite caching (Caches the previous DNS response indefinitely to reduce load) would prevent dynamic failover and load balancing.

  3. Final Answer:

    Option B -> Option B

  4. Quick Check:

    GSLB checks health and latency before DNS response [check]
Hint: GSLB checks health before DNS response [check]
Common Mistakes:
3. For a distributed cache with frequent node joins and leaves, which approach best balances minimal key remapping and low client complexity?
medium
A. Use a centralized directory service to map keys to nodes
B. Use consistent hashing with virtual nodes and client-side slot caching
C. Use simple modulo hashing with client retries on failures
D. Use random key assignment with periodic full cache rebuilds

Solution

  1. Step 1: Evaluate centralized directory approach

    Centralized directory (Use a centralized directory service to map keys to nodes) adds single point of failure and latency, increasing client complexity.

  2. Step 2: Compare consistent hashing with virtual nodes

    Consistent hashing with virtual nodes and client-side caching (Use consistent hashing with virtual nodes and client-side slot caching) minimizes remapping and keeps client complexity manageable.

  3. Final Answer:

    Option B -> Option B

  4. Quick Check:

    Consistent hashing + virtual nodes + client caching -> minimal remap, manageable complexity [check]
Hint: Consistent hashing + virtual nodes + client caching balances remap and complexity [OK]
Common Mistakes:
4. Without the retry queue component in the payment gateway's webhook delivery system, what exact failure occurs when a merchant's endpoint is temporarily down?
medium
A. Webhook notifications are retried immediately in a tight loop, overwhelming the merchant server
B. Webhook delivery blocks the main payment processing thread until the merchant recovers
C. Webhook notifications are queued in memory indefinitely, causing memory leaks and crashes
D. Webhook notifications are lost permanently with no retry, causing inconsistent merchant state

Solution

  1. Step 1: Understand retry queue role

    The retry queue buffers failed webhook notifications for asynchronous retries, ensuring eventual delivery.

  2. Step 2: Consequence of missing retry queue

    Without it, failed webhooks are dropped or lost, causing permanent notification loss and inconsistent merchant state.

  3. Final Answer:

    Option D -> Option D

  4. Quick Check:

    Missing retry queue -> permanent webhook loss on failure [check]
Hint: Retry queue prevents permanent webhook loss [check]
Common Mistakes:
5. At 10x scale, which component in a Zookeeper-based leader election and configuration system is most likely to become the bottleneck first, and why?
hard
A. Client applications, because they overwhelm the network with configuration fetches
B. Follower nodes, because they must process all leader election events concurrently
C. Persistent storage backend, because it cannot keep up with ephemeral node writes
D. Zookeeper ensemble leader node, because it handles all write requests and session management serially

Solution

  1. Step 1: Identify Zookeeper ensemble roles

    Leader node serializes all writes and manages session heartbeats.

  2. Step 2: Analyze scaling impact

    At 10x scale, leader node CPU and network become bottleneck due to serialized writes and session management.

  3. Step 3: Evaluate other components

    Followers handle reads and watches but are less bottlenecked; storage backend is optimized for writes.

  4. Final Answer:

    Option D -> Option D

  5. Quick Check:

    Zookeeper leader serializes writes -> first bottleneck at scale [check]
Hint: Leader node serializes writes -> first bottleneck at scale [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