CQRS (Command Query Responsibility Segregation)

Design a CQRS-based backend that scales read and write workloads independently using a transactional command model and asynchronously updated read models. Covers event/CDC pipelines, projection correctness, caching, and consistency trade-offs at high scale.

Problem Statement — what are we building and why?

In many real systems, reads and writes have fundamentally different shapes:

  • Writes must enforce business invariants (e.g., you can’t oversell inventory, you can’t double-charge a card, you can’t follow yourself).
  • Reads must be fast and flexible (feeds, dashboards, search, “my orders” pages), often requiring denormalized data.

CQRS (Command Query Responsibility Segregation) is a design pattern that separates the write model (commands) from the read model (queries) so each can be optimized and scaled independently. CQRS does not inherently require microservices, Kafka, or event sourcing, but it is frequently paired with:

  • Event streaming / PubSub for propagating changes to read models.
  • Event sourcing for an append-only source of truth.
  • Transactional outbox to reliably publish events.
  • Sagas to coordinate multi-service workflows.

This case study mirrors an interview: we’ll design a generic but concrete CQRS system for an e-commerce “Order” domain (place order, cancel order, view order history, order dashboard). The same architecture applies to ride-hailing, banking ledgers, social feeds, inventory, etc.

References for grounding:

Requirements

Functional Requirements (user-facing features, 3-5 key ones)

  1. Place Order (Command)

    • Validate cart, pricing, inventory reservation, and payment authorization.
    • Return an order_id and a status.

  2. Cancel Order (Command)

    • Only allowed in certain states (e.g., before shipment).
    • Triggers compensations (release inventory, void payment auth).

  3. Get Order Details (Query)

    • Fast read for order summary and line items.

  4. List My Orders (Query)

    • Paginated order history for a user.

  5. Operations Dashboard (Query)

    • Near-real-time metrics (orders per minute, failures by reason), filterable.

Non-Functional Requirements (latency <Xms, availability 99.99%, consistency model, durability)

  • Availability

    • Command API: 99.95% (writes are harder; correctness matters)
    • Query API: 99.99% (reads should stay up even if projections lag)

  • Latency (P99)

    • Commands (place/cancel): < 200ms excluding third-party payment latency; with payment, target < 800ms.
    • Queries (order details / list): < 50ms from cache, < 150ms from read DB.

  • Consistency model

    • Write model: strong consistency within an Order aggregate (single order’s state transitions are linearizable).
    • Read model: eventual consistency (seconds-level lag acceptable), with an option for read-your-writes for critical screens.

  • Durability

    • All accepted commands must survive machine loss and restarts.
    • Events must be durably recorded and replayable (at least for a bounded window).

  • Security & compliance (typical follow-up)

    • PII encryption at rest, TLS in transit.
    • Audit trail for state transitions.

Capacity Estimation (back-of-envelope calculations)

We’ll pick concrete numbers (call out that they’re estimates, as you would in interviews).

Traffic assumptions

  • DAU: 50M
  • Orders per DAU per day: 0.2 (1 order per 5 days)
    • Orders/day = 50M × 0.2 = 10M orders/day
  • Writes per order: ~3 commands on average (place, updates, cancel/return events)
    • Write commands/day ≈ 10M × 3 = 30M commands/day

Using cheat sheet: 1 day ≈ 10^5 seconds.

  • Average command QPS: 30M / 86,400 ≈ 347 QPS
  • Peak QPS: 3× average ≈ 1,050 QPS

Reads are usually much higher:

  • Order detail views: 5 per order (user checks status, emails, support)
    • 10M × 5 = 50M detail reads/day
  • Order list views: 1 per DAU/day = 50M list reads/day

Total reads/day ≈ 100M.

  • Average read QPS: 100M / 86,400 ≈ 1,157 QPS
  • Peak read QPS: 3× ≈ 3,470 QPS

This is already read-heavy, and many real systems are 10–100× more read-heavy.

