Catalog My learning Blog
System Design Roadmap: 100-Day Course for Engineers · Phase 5 · Lesson 48 · 4 min read

CQRS Pattern in System Design

On this page
  1. The order page that joins seven tables
  2. What is CQRS? Commands and queries want different models
  3. Three levels of CQRS: pick the smallest that works
  4. Worked example: a CQRS read model for order details
  5. CQRS with event sourcing
  6. CQRS drawbacks and when to avoid it
  7. Check yourself

The order page that joins seven tables

The order detail page joins orders, items, products, customers, payments, shipments and addresses. It's slow, and every attempt to speed it up with indexes or denormalized columns makes the write path messier. The problem is asking one model to serve two masters.

What is CQRS? Commands and queries want different models

A command changes state: PlaceOrder, CancelOrder. It cares about correctness: validation, invariants ("can't cancel a shipped order"), transactions. A normalized model fits.

A query reads state: "show order 991", "list my orders". It cares about speed and shape: exactly the fields the screen needs, pre-joined. A denormalized model fits.

CQRS (Command Query Responsibility Segregation) stops forcing one model to do both. Commands go through a write model built for rules; queries go through read models built for screens.

Commands flow through a write model built for correctness; queries flow through read models built for speed.
Commands flow through a write model built for correctness; queries flow through read models built for speed.

Three levels of CQRS: pick the smallest that works

LevelWhat's separatedConsistencyEffort
1. Code pathsCommands use the domain model (e.g. an ORM); queries use direct SQL or views into DTOsImmediate, same databaseLow
2. Read tablesCommands update normalized tables; denormalized read tables updated in the same transaction or soon afterImmediate or nearMedium
3. Separate storesWrite side publishes events; projections build read models in other stores (a document DB, a search index)EventualHigh

Level 1 alone often solves the "query code is tangled in domain logic" problem. You've likely done level 2 already with reporting views or summary tables. Level 3 is what people usually mean by CQRS, and it's where the cost appears.

Worked example: a CQRS read model for order details

Write side: commands like PlaceOrder and CancelOrder validate against the normalized schema and commit. After commit, an OrderChanged event is published (through an outbox, so it can't be lost).

Read side: a projector consumes the events and maintains order_details documents: one record per order with customer name, items, payment status and tracking number, exactly what the page shows. The page does one key lookup.

Other read models come from the same events: a search index for the support team, a per-customer order list, a warehouse dashboard. Adding a new screen means adding a projection, not reworking the write model.

CQRS with event sourcing

With event sourcing (Day 47), the event log already exists, so read models are projections of it. They can be rebuilt from scratch at any time by replaying events, which also makes fixing a projection bug easy: fix the code, rebuild.

CQRS drawbacks and when to avoid it

  • Eventual consistency. After CancelOrder returns, the read model may still say "active" for a moment. Handle it: return the new state from the command, update the UI optimistically, or read from the write side right after a change.
  • More moving parts: projectors, their failures and lag, more stores to run.
  • Overkill for simple CRUD. If reads and writes look alike, CQRS adds work without benefit.

Use it where read and write needs diverge sharply, typically high-traffic read screens over a complex domain.

Two practical points if you go to level 3. Monitor projection lag, how far each read model is behind the event stream, and alert on it the way you would on replica lag. And plan how to rebuild a projection without downtime: build the new version alongside the old one from the start of the stream, switch reads when it catches up, then drop the old one.

A note for .NET teams: splitting code into CreateOrderCommand and GetOrderQuery handlers, often with MediatR, is level 1 at most. It's a tidy code structure, not separate models. The benefits and costs in this lesson come from separating the read model's shape and storage, not from the naming.

Check yourself

Level 1, a code-organization split. Queries still read the write model's shape, so slow multi-join screens stay slow until you add read models shaped for them.

The read model is updated asynchronously from events. Return the new state from the command, update the UI optimistically, or read from the write side right after a change.

For simple CRUD where reads mirror writes, such as an admin form editing a settings table. Separate read and write models then add more code, a sync mechanism and eventual consistency without solving any problem. Reach for CQRS when reads and writes need clearly different shapes or scale.

A data structure shaped for one screen or query, such as a denormalized order_details table, kept up to date from the write side's changes or events. Queries read it directly, with no joins or business logic, so they stay fast and simple.

No. CQRS separates the models for reading and writing; event sourcing stores state as a log of events. They pair well, since events can feed read models, but CQRS works fine with a normal database and a read model updated by triggers, outbox events or change data capture.