CQRS (Command Query Responsibility Segregation) in HLD - System Design Exercise

Design: CQRS-based System

Design the architecture separating command and query responsibilities including data flow, components, and database schema. Out of scope: detailed UI design and specific technology vendor choices.

Functional Requirements

FR1: Separate the system into command (write) and query (read) parts

FR2: Ensure commands update data consistently

FR3: Allow queries to be optimized for fast reads

FR4: Support eventual consistency between command and query data stores

FR5: Handle 10,000 concurrent users with 70% read and 30% write operations

FR6: API response time for queries should be under 150ms at p99

FR7: System availability should be 99.9%

Non-Functional Requirements

NFR1: Data consistency between command and query sides can be eventual, not immediate

NFR2: Write operations must be strongly consistent

NFR3: Read operations should not block writes

NFR4: System must scale horizontally

NFR5: Latency for command processing should be under 300ms

Think Before You Design

Questions to Ask

❓ Question 1

❓ Question 2

❓ Question 3

❓ Question 4

❓ Question 5

Key Components

API Gateway or Load Balancer

Command Service (handling writes)

Query Service (handling reads)

Command Database (transactional, relational or NoSQL)

Query Database (optimized for fast reads, possibly denormalized)

Event Bus or Message Queue for synchronization

Event Processor to update query database

Cache layer for queries

Design Patterns

Event Sourcing to capture all changes as events

Eventual Consistency for syncing read models

CQRS pattern itself for separation of concerns

Asynchronous messaging for decoupling command and query sides

Reference Architecture

          +-----------------+          
          |   API Gateway   |          
          +--------+--------+          
                   |                   
        +----------+----------+        
        |                     |        
+-------v-------+     +-------v-------+
| Command       |     | Query         |
| Service       |     | Service       |
+-------+-------+     +-------+-------+
        |                     |        
+-------v-------+             |        
| Command DB    |             |        
+-------+-------+             |        
        |                     |        
        |   Event Bus / MQ     |        
        +----------+----------+        
                   |                   
          +--------v--------+          
          | Event Processor |          
          +--------+--------+          
                   |                   
          +--------v--------+          
          | Query DB        |          
          +-----------------+          

Components

API Gateway

Nginx or AWS API Gateway

Routes requests to command or query services based on operation type

Command Service

REST/GraphQL API with transactional support

Handles all write operations ensuring strong consistency

Query Service

REST/GraphQL API optimized for read queries

Handles all read operations optimized for low latency

Command Database

Relational DB like PostgreSQL or NoSQL with ACID support

Stores the authoritative data for writes

Event Bus / Message Queue

Kafka, RabbitMQ, or AWS SNS/SQS

Transports events from command side to query side asynchronously

Event Processor

Microservice or Lambda function

Consumes events and updates the query database accordingly

Query Database

NoSQL DB like Elasticsearch or DynamoDB optimized for reads

Stores denormalized data optimized for fast queries

Cache Layer

Redis or Memcached

Speeds up frequent read queries

Request Flow

1. Client sends a write request to API Gateway

2. API Gateway routes the request to Command Service

3. Command Service validates and writes data to Command Database within a transaction

4. Command Service publishes an event describing the change to the Event Bus

5. Event Processor consumes the event asynchronously from the Event Bus

6. Event Processor updates the Query Database to reflect the change

7. Client sends a read request to API Gateway

8. API Gateway routes the request to Query Service

9. Query Service reads data from Query Database or Cache and returns response

Database Schema

Entities: User, Order, Product Command DB: normalized tables with foreign keys for consistency Query DB: denormalized documents or tables optimized for query patterns Relationships: User 1:N Orders, Order N:M Products via OrderProduct junction Events capture changes like OrderCreated, ProductUpdated

Scaling Discussion

Bottlenecks

Command Database write throughput limits

Event Bus message backlog under high write load

Event Processor lag causing stale query data

Query Database read load spikes

Cache invalidation complexity

Solutions

Scale Command Database vertically or shard by user or entity

Partition Event Bus topics and increase consumer instances

Parallelize Event Processors and use checkpointing

Use read replicas and horizontal scaling for Query Database

Implement cache expiration and event-driven cache invalidation

Interview Tips

Time: Spend 10 minutes understanding requirements and clarifying consistency needs, 15 minutes designing components and data flow, 10 minutes discussing scaling and trade-offs, 10 minutes for Q&A

Explain the separation of command and query responsibilities clearly

Discuss consistency trade-offs and eventual consistency implications

Highlight asynchronous event-driven communication

Mention database choices for transactional writes vs fast reads