Storage estimates

Write DB (transactional)

Per order (normalized):

  • Order row: ~500 bytes
  • Line items: average 3 items × 300 bytes = 900 bytes
  • Payment/inventory references: ~300 bytes

Total per order ≈ 1.7KB (excluding indexes; round to 2KB with overhead).

  • Storage/day: 10M × 2KB = 20GB/day
  • Storage/year: 20GB × 365 ≈ 7.3TB/year (before replication)

Events / outbox

Assume 5 events/order average, 500 bytes/event (JSON/Avro/Proto payload + metadata):

  • Events/day: 10M × 5 = 50M events/day
  • Storage/day: 50M × 500B = 25GB/day
  • Storage/year: ~9TB/year

Read models

Read models are denormalized; assume ~3KB/order for summaries + indexes:

  • 10M × 3KB = 30GB/day
  • ~11TB/year

Bandwidth (rough)

  • Read response size average 2KB; peak read QPS 3,470
    • 3,470 × 2KB ≈ 6.9MB/s (not huge; caches/CDN reduce further)

Cache sizing

If 5% of orders are “hot” (recent) and frequently accessed:

  • Hot orders/day: 10M × 5% = 500k
  • Cache entry 2KB + overhead; assume 4KB
  • Cache size ~ 500k × 4KB ≈ 2GB

Add headroom for lists, user order lists, and metadata: plan 20–50GB Redis for a region.

High-Level Design with architecture diagram (mermaid)

flowchart LR
  subgraph Clients
    W[Web/Mobile]
    OPS[Ops Dashboard]
  end

  W --> CDN[CDN/Edge Cache]
  CDN --> GW[API Gateway + Auth + Rate Limiter]
  OPS --> GW

  subgraph CommandSide[Command Side]
    CMD[Command API / Order Service]
    WDB[(Write DB: Postgres/Spanner)]
    OUT[(Outbox Table)]
  end

  subgraph Eventing[Eventing / CDC]
    RELAY[Outbox Relay / CDC Connector]
    BUS[(Kafka / PubSub Topics)]
    DLQ[(Dead Letter Queue)]
  end

  subgraph Projections[Read Model Builders]
    PROJ1[OrderSummary Projector]
    PROJ2[UserOrders Projector]
    PROJ3[OpsMetrics Projector]
    IDEMP[(Idempotency/Dedupe Store)]
  end

  subgraph QuerySide[Query Side]
    QAPI[Query API]
    CACHE[(Redis Cache)]
    RDB1[(Read DB: OrderSummary KV/Doc)]
    RDB2[(Read DB: UserOrders Wide-Column)]
    RDB3[(Read DB: Analytics/OLAP)]
  end

  GW --> CMD
  CMD --> WDB
  CMD --> OUT

  OUT --> RELAY --> BUS

  BUS --> PROJ1 --> RDB1
  BUS --> PROJ2 --> RDB2
  BUS --> PROJ3 --> RDB3

  PROJ1 --> CACHE

  BUS -->|bad events| DLQ
  PROJ1 --> IDEMP
  PROJ2 --> IDEMP

  GW --> QAPI
  QAPI --> CACHE
  QAPI --> RDB1
  QAPI --> RDB2
  QAPI --> RDB3

Key idea: Commands write to a strongly consistent store, then changes propagate via events/CDC to projection workers that build one or more read models optimized for queries.

Detailed Design for each component

1) API Gateway / BFF

