Bird
Raised Fist0

Read Replicas and CQRS Pattern for Read-Heavy Systems

HLDmediumInterview freq: common
Choose your preparation mode4 modes available
ArchitectureTraceChallenge
🎯
Read Replicas and CQRS Pattern for Read-Heavy Systems
45 min · senior
senior

You are designing a backend system for a social media platform with 50 million daily active users (DAU). The system is read-heavy, with 90% of operations being reads. The business requires end-to-end encryption of user data at rest and in transit, and a P99 read latency under 200ms globally. The system must support offline data delivery and eventual consistency between writes and reads. The write workload is moderate but must not block reads. The system should scale horizontally and handle failover gracefully.

Constraints
  • End-to-end encryption of data at rest and in transit is mandatory
  • P99 read latency must be under 200ms globally
  • System must support offline data delivery with eventual consistency
  • Write operations must not block or degrade read performance
  • System must scale horizontally to support 50M DAU
  • Failover and disaster recovery must be automated and transparent
📋
Guided Steps
1/5
Step 1
Choosing the Write and Read Data Stores
Which databases or storage systems will you use for writes and reads, and why?
💡 Hint
Consider separation of concerns for writes and reads, and how to scale each independently.
Key Decision
Use PostgreSQL with synchronous replication for writes to ensure ACID compliance and data integrity, and deploy multiple read replicas using Amazon Aurora Read Replicas for horizontally scalable, low-latency reads.
Interviewer Expectation
Mid-level: Names PostgreSQL and read replicas. Senior: Explains why Aurora Read Replicas provide fast failover and how synchronous writes ensure durability without blocking reads.
⚠️Common Wrong Answer
Using a single monolithic database for both reads and writes, causing read queries to block writes and degrade latency under high load.
Step 2
Implementing CQRS Pattern
How will you separate the command (write) and query (read) paths to optimize for read-heavy workloads?
💡 Hint
Think about how to decouple writes from reads and how to propagate changes asynchronously.
Key Decision
Implement CQRS by directing all writes to the primary PostgreSQL instance and asynchronously replicating changes to read replicas via logical replication slots and Debezium CDC streaming into Kafka topics consumed by read models.
Interviewer Expectation
Mid-level: Mentions CQRS and asynchronous replication. Senior: Details Debezium CDC with Kafka for change event streaming and how it enables eventual consistency with bounded staleness.
⚠️Common Wrong Answer
Synchronous replication for reads causing write latency spikes and blocking under heavy write load.
Step 3
Ensuring Data Encryption and Security
How will you ensure end-to-end encryption of data at rest and in transit across the system?
💡 Hint
Consider encryption at multiple layers including storage, network, and application.
Key Decision
Use AWS KMS-managed keys for transparent data encryption at rest on Aurora and Kafka, enable TLS 1.3 for all network communication, and implement application-layer encryption with envelope encryption for sensitive fields before writing to the database.
Interviewer Expectation
Mid-level: Names TLS and encryption at rest. Senior: Explains envelope encryption benefits and key rotation strategies to minimize exposure.
⚠️Common Wrong Answer
Relying only on TLS without encrypting data at rest, risking data exposure if storage is compromised.
Step 4
Designing Offline Data Delivery and Sync
How will the system support offline data delivery and eventual consistency for clients?
💡 Hint
Think about client-side caching, sync tokens, and how to handle data reconciliation.
Key Decision
Implement client-side sync tokens with cursor-based pagination using Kafka offsets, deliver data snapshots via read replicas, and use a background sync service that reconciles client state with the latest CDC events to ensure eventual consistency.
Interviewer Expectation
Mid-level: Mentions client caching and sync tokens. Senior: Explains Kafka offset-based cursor sync and how background reconciliation handles conflicts and stale reads.
⚠️Common Wrong Answer
Using simple HTTP polling causing excessive load and inconsistent data views under high concurrency.
Step 5
Handling Failover and Scalability
How will you design failover and horizontal scaling for both read and write paths?
💡 Hint
Consider automated failover mechanisms and load balancing strategies for replicas.
Key Decision
Use Amazon Aurora's built-in automated failover for the primary write node, deploy multiple read replicas behind an Elastic Load Balancer with health checks, and use Kafka consumer groups for scalable CDC event processing.
Interviewer Expectation
Mid-level: Names Aurora failover and load balancer. Senior: Explains how Aurora failover latency impacts write availability and how Kafka consumer groups enable horizontal scaling of read model updates.
⚠️Common Wrong Answer
Manual failover processes causing prolonged downtime and inconsistent read availability.
🔌
API Design Challenge
Sketch 3 core API endpoints for this read-heavy system implementing CQRS and read replicas.
Hints
  • How does the client submit write requests to the system?
  • How does the client fetch read data efficiently and consistently?
  • How does the client acknowledge or sync offline data delivery?
