CQRS Pattern in System Design
On this page
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.
Three levels of CQRS: pick the smallest that works
| Level | What's separated | Consistency | Effort |
|---|---|---|---|
| 1. Code paths | Commands use the domain model (e.g. an ORM); queries use direct SQL or views into DTOs | Immediate, same database | Low |
| 2. Read tables | Commands update normalized tables; denormalized read tables updated in the same transaction or soon after | Immediate or near | Medium |
| 3. Separate stores | Write side publishes events; projections build read models in other stores (a document DB, a search index) | Eventual | High |
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
CancelOrderreturns, 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.