Responsibilities:

  • Auth (JWT/OAuth), request validation
  • Rate limiting (token bucket) per user/IP
  • Routing: /commands/* to Command API, /queries/* to Query API
  • Optional: response caching for safe GETs

Why it matters in CQRS:

  • It enforces the separation at the edge: commands and queries have different SLAs and scaling.

2) Command API (Order Service)

Responsibilities:

  • Validate command intent and business rules.
  • Execute state changes atomically.
  • Emit domain events reliably.

Implementation choices:

  • Stateless app servers; scale horizontally.
  • Use single DB transaction per command.

Command handling flow (PlaceOrder):

  1. Validate idempotency key.
  2. Validate user/cart.
  3. Create order row + line items.
  4. Insert outbox event(s) in same transaction.
  5. Commit.
  6. Return order_id and order_version.

3) Write DB

Default: SQL (Postgres/MySQL) or Spanner

Justification:

  • Commands are where invariants live; SQL gives:
    • transactions
    • constraints
    • uniqueness
    • foreign keys
    • familiar correctness semantics

If global multi-region is required with strong consistency, Spanner-like systems plus Change Streams can replace custom CDC.

4) Transactional Outbox + Relay (CDC)

Problem it solves:

  • Without outbox, you can get: “DB commit succeeded, event publish failed.”

Outbox pattern:

  • In the same transaction as the write, insert an outbox row:
    • event_id, aggregate_id, type, payload, created_at
  • Relay process polls (or uses logical replication) and publishes to Kafka/PubSub.

Reliability:

  • Relay uses at-least-once publish.
  • Consumers must be idempotent.

5) Event Bus (Kafka / PubSub)

Why:

  • Fanout to multiple projections (order summary, user history, ops metrics, notifications).

Design notes interviewers probe:

  • Partitioning key: aggregate_id (order_id) to preserve per-order ordering.
  • Delivery: at-least-once; duplicates possible.
  • Retention: keep 7–30 days to allow replays/backfills.

6) Projection workers

Responsibilities:

  • Consume events.
  • Update read models.
  • Maintain idempotency and ordering guarantees.

Projection correctness rules:

  • Idempotent application: store processed event_id or (aggregate_id, version).
  • Monotonic versioning: each order event increments order_version.
  • DLQ for poison pills: invalid payloads, schema mismatch.

7) Read DB(s)

CQRS encourages polyglot persistence:

  • OrderSummary store (RDB1)

    • Query pattern: GetOrder(order_id)
    • Good fit: key-value/document (DynamoDB/Cassandra/DocDB) or even Postgres read replica at smaller scale.

  • UserOrders store (RDB2)

    • Query pattern: ListOrders(user_id, cursor, limit) sorted by time.
    • Good fit: wide-column (Cassandra/DynamoDB) with partition by user_id.

  • OpsMetrics store (RDB3)

    • Query pattern: time series aggregates.
    • Good fit: OLAP (BigQuery/Snowflake), or time-series DB.

8) Query API

Responsibilities:

  • Serve read traffic from caches and read models.
  • Pagination, filtering, shaping.
  • Optional “freshness” semantics (read-your-writes).

Scaling:

  • Stateless; scale independently from command side.

Database Design with schema

Choice of SQL vs NoSQL with JUSTIFICATION

Write side: SQL (Postgres/Spanner)

  • We need atomic multi-row updates (order + items + outbox).
  • We need invariants (no duplicate order numbers, valid state transitions).
  • SQL makes correctness easier and reduces application-level concurrency bugs.

Read side: NoSQL / specialized stores

  • Read access patterns are predictable and can be denormalized.
  • We want low-latency reads and horizontal scaling.
  • Eventual consistency is acceptable for most views.

Write DB schema (Postgres-style)

orders

  • order_id (UUID, PK)
  • user_id (UUID, indexed)
  • status (enum: PENDING, CONFIRMED, CANCELED, SHIPPED)
  • total_amount_cents (int)
  • currency (char(3))
  • order_version (bigint) — increments per state change
  • created_at, updated_at

Indexes:

  • PK on order_id
  • (user_id, created_at DESC) for support/admin queries (not the main read path)

order_items

  • order_id (FK)
  • item_id (UUID)
  • sku (string)
  • qty (int)
  • unit_price_cents (int)
  • PK: (order_id, item_id)

command_dedup

  • idempotency_key (string, PK)
  • user_id (UUID)
  • command_name (string)
  • request_hash (optional)
  • order_id (UUID)
  • created_at

Purpose:

  • Prevent duplicate PlaceOrder due to retries/timeouts.

outbox_events

  • event_id (UUID, PK)
  • aggregate_type (e.g., “Order”)
  • aggregate_id (UUID)
  • aggregate_version (bigint)
  • event_type (string)
  • payload (jsonb / bytes)
  • created_at
  • published_at (nullable)

Indexing strategy:

  • Index on (published_at, created_at) for relay scanning.
  • Optional partial index WHERE published_at IS NULL.

Read DB schema

RDB1: order_summary (document/kv)

Key: order_id Value:

{
  "order_id": "...",
  "user_id": "...",
  "status": "SHIPPED",
  "total": {"amount_cents": 2599, "currency": "USD"},
  "items": [{"sku": "abc", "qty": 2, "price_cents": 999}],
  "shipping": {"carrier": "...", "tracking": "..."},
  "order_version": 17,
  "updated_at": "..."
}

Indexing:

  • Primary key lookup only. If you need secondary attributes (status filtering), maintain separate indexes or a separate read model.

RDB2: user_orders_by_time (wide-column)

Partition/shard key selection:

  • Partition key: user_id
  • Clustering key: created_at DESC, order_id

Why:

  • The dominant query is “orders for a user sorted by time.”
  • This avoids scatter-gather.

Hot partition risk:

  • Some users may have extremely high order volume (rare). If needed:
    • bucket by month: partition key (user_id, yyyy_mm)
    • or add a small bucket (user_id, bucket) for heavy users.

RDB3: orders_metrics_minute

  • Key: (minute_ts, metric_name, dimension)
  • Value: counts/sums

Built from events; used for dashboards.

API Design with REST endpoints, request/response schemas

We’ll expose REST externally (web/mobile), and optionally use gRPC internally.

Commands (REST)

POST /commands/orders:place

Headers:

  • Idempotency-Key: <uuid>

Request:

{
  "user_id": "u_123",
  "cart_id": "c_456",
  "payment_method_id": "pm_789",
  "shipping_address_id": "addr_1"
}

Response (202 or 200 depending on sync/async payment):

{
  "order_id": "o_abc",
  "status": "PENDING",
  "order_version": 1,
  "accepted_at": "2026-08-14T12:00:00Z"
}

Notes:

  • Returning order_version enables read-your-writes strategies on the query side.

POST /commands/orders/{order_id}:cancel

Headers:

  • Idempotency-Key

Request:

{ "reason": "USER_REQUEST" }

Response:

{
  "order_id": "o_abc",
  "status": "CANCELED",
  "order_version": 5
}

Queries (REST)

GET /queries/orders/{order_id}

Response:

{
  "order": { /* order_summary document */ },
  "freshness": {
    "read_model_version": 17,
    "source": "cache|read_db"
  }
}