Address scaling bottlenecks and solutions realistically

Practice

1. What is the main purpose of using CQRS in system design?

easy

A. To encrypt data for security purposes

B. To combine all database operations into a single service

C. To separate read and write operations for better scalability

D. To reduce the number of servers needed

Solution

1. Step 1: Understand CQRS concept

   CQRS stands for Command Query Responsibility Segregation, which means separating commands (writes) from queries (reads).

2. Step 2: Identify the main benefit

   This separation allows each part to be optimized and scaled independently, improving performance and maintainability.

3. Final Answer:

   To separate read and write operations for better scalability -> Option C

4. Quick Check:

   CQRS = Separate reads and writes [OK]

Hint: CQRS splits commands and queries for scaling [OK]

Common Mistakes:

• Thinking CQRS combines operations into one service
• Confusing CQRS with security encryption
• Assuming CQRS reduces server count directly

2. Which of the following is the correct way to describe the role of the 'Command' in CQRS?

easy

A. It processes write operations that change system state

B. It handles read-only queries to fetch data

C. It stores cached data for faster access

D. It manages user authentication and authorization

Solution

1. Step 1: Define Command role in CQRS

   Commands are responsible for write operations that modify the system's state.

2. Step 2: Differentiate from Query

   Queries only read data without changing it, so they are not commands.

3. Final Answer:

   It processes write operations that change system state -> Option A

4. Quick Check:

   Command = Write operations [OK]

Hint: Commands change data; queries only read [OK]

Common Mistakes:

• Confusing commands with queries
• Thinking commands handle caching
• Assuming commands manage security

3. Consider a system using CQRS where the write side updates a user profile and the read side serves user data. If the write side updates the user's email, what is the expected behavior on the read side immediately after the update?

medium

A. The read side instantly shows the updated email without delay

B. The read side deletes the user data until refreshed

C. The read side rejects the query until the write completes

D. The read side may show the old email briefly due to asynchronous update

Solution

1. Step 1: Understand asynchronous update in CQRS

   In CQRS, the read side is often updated asynchronously via events after the write completes.

2. Step 2: Identify read side behavior after write

   Because of this delay, the read side may temporarily show stale data until it receives the update event.

3. Final Answer:

   The read side may show the old email briefly due to asynchronous update -> Option D

4. Quick Check:

   Read side updates asynchronously = possible stale data [OK]

Hint: Reads update asynchronously, so data may lag briefly [OK]

Common Mistakes:

• Assuming immediate read consistency
• Thinking reads block until writes finish
• Believing read data is deleted during update

4. A developer implemented CQRS but notices that the read model is not updating after commands execute. What is the most likely cause?

medium

A. The read model database is corrupted and cannot be read

B. The command handler is not sending events to update the read model

C. The query side is trying to write data instead of reading

D. The system is using synchronous updates causing deadlocks

Solution

1. Step 1: Identify how read model updates in CQRS

   The read model updates via events sent by the command handler after state changes.

2. Step 2: Diagnose missing updates

   If the read model is not updating, likely the events are not being sent or processed properly.

3. Final Answer:

   The command handler is not sending events to update the read model -> Option B

4. Quick Check:

   Missing events cause read model stale data [OK]

Hint: Check if events are sent after commands [OK]

Common Mistakes:

• Blaming database corruption without evidence
• Confusing query side roles
• Assuming synchronous updates cause deadlocks here

5. You are designing a high-traffic e-commerce system using CQRS. Which approach best ensures that the read side remains highly available and scalable while keeping data reasonably fresh?

hard

A. Use event sourcing to asynchronously update read models and deploy multiple read replicas

B. Use a single database for both reads and writes to avoid data duplication

C. Synchronously update the read model within the command transaction to ensure consistency

D. Disable caching on the read side to always fetch fresh data from the write database

Solution

1. Step 1: Understand scalability needs in CQRS

   Separating reads and writes allows scaling read replicas independently to handle high traffic.

2. Step 2: Use event sourcing for asynchronous updates

   Event sourcing helps keep read models updated asynchronously, balancing freshness and availability.

3. Step 3: Evaluate other options

   Single database limits scalability; synchronous updates reduce availability; disabling caching hurts performance.

4. Final Answer:

   Use event sourcing to asynchronously update read models and deploy multiple read replicas -> Option A

5. Quick Check:

   Event sourcing + read replicas = scalable, fresh reads [OK]

Hint: Event sourcing + replicas = scalable reads with freshness [OK]

Common Mistakes:

• Using single DB limits scalability
• Synchronous updates reduce availability
• Disabling cache hurts performance