📊
Scoring Rubric
▼ Show
Must Have - 60%
  • Separation of write and read data stores with real technologies named
  • Use of CDC streaming (Debezium + Kafka) for CQRS asynchronous replication
  • End-to-end encryption including envelope encryption and TLS
  • Offline data delivery with client sync tokens and eventual consistency
  • Automated failover and horizontal scaling strategies
Good to Have - 30%
  • Detailed explanation of partition keys or Kafka topic design for ordering
  • Discussion of key rotation and encryption key management
  • Handling of conflict resolution in background reconciliation
  • Load balancing strategies for read replicas
Bonus - 10%
  • Use of monitoring and alerting tools for failover detection
  • Design for multi-region replication and disaster recovery
Anti-patterns to Avoid
Using a single monolithic database for both reads and writes causing blocking and latency spikes under load, which leads to degraded system performance and poor user experience
Synchronous replication for reads causing write latency degradation and reduced throughput, resulting in slower writes and potential system bottlenecks
Relying solely on TLS without encrypting data at rest risking data exposure on storage compromise, which can lead to data breaches if storage media is accessed by unauthorized parties
HTTP polling for offline sync causing excessive load and poor scalability, as 50M DAU polling frequently can overwhelm servers and increase latency
Manual failover processes causing prolonged downtime and inconsistent read availability, leading to poor reliability and user dissatisfaction
✅
Model Answer
▼ Reveal

This design uses PostgreSQL as the primary write database with synchronous replication to ensure ACID compliance and durability. Read replicas are implemented using Amazon Aurora Read Replicas to provide scalable, low-latency reads. CQRS is implemented by separating write and read paths, with Debezium CDC streaming changes from PostgreSQL to Kafka topics, which update read models asynchronously, enabling eventual consistency. End-to-end encryption is ensured using AWS KMS-managed keys for data at rest, TLS 1.3 for data in transit, and application-layer envelope encryption for sensitive fields. Offline data delivery is supported via client-side sync tokens based on Kafka offsets, enabling efficient incremental sync and eventual consistency. Failover is automated using Aurora's built-in mechanisms, and read replicas are load balanced behind an Elastic Load Balancer. Kafka consumer groups enable scalable processing of CDC events to update read models.

Key Decisions
  • Use PostgreSQL with synchronous replication to ensure strong consistency and durability for writes without blocking reads
  • Implement CQRS by asynchronously replicating changes via Debezium CDC into Kafka topics consumed by read models, enabling eventual consistency with bounded staleness
  • Encrypt data at rest using AWS KMS-managed keys on Aurora and Kafka storage, and enable TLS 1.3 for all network communication
  • Apply envelope encryption at the application layer for sensitive fields before persisting to the database, with key rotation strategies to minimize exposure
  • Support offline data delivery with client-side sync tokens based on Kafka offsets and cursor-based pagination for efficient incremental sync
  • Automate failover using Amazon Aurora's built-in mechanisms to minimize downtime and ensure write availability
  • Load balance read replicas behind an Elastic Load Balancer with health checks to distribute read traffic and improve availability
  • Use Kafka consumer groups to horizontally scale CDC event processing and update read models reliably and fault-tolerantly
  • Implement a background reconciliation service to handle data conflicts and ensure eventual consistency between writes and reads
  • PostgreSQL primary with Amazon Aurora Read Replicas for scalable reads and strong consistency on writes
  • Debezium CDC with Apache Kafka for asynchronous change data capture enabling CQRS and eventual consistency
  • AWS KMS for transparent encryption at rest combined with TLS 1.3 for secure data in transit
  • Envelope encryption at application layer for sensitive data fields to ensure end-to-end encryption
  • Client-side sync tokens using Kafka offsets for efficient offline data delivery and incremental sync
  • Amazon Aurora automated failover for write availability and Elastic Load Balancer for read replica load distribution
  • Kafka consumer groups for scalable and fault-tolerant processing of CDC events to update read models
  • Background reconciliation service to handle data conflicts and ensure eventual consistency between writes and reads

Practice

(1/5)
1. In the training pipeline trace, at Step 5 the Feature Joiner component receives feature data from multiple sources. What happens if one source's feature data arrives late at this step?
easy
A. The Feature Joiner buffers the data and waits until all sources have arrived or a timeout occurs
B. The Feature Joiner discards the entire training batch and retries fetching all features
C. The Feature Joiner immediately proceeds with partial data, marking missing features as null
D. The Feature Joiner triggers an alert but continues processing with stale cached features