Optional read-your-writes:

  • Client may pass ?min_version=17.
  • Query API can wait briefly or fallback (discussed below).

GET /queries/users/{user_id}/orders?cursor=...&limit=20

Response:

{
  "orders": [{"order_id":"...","status":"...","created_at":"..."}],
  "next_cursor": "..."
}

Internal (gRPC, optional)

  • OrderCommandService.PlaceOrder(PlaceOrderRequest) returns (PlaceOrderResponse)
  • OrderQueryService.GetOrder(GetOrderRequest) returns (GetOrderResponse)

gRPC is common internally for lower overhead and stronger contracts, but REST is usually sufficient for interviews.

Deep Dive 1: How to handle hot partitions / celebrity problem

Even though we’re designing “orders,” the same hotspot issue appears everywhere:

  • A single key gets disproportionate traffic.
  • A single partition in Kafka or Cassandra becomes overloaded.

Where hotspots show up in CQRS

  1. Event bus partition hotspots

    • If partition key is order_id, load is spread well.
    • If you choose a coarse key (e.g., user_id), a whale user could dominate.

  2. Read model hotspots

    • user_orders_by_time partitioned by user_id can hotspot for a whale.

  3. Cache hotspots

    • A single order being checked repeatedly (support incident, viral issue).

Mitigations

  • Choose partition keys aligned with uniformity

    • For event ordering, use aggregate_id (order_id). Orders are typically uniformly distributed.

  • Bucket heavy partitions

    • For user_orders_by_time, use (user_id, yyyy_mm) or (user_id, bucket).

  • Hybrid materialization (Instagram-style analogy)

    • Instagram feed systems often use hybrid fanout to handle celebrity accounts.
    • CQRS analog: don’t precompute expensive views for whales; compute-on-read or maintain a specialized read model.

  • Load shedding and rate limits

    • For pathological keys, apply per-key rate limits at Query API.

  • Cache stampede protection

    • Use request coalescing (single flight) and short TTL jitter.

