system-design

CQRS — Command Query Responsibility Segregation

Separate the model you write with from the model you read with. A powerful idea that is applied far more often than it’s needed.

Prerequisites: Event-Driven Architecture, Caching Time to read: ~20 minutes

The problem

One data model serves both writes and reads, and they want opposite things.

Writes want normalization, constraints, and small focused transactions — one source of truth for every fact, so updates are safe.

Reads want exactly the shape the screen needs, with no joins, precomputed.

-- The write model, properly normalized
orders, order_items, products, customers, addresses, shipments

-- The read the dashboard needs, 5,000 times a second
SELECT o.id, c.name, c.email, SUM(oi.qty * oi.price) AS total,
       COUNT(oi.id) AS item_count, s.tracking_number, a.city
FROM orders o
JOIN customers c ON ... JOIN order_items oi ON ...
JOIN shipments s ON ... JOIN addresses a ON ...
GROUP BY ...

🚨 A six-way join with an aggregation, at 5,000 QPS. You can index it, cache it, and add replicas — but the fundamental mismatch remains: the shape that’s correct for writing is the wrong shape for reading.

CQRS separates them.

The core idea

flowchart LR
    C[Command<br/>PlaceOrder] --> W[Write model<br/>normalized, validated]
    W --> DB[(Write store)]
    DB -->|events / CDC| P[Projection]
    P --> RD[(Read store<br/>denormalized)]
    Q[Query<br/>GetOrderSummary] --> RD

Commands change state. They’re validated against the write model, enforce invariants, and typically return nothing but success or failure.

Queries read state. They hit a read model shaped exactly for the query, requiring no joins.

A projection keeps the read model updated from the write model — usually via events or CDC.

🚨 The crucial insight: the read model is a derived, disposable cache. It can be rebuilt from the write model at any time. That’s what makes it safe to have several of them, each optimized differently.

Levels of CQRS

🚨 CQRS is a spectrum, and most people jump straight to the expensive end. Naming the levels is a strong signal.

Level 0 — Separate methods

# Commands return nothing; queries have no side effects
def place_order(cmd) -> None: ...
def get_order_summary(order_id) -> OrderSummaryDTO: ...

Just clean design (Command-Query Separation). No infrastructure. Do this always.

Level 1 — Separate models, same database

Write through domain objects; read through purpose-built projections or views.

CREATE MATERIALIZED VIEW order_summaries AS SELECT ... ;
REFRESH MATERIALIZED VIEW CONCURRENTLY order_summaries;

✅ Reads are fast and simple. Still one database, one transaction, immediate or near-immediate consistency. ❌ Refresh cost.

🎙️ This level solves most real problems and almost nobody considers it. “Before separating stores, I’d use materialized views — same database, so no consistency gap, and it removes the join cost entirely.”

Level 2 — Separate databases

Writes to Postgres; reads from Elasticsearch, Redis, or a denormalized store, updated by events.

✅ Independent scaling and technology choice per side. Reads can be a completely different engine. ❌ 🚨 Eventual consistency, plus a projection pipeline to build and operate.

Level 3 — CQRS with event sourcing

The write side stores events; read models are projections of the event stream.

✅ Complete audit trail; new read models can be built by replaying history. ❌ Substantial complexity. → Event Sourcing

🚨 CQRS and event sourcing are independent. You can do either without the other. They’re often discussed together because event sourcing makes projections natural — but conflating them makes CQRS sound far more expensive than it needs to be.

What it buys you

1. Independent scaling. Most systems are 100:1 read-heavy. Scale the read side to 50 nodes and the write side to 3.

2. Query-shaped storage. Each read model matches exactly one screen. No joins at read time.

3. Multiple read models from one write model:

Write model: normalized orders
  ├── Elasticsearch  → full-text order search
  ├── Redis          → "my recent orders" per user
  ├── ClickHouse     → analytics and reporting
  └── Postgres view  → the admin dashboard

🚨 This is the strongest argument for CQRS, and it’s underappreciated. One authoritative write model, many purpose-built read models — each optimized for its access pattern, each independently rebuildable.

4. A simpler write model. Freed from serving queries, the domain model can focus purely on invariants and business rules.

5. Team independence. Read models can be added by other teams without touching the write side.

What it costs

⚖️ Be honest about this — it’s the difference between a good answer and a buzzword.