Solution

  1. Step 1: Understand Feature Joiner role at Step 5

    The Feature Joiner must combine features from multiple sources to create a consistent training dataset.

  2. Step 2: Analyze late arrival handling

    To maintain data consistency, the joiner buffers incoming data and waits for all sources or a timeout. Proceeding immediately risks inconsistent feature vectors; discarding entire batches is inefficient; using stale cached features risks training on outdated data.

  3. Final Answer:

    Option A -> Option A

  4. Quick Check:

    Feature Joiner buffers and waits or times out for consistency [check]
Hint: Feature Joiner buffers until all sources arrive or timeout [check]
Common Mistakes:
2. A system expects 1 million daily active users (DAU) with an average of 50 requests per user per day. Which component is best suited to handle the peak QPS estimation for capacity planning?
easy
A. Use a load balancer's metrics to estimate peak QPS from incoming traffic patterns
B. Use a relational database to directly measure QPS by counting requests in real-time
C. Use client-side logs aggregated asynchronously to estimate QPS after the fact
D. Use a CDN to estimate QPS by counting cache hits and misses

Solution

  1. Step 1: Identify component that reflects real-time incoming traffic

    Load balancer metrics provide immediate, accurate QPS data as they handle all incoming requests before backend processing.

  2. Step 2: Eliminate other options

    Relational DB counting is too slow and adds overhead; client logs are delayed and incomplete; CDN metrics reflect cached content, not all requests.

  3. Final Answer:

    Option A -> Option A

  4. Quick Check:

    Load balancer metrics reflect real-time QPS accurately [check]
Hint: Load balancer metrics reflect real-time QPS [check]
Common Mistakes:
3. If the reconciliation service that compares debit and credit entries is temporarily down, what is the immediate failure mode observed in the digital wallet system?
medium
A. The system marks transactions as pending and delays final confirmation to users until reconciliation completes
B. New transactions are blocked until reconciliation service recovers to prevent ledger divergence
C. Transactions continue normally but inconsistencies accumulate silently until reconciliation resumes
D. Debit entries are committed immediately but credit entries are delayed, causing temporary imbalance

Solution

  1. Step 1: Understand reconciliation service role

    It ensures debit and credit entries match before confirming transactions to users.

  2. Step 2: Analyze failure impact

    Blocking all transactions (B) reduces availability unnecessarily; silent accumulation (A) risks unnoticed errors; committing debit only (D) breaks double-entry. Marking transactions pending (C) delays confirmation but preserves consistency.

  3. Final Answer:

    Option A -> Option A

  4. Quick Check:

    Pending status delays confirmation -> preserves ledger integrity [check]
Hint: Pending transactions delay confirmation, not silent failure [OK]
Common Mistakes:
4. For a proximity service requiring low-latency queries and frequent updates of business locations, which spatial indexing approach is better and why?
medium
A. Use Geohash-based indexing because it supports efficient prefix queries and simpler updates
B. Use a full R-tree index because it guarantees minimal overlap and tight bounding rectangles
C. Use a grid-based fixed cell index because it provides exact bounding box queries without hierarchy
D. Use a Quadtree because it supports fast insertions and deletions with balanced trees

Solution

  1. Step 1: Compare update and query costs

    Quadtree (Use a Quadtree because it supports fast insertions and deletions with balanced trees) can become unbalanced and expensive to update frequently. R-tree (Use a full R-tree index because it guarantees minimal overlap and tight bounding rectangles) is complex and costly to maintain with frequent writes.

  2. Step 2: Evaluate Geohash benefits

    Geohash (Use Geohash-based indexing because it supports efficient prefix queries and simpler updates) encodes locations into fixed-length strings allowing efficient prefix queries and simpler incremental updates, ideal for low-latency and frequent writes.

  3. Final Answer:

    Option A -> Option A

  4. Quick Check:

    Geohash supports efficient prefix queries and simpler updates [check]
Hint: Geohash enables fast prefix queries and simpler frequent updates [OK]
Common Mistakes:
5. At 10x scale, which component is most likely to fail first in a system targeting 99.999% availability, and why?
hard
A. The database because it cannot handle 10x write throughput without sharding
B. The load balancer because it cannot maintain 10x concurrent TCP connections
C. The message queue because it runs out of disk space under 10x message volume
D. The chat servers because WebSocket connections are stateful and can't be resharded easily

Solution

  1. Step 1: Identify stateful bottlenecks at scale

    Chat servers maintain stateful WebSocket connections bound to server memory, limiting horizontal scaling.

  2. Step 2: Compare other components' scalability

    Databases can shard; load balancers can be scaled horizontally; queues can be partitioned and scaled with disk expansion.

  3. Final Answer:

    Option D -> Option D

  4. Quick Check:

    Stateful WebSocket servers -> resharding hard -> first bottleneck at 10x scale [check]
Hint: Stateful WebSocket servers -> resharding hard -> first bottleneck [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