Deep Dive 2: Consistency vs availability trade-offs specific to CQRS

CQRS almost always implies eventual consistency on the read side. Interviewers will push: “What does the user see right after placing an order?”

Baseline behavior

  • Command returns success after write transaction commits.
  • Read model updates asynchronously (events → projections).
  • User might see the old state for a short window.

Options for “read-your-writes”

  1. Version-based waiting (recommended)

    • Command returns order_version = v.
    • Query request includes min_version=v.
    • Query API checks read model version; if behind, it can:
      • wait up to e.g. 200–500ms (long-poll)
      • or return 202 Processing with retry-after.

  2. Fallback to write DB (selective)

    • If read model is behind, query write DB for that single order.
    • Pros: strong freshness
    • Cons: couples query path to write DB; can overload it at scale.

  3. Sticky reads / session affinity

    • Route the user to a region where projections have caught up.
    • Works better in single-region or with ordered replication.

  4. Write-through into a “hot read store”

    • On command commit, also synchronously update a small read store (or cache) for that order.
    • Pros: immediate reads for that order
    • Cons: more write latency and complexity; must still converge with projections.

CAP-style framing (what to say in interviews)

  • Command side prioritizes consistency (within aggregate) and durability.
  • Query side prioritizes availability and latency, accepting bounded staleness.

Netflix’s Tudum post is a good real-world example of teams evolving CQRS read distribution to improve freshness/performance characteristics for their use case.

Deep Dive 3: Caching strategy with invalidation approach

CQRS makes caching easier because the query side is isolated and often immutable-ish between updates.

Read flow:

  1. Query API checks Redis: GET order:{order_id}
  2. On miss, read from RDB1 (order_summary)
  3. Populate cache with TTL (e.g., 30–120s) + jitter

Write flow:

  • Do not update cache directly from Command API (keeps separation).
  • Instead, projection worker updates the read DB and then either:
    • Invalidate cache keys (DEL order:{id})
    • or refresh cache with the new document

Refreshing is often better for hot keys; invalidation is simpler.

Invalidation correctness

Because events can be duplicated or reordered across partitions, cache updates must be guarded:

  • Include order_version in cached value.
  • Only overwrite if incoming version is newer.

Stampede and cold start

  • Use single-flight locks (SETNX lock:order:{id}) to prevent thundering herd.
  • Prewarm cache for the most recent orders if needed.

Scalability & Performance section

Scaling reads independently

  • Query API scales horizontally behind a load balancer.
  • Read DBs scale based on their access patterns:
    • KV/doc store for direct lookups
    • wide-column for timeline-like queries
    • search/OLAP for analytics

Scaling writes independently

  • Command API scales horizontally.
  • Write DB scales via:
    • vertical scaling + read replicas (though commands mostly write)
    • partitioning/sharding by order_id if necessary
    • or moving to distributed SQL (Spanner) for global scale.