🚨 1. Eventual consistency, and it’s user-visible. The user places an order and their order list doesn’t show it yet. This is the problem that dominates real CQRS implementations.

Mitigations:

Read your own writes: after a command, read from the write model (or block briefly) for that user. → Consistency Models
Optimistic UI: the client displays the change immediately; the server confirms later.
Return the result from the command, so the UI doesn’t need to re-query.
Show a “processing” state honestly.

2. Two models to maintain. Every write-model change may require a projection change. They drift.

3. Projection lag and failure. The projection can fall behind or break, and now your read model is wrong with no error surfaced. You must monitor projection lag as a first-class metric.

4. Rebuild time. Reprojecting a billion records after a bug fix takes real time — hours or days — during which reads are stale or unavailable.

5. More infrastructure, more failure modes, more to debug.

6. Cognitive load. Every developer must know which model to use and understand the consistency implications.

Building projections

From events:

def on_order_placed(event):
    read_db.upsert("order_summaries", {
        "order_id": event.order_id,
        "customer_name": event.customer_name,   # denormalized at write time
        "total_cents": event.total_cents,
        "status": "placed",
    })

From CDC — read the write database’s replication log. No application changes, and nothing is missed. → CDC

🚨 Projections must be idempotent. They’ll reprocess events on restart, on redelivery, and on rebuild. Use upserts keyed on the entity ID, and track the last processed position. → Idempotency

Handling out-of-order events: include a version or sequence number and ignore anything older than what you’ve applied:

UPDATE order_summaries SET status = :status, version = :version
WHERE order_id = :id AND version < :version;

Rebuilding must be possible and practised. If you can’t rebuild a read model on demand, you don’t really have a derived store — you have a second source of truth that will diverge. Build the rebuild path early and run it periodically, not just when something breaks.

When to use it

Good fits:

A large read/write asymmetry — 100:1 or more, with genuinely different scaling needs.
Complex reads over a complex domain — the query needs a shape the write model can’t provide cheaply.
Multiple distinct read shapes from the same data (search + dashboard + analytics + API).
A rich domain model where invariants matter and queries would pollute it.
You already have events — projections are a small additional step.

Poor fits — and these are the common mistakes:

❌ Simple CRUD. If reads are “select the row you just wrote,” CQRS is pure overhead.
❌ Strong consistency required on reads. A trading position, an account balance at authorization.
❌ A small team or an early-stage product. The complexity isn’t repaid.
❌ “To be scalable.” A read replica and a cache solve most read-scaling problems with a fraction of the cost.

🎙️ The answer that scores well: “Before CQRS I’d try read replicas and a cache — that handles read scaling with far less complexity. CQRS earns its place when the read *shape is the problem, not just read volume — when we need several genuinely different projections of the same data.”*

🚨 That distinction — volume vs shape — is the key one. Read replicas solve volume. CQRS solves shape.

⚖️ Trade-offs

Level	Gain	Cost
Separate methods (CQS)	Cleaner code	None — just do it
Materialized views	Fast reads, immediate consistency, one database	Refresh cost
Separate read store	Independent scaling; purpose-built storage	Eventual consistency; a projection pipeline
Multiple read models	Each optimized for its use	Each must be maintained and rebuilt
With event sourcing	Full audit; rebuild any projection from history	Substantial complexity

In the real world

Most systems already do informal CQRS and don’t call it that: a search index alongside a relational database, a Redis cache of computed values, a reporting replica. CQRS is largely the formalization of what teams do anyway — which is a useful reframing, because it makes the pattern feel less exotic.
The eventual-consistency UX problem is the most commonly reported difficulty. Teams consistently find that the technical implementation is straightforward and the “user posted something and can’t see it” problem takes far more effort than expected.
Materialized views are the underrated middle ground. Postgres, Oracle, and SQL Server all support them, and REFRESH MATERIALIZED VIEW CONCURRENTLY gives you a fast read model without leaving your database or accepting a consistency gap.

🚨 Interview traps

Proposing CQRS for simple CRUD. It’s overhead with no benefit.
Not addressing the read-your-own-writes problem. It’s the main user-visible cost.
Conflating CQRS with event sourcing. They’re independent.
No projection monitoring. Lag means silently wrong reads.
No rebuild story. A read model you can’t rebuild is a second source of truth.
Using it for read volume when a replica and a cache would do. The case for CQRS is read shape.

🎙️ Soundbites

