Engineering Atlas › System Design Fundamentals

Separating reads from writes with CQRS

11 min read · last reviewed 2026-10-17

Open in the interactive app: practice, quiz, mock interview →

In one minute

How it works

Two models, one source of truth

A command such as PlaceOrder or CancelOrder goes to a handler that loads the aggregate, checks rules ("cannot cancel a shipped order"), and writes the new state. It returns success or failure, not data for display. Name commands after business tasks ("Book room"), not field updates ("Set status to Reserved"), so the handler sees the user's intent and can check the rules that go with it (Azure Architecture Center). Queries never touch that model. They read from views built for them: an order history table keyed by customer, a search index, a counter in a cache. The write store remains the source of truth; every view can be rebuilt from it.

Keeping views up to date

Three common ways, in order of coupling:

Projections

A projector is a consumer that maps each change to view updates, for example OrderPlaced increments customer_summary.order_count and adds to total_spent. It must be idempotent (store the last processed position or event ID in the same transaction as the view update) and ordered per aggregate. Rebuilding a view means resetting it and replaying from the beginning, which you will do whenever a view's shape changes. If the projector polls a table by an auto-increment ID instead of consuming a log, beware: IDs are assigned before commit, so a reader can pass an ID whose transaction commits a moment later and never see it (Dudycz).

Living with lag

After a user places an order, the order list might not show it for 300 ms. Options: return the new state in the command response and let the client merge it, have the client wait until the view's position passes the command's position ("read your writes" via a version token), or route that one user's next read to the write store. Pick per screen; most views tolerate seconds of lag.

Down the stack

Layer 1The contract

Commands are processed against the write store with its normal guarantees: serialisable or at least per-row atomic updates, constraint checks, and a commit point. Queries against a projected view see a prefix of the committed history: every change up to some position, nothing after it, in order. The gap between the write store's head and the view's position is the lag, and the contract for each view is a lag budget plus monotonic progress (a view never goes backwards). Read-your-writes is not provided unless the client waits for a position.

Layer 2The mechanism

With Postgres as the write store, change data capture uses logical decoding. A logical replication slot decodes WAL records into row-level changes through an output plugin such as pgoutput; Debezium reads the slot and emits change events tagged with their LSN (log sequence number). Each projector applies events and stores the last applied LSN or broker offset in the same transaction as the view update, so restart resumes exactly. The slot tracks confirmed_flush_lsn; Postgres retains all WAL after that point until the consumer confirms it. A slot's position is persisted only at checkpoints, so after a crash it can go back and resend recent changes; Postgres leaves avoiding double effects to the client, which is one more reason projectors must be idempotent (Postgres docs). Until Postgres 17, logical slots also lived only on the primary, so a failover lost the CDC position; 17 added failover slots that are synchronised to a standby (Postgres 17 release notes). A version token returned to the client is just a commit LSN that a query can wait for.

Layer 3The machine

Every commit appends WAL records sequentially and calls fsync (batched across concurrent commits by group commit), into 16 MB WAL segment files. Logical decoding reads that same WAL, so CDC adds sequential read I/O and CPU for decoding, not extra writes on the hot path. A stalled consumer is dangerous: retained WAL grows at the write rate, for example 20 MB per second becomes 1.7 TB per day, until the disk fills and the primary stops. The default is to keep WAL for a slot without limit. Set max_slot_wal_keep_size (Postgres 13 and later), which invalidates a slot that falls too far behind instead of filling the disk (Postgres docs); Postgres 18 also adds idle_replication_slot_timeout to drop slots nobody has used for a while (Postgres 18 release notes). An invalidated slot means the projector must be re-snapshotted, which is far cheaper than a primary outage. On the read side, a denormalised row keyed for the query is found with a B-tree descent of 3 or 4 levels of 8 KB pages, usually all in the buffer cache, a few microseconds. The normalised join it replaces may touch hundreds of pages across five tables and spill sorts to disk.

Why it's built this way

  1. Writes want normalised data to keep invariants cheap to check; reads want data pre-joined in the shape of the screen.
  2. One physical layout cannot be optimal for both, so the data is stored twice in different layouts.
  3. Keeping the second copy synchronously would put every view on the write path, so it is updated asynchronously and lags.
  4. The database already writes an ordered change log for crash recovery, so CDC reuses the WAL instead of inventing a new feed.

Diagram

flowchart LR
    U["Client"] -->|"command"| CH["Command handler"]
    CH -->|"validate + write"| W[("Write store")]
    W -->|"change feed / outbox"| PR["Projectors"]
    PR --> V1[("Order history view")]
    PR --> V2[("Search index")]
    PR --> V3[("Dashboard counters")]
    U -->|"query"| QH["Query handlers"]
    QH --> V1
    QH --> V2
    QH --> V3

Worked example

A food-delivery platform stores orders in Postgres: 400 writes per second at peak. Reads are 25,000 per second: customers checking order history, restaurants polling their live queue, and a support dashboard searching by phone number or address. The history query joins five tables and costs 30 ms; the support search uses LIKE across joined columns and costs up to 2 seconds.

With CQRS, the order service writes only to normalised tables and an outbox. A projector consumes the change stream and maintains three views: a customer_orders table keyed by (customer_id, placed_at) holding a pre-rendered summary row (2 ms reads), a per-restaurant live queue in Redis, and a search index for support. Projector lag p99 is 400 ms.

The restaurant queue cannot be stale by much, so its projector runs with its own consumer group and alerts at 2 seconds of lag. For customers, the API returns the created order in the PlaceOrder response so the app shows it immediately. Primary database CPU falls from 70% to 25% because read traffic moved to stores that are cheap to scale horizontally.

In the wild

Trade-offs

OptionStrengthsWeaknessesChoose when
Single model (CRUD)Simple, strongly consistent readsReads and writes compete, queries get complexBalanced, modest workloads
CQRS with shared databaseSeparate code paths, consistent viewsWrite cost grows with views, one store to scaleYou want clean separation without lag
CQRS with separate read storesIndependent scaling, best-fit stores per queryEventual consistency, projector operationsHeavy, varied read patterns
CQRS with event sourcingFull history, any view rebuildableHighest complexity, schema evolution of eventsAudit-heavy domains needing temporal queries

Pitfalls in production

Leadership lens

Check yourself

  1. Why should a projector record its last processed position in the same transaction as the view update?

    Show answer

    So a crash cannot leave the view updated but the position not advanced, which would apply the event twice on restart, or the reverse, which would skip it.

  2. A user creates an item and the list page does not show it. Name two fixes.

    Show answer

    Return the created item from the command and merge it client-side, or pass a version token so the query waits until the view has caught up to that version.

  3. When is a single shared database still a reasonable CQRS implementation?

    Show answer

    When the main goal is separating code paths and query shapes, the write rate is modest, and consistent reads matter more than independent scaling.

Sources and further study

Primary

Systems docs

Books

Other curricula

Watch

Free resources

Open in the interactive app: practice, quiz, mock interview →