Eventing throughput

  • Peak commands ~1k QPS, events maybe 5k QPS; modest.
  • But the architecture holds when this grows by 100×:
    • Kafka topics partitioned by aggregate_id
    • projection consumers scaled by partition count

Failure modes and resilience

  • Projection lag

    • Symptoms: stale reads
    • Mitigation: consumer autoscaling, backpressure, monitoring lag per partition

  • Poison pill event

    • Mitigation: DLQ + alerting + replay tooling

  • Outbox relay stuck

    • Mitigation: multiple relay instances with leader election; metrics on unpublished outbox rows

  • Read DB corruption due to buggy projector

    • Mitigation: replay/backfill pipeline, immutable event log retention, canary projector

Monitoring to include:

  • Command latency + error rates
  • DB commit latency
  • Outbox backlog
  • Kafka consumer lag
  • Projection success/failure counts
  • Read cache hit rate

Trade-offs and Alternatives — what would you do differently for different constraints?

1) “Do we really need CQRS?”

If:

  • reads and writes are similar
  • traffic is modest
  • team wants simplicity

Then a single relational DB with normalized schema + read replicas + caching may be better.

CQRS adds complexity:

  • multiple datastores
  • eventual consistency
  • replay/backfill tooling
  • schema evolution challenges

2) CQRS without Kafka (CDC-based)

Instead of application-emitted events:

  • Use DB-level CDC (e.g., Spanner Change Streams, Debezium on Postgres)
  • Pros: fewer code paths; avoids “forgot to publish event”
  • Cons: event semantics become “row changes,” not domain events; harder to express intent

3) Event Sourcing vs state-stored write model

State-stored (what we designed):

  • Store current state in SQL tables.
  • Emit domain events for projections.

Event sourcing:

  • Store only append-only events as the source of truth.
  • Rebuild state by replay (with snapshots).

Event sourcing pros:

  • perfect audit trail
  • easy replay and temporal queries (“what did we know then?”)

Cons:

  • harder querying on write side
  • event schema evolution becomes critical
  • operational complexity (snapshotting, rebuilding)

4) Exactly-once processing vs at-least-once

  • Exactly-once end-to-end is extremely difficult across distributed boundaries.
  • Design for at-least-once with:
    • idempotent consumers
    • dedupe store
    • version checks

5) Multi-region

Options:

  • Single-region write, multi-region read models (simpler)
  • Multi-region writes require distributed transactions (Spanner-like) or careful partitioning by region

Interviewer follow-ups (and how this design answers them)

  1. Where do you draw aggregate boundaries?

    • Order is an aggregate; invariants enforced per order.
    • Avoid cross-order invariants in a single transaction; use sagas if needed.

  2. How do you guarantee idempotency for commands?

    • Idempotency-Key + command_dedup table.
    • Return same order_id for retries.

  3. How do you publish events reliably?

    • Transactional outbox + relay.

  4. How do you handle read-your-writes?

    • Version-based waiting or selective fallback to write DB.

  5. How do you rebuild projections?

    • Replay from Kafka retention window or from stored outbox/events table.
    • Maintain backfill jobs and versioned projector code.

  6. How do you evolve event schema safely?

    • Version events (event_type + schema version).
    • Backward-compatible changes; schema registry; upcasters.

  7. How do you handle hot partitions?

    • Proper partition keys, bucketing, hybrid materialization.

Summary

This CQRS design:

  • Keeps writes correct using a transactional command model (SQL + invariants).
  • Keeps reads fast and scalable using purpose-built read models (KV/doc + wide-column + OLAP) and caching.
  • Propagates changes reliably using transactional outbox + event bus + idempotent projections.
  • Embraces eventual consistency on the read side while offering pragmatic read-your-writes techniques.

CQRS is powerful when read and write concerns diverge—but it’s also an architectural commitment. In interviews, the strongest answers highlight not only the architecture, but also the failure modes (duplicates, reordering, projection bugs, hot keys) and the operational tooling required to keep the system healthy.