“Read replicas solve read volume; CQRS solves read *shape. The problem here is that the dashboard needs a six-way join and an aggregation — no amount of replicas fixes that, but a purpose-built projection does.”*
“I’d start with materialized views in the same database. Same consistency guarantees, no projection pipeline, and it removes the join cost. Separate stores only when we need a different engine.”
“The main cost is that a user places an order and doesn’t immediately see it. I’d handle that by returning the result from the command and reading from the write model for that user for a few seconds.”
“Projections must be idempotent and rebuildable, and projection lag is something I’d alert on — otherwise reads go silently stale with no error anywhere.”
“CQRS and event sourcing are separate decisions. We can have separate read models without storing events, and that’s a much smaller commitment.”

🛠️ Try it

1. Feel the shape problem. Build a normalized schema and write the “order summary” query with five joins and an aggregation. Time it over a million orders. Then create a materialized view and time the same query. The difference is the argument for CQRS, and materialized views get you most of it for very little.

2. Build a projection. Publish OrderPlaced events and maintain a denormalized order_summaries table from them. Then:

Replay the same event twice. Does your projection double-count? (It shouldn’t — make it idempotent.)
Deliver events out of order. Does the projection end up wrong? (Add version checking.)
Delete the read model and rebuild it from the event history. Time it — that number is your recovery time.

3. Experience the consistency gap. Add 500 ms of artificial projection lag. Place an order and immediately load the order list. Watch it not be there. Then implement read-your-own-writes and feel the difference.

4. Break a projection silently. Introduce a bug that makes the projection skip some events, with no error. Notice that nothing alerts. Then add projection lag and event-count monitoring.

Check yourself

1. What problem does CQRS actually solve — and what doesn't it solve?

It solves a **shape mismatch**: the normalized model that's correct for writing (one source of truth per fact, small transactions, enforced invariants) is the wrong shape for reading (denormalized, pre-joined, matching exactly what a screen needs). It also lets one write model feed several differently-optimized read models — search, dashboard, analytics — each rebuildable. It does **not** primarily solve read *volume*: read replicas and a cache handle that with far less complexity. If your query is fast but you need more of them, add replicas. If your query is structurally expensive regardless of hardware, that's the CQRS case.

2. Are CQRS and event sourcing the same thing?

No — they're independent decisions that are frequently discussed together. CQRS separates the read model from the write model; the write model can be an ordinary normalized relational schema, with projections fed by CDC or by events published alongside the write. Event sourcing stores the sequence of events as the source of truth and derives current state by replay. You can do CQRS without event sourcing (very common, and much cheaper), and event sourcing without CQRS (though it's awkward, since querying an event log directly is painful). Conflating them makes CQRS sound far more expensive than it needs to be.

3. What's the main user-visible cost of CQRS, and how do you handle it?

Eventual consistency between the write and read models: the user performs an action and the resulting data isn't in the read model yet, so their own change appears to have failed. This is the problem that dominates real implementations. Mitigations: **return the result directly from the command** so the UI doesn't need to re-query; **read-your-own-writes** — route that user's reads to the write model for a few seconds after their command; **optimistic UI** — display the change immediately and reconcile when confirmed; or **an honest "processing" state**. The technical projection work is usually straightforward; this UX problem takes more effort than teams expect.

4. Why must projections be idempotent and rebuildable?

**Idempotent** because they will reprocess events — on consumer restart, on at-least-once redelivery, on partition rebalance, and during any rebuild. A projection that increments a counter per event will silently produce wrong numbers; one that upserts keyed on the entity ID won't. Add version checking so out-of-order events don't overwrite newer state. **Rebuildable** because the read model is by definition derived and disposable — that's what makes it safe to have several. If you can't rebuild it on demand, you don't have a derived store; you have a second source of truth that will diverge and that you can't fix. Build and periodically exercise the rebuild path *before* you need it.

5. When is CQRS the wrong choice?

For simple CRUD, where reads are essentially "select the row you just wrote" — the read and write shapes are the same, so you'd add a pipeline and a consistency gap for nothing. When reads must be strongly consistent: an account balance at authorization time, a trading position, inventory at checkout. When the team is small or the product is early — the operational and cognitive costs outweigh the benefit, and you're still learning what the read shapes even are. And when the actual problem is read *volume* rather than read *shape*, since a read replica plus a cache solves that at a fraction of the complexity.