CQRS and event sourcing: add read models first, store events only when history is the product Advanced

The problem

An order service keeps orders and order_items. Three readers hit them: the operations dashboard (orders per status per hour), support search and the customer’s “my orders”. Each needs a different shape, so queries join five tables and compete with checkout.

Audit keeps asking for more: who cancelled order 101, and what the total was before the coupon. An UPDATE overwrites the answer.

Two patterns get proposed together. When do separate read models fix the slowness, and when does storing events instead of state pay for itself?

Which approach? (summary first)

Situation	Choose	Condition
Reads slow, but one relational shape still fits	CRUD: indexes, a materialized view, a read replica	Fix the query before adding a model
Dashboards and search need other shapes or stores	CQRS read models fed by outbox or CDC	Screens tolerate seconds of lag; projector is idempotent
Audit means “show who changed what”	CRUD + history table written in the same transaction	History is a record, not the source of truth
Rules depend on history; new questions about past behaviour keep arriving; ledger-like data	Event sourcing in that bounded context only	Team owns schema evolution, erasure and projection rebuilds

Core concepts

CQRS (Command Query Responsibility Segregation) uses one model to change data and another to read it. Greg Young framed it as splitting one object into two: one for commands, one for queries. Nothing in that definition requires events, a broker or a second database.

Event sourcing stores every change as an immutable event in an append-only stream per aggregate. Current state is a fold: start from an empty order, apply OrderPlaced, ItemAdded, OrderPaid in version order. The event log is the source of truth; tables are derived.

	CRUD	CQRS without ES	Event sourcing (+ CQRS)
Source of truth	Current rows	Current rows	Event streams
Read models	Same tables	Projections from outbox/CDC	Projections from the event log
History	Overwritten unless copied	Overwritten unless copied	Complete by construction
Concurrency	Row locks or a version column	Same as CRUD	Expected version per stream

Fowler warns that for most systems CQRS “adds risky complexity”. Azure calls event sourcing costly to migrate to or from: “for most systems and most parts of a system, traditional data management is sufficient”.

Loading diagram…

With CQRS alone, the write side keeps orders and records a domain event in the outbox inside the same transaction, avoiding a dual write. With event sourcing, the event is the write; there is no row to keep in sync.

How it works

The demo (examples/src/cqrs-event-sourcing/) is an event store in one Postgres 17 table; the constraint is the key line:

CREATE TABLE es_events (
  global_position bigserial PRIMARY KEY,
  stream_id       text  NOT NULL,          -- 'order-101'
  version         int   NOT NULL,          -- 1, 2, 3... per stream
  data            jsonb NOT NULL,          -- plus type, schema_version
  tx_id           xid8  NOT NULL DEFAULT pg_current_xact_id(),
  CONSTRAINT es_events_stream_version_key UNIQUE (stream_id, version)
);

Append = read max(version), compare with the version the command loaded (expected version), insert expected + 1: optimistic locking per stream, as in Greg Young’s 2010 relational design.

PayOrder appends version 4 at once; the read model shows it only after the projector runs

Enlarge diagram

Web app → Command handlerPayOrder

◀ Prev▶ PlayNext ▶↺ RestartStep 1/6

The customer pays order-101. The command names the intent, not a column update.

Step	Write side (es_events)	Read side (es_order_summary)
1–2	version 3, folded as placed	version 3
3–4	OrderPaid = version 4	version 3: stale for minVersion=4
5–6	unchanged	v4 and checkpoint applied atomically: paid

Two writers, one version

The max(version) check alone races: two sessions both read 3. The unique index settles it.

The second INSERT of version 4 waits on the unique index, then fails with 23505

◀ Prev▶ PlayNext ▶↺ RestartStep 1/6

Both sessions load order-101 at version 3, status placed. No lock is held.

1. Both sessions fold order-101 at version 3.
2. web-checkout inserts version 4, uncommitted.
3. support-backoffice inserts version 4 and waits (Lock: transactionid).
4. web-checkout commits; support gets 23505 → ConcurrencyError.
5. Support reloads and re-runs the rule: paid orders are refunded, not cancelled.

Operational details

• Snapshots cache the folded state every N events (3 in the demo); load = snapshot + later events. When apply() changes, delete and regenerate them.
• Projection order: global_position is assigned at insert but visible at commit, so tailing global_position > checkpoint can skip an event forever (output below).
• The demo projector reads only tx_id < pg_snapshot_xmin(pg_current_snapshot()), ordered by (tx_id, global_position). Every transaction below xmin has finished, so nothing behind the checkpoint can still appear. Cost: any long transaction on the server, even in another database, stalls projections.
• Checkpoint and read-model rows commit together; a crash replays the batch harmlessly. Projectors fed through a broker need an idempotent consumer.
• Rebuild = truncate the read model, delete the checkpoint, replay. Time it on production-sized data.
• Kafka is not the event store: it has no per-stream expected-version check. Use it to fan events out.

Read-your-writes on an eventually consistent read side

Users notice lag right after their own action. Options, cheapest first:

1. Return the new version; the client renders its own result (optimistic UI).
2. Query with minVersion: wait briefly for the projection, else read the write side.
3. Fold the user’s own aggregate from its stream; lists and search stay on projections.
4. Update one critical view inside the append transaction, paying in write latency.

Schema evolution and erasure

Stored events never change, so old shapes live forever. The demo’s ItemAdded v1 stored price: "12.50"; an upcaster turns it into v2 priceMinor: 1250 on read. Consumer compatibility rules: event schema evolution.

Erasure under GDPR Article 17 collides with immutability. Azure lists two approaches: keep personal data outside events and reference it by ID, or crypto-shred: encrypt fields with a per-subject key and delete the key.

Crypto-shredding makes ciphertext unreadable; it does not remove it. Whether that satisfies erasure is a legal decision. Plaintext in read models, caches, logs and backups needs its own purge.

Real-world example

Read models for search, event sourcing only for the payments ledger

Search and the dashboard become projections fed by the existing outbox; checkout keeps orders. Order history polls with minVersion after “Cancel”.

Audit then asks for “refunds as known on each day”, a temporal query. Only the payments ledger becomes event-sourced, and its events carry customerId, never the email.

Run the demo (Postgres 17.11 from examples/docker-compose.yml, port 5433):

docker compose -f examples/docker-compose.yml up -d postgres
npx --prefix examples ts-node examples/src/cqrs-event-sourcing/demo.ts
npx --prefix examples ts-node examples/src/cqrs-event-sourcing/gap-demo.ts

Real output, trimmed (ciphertext varies per run):

== 3. Concurrency: customer pays while support cancels, both loaded at version 3 ==
support session while web is uncommitted: {"wait_event_type":"Lock","wait_event":"transactionid","state":"active"}
web-checkout: COMMIT version 4 (OrderPaid)
support-backoffice: ConcurrencyError: order-101: duplicate key value violates unique constraint "es_events_stream_version_key" (Key (stream_id, version)=(order-101, 4) already exists.)
retry after reload at version 4: cannot cancel an order in status paid; start a refund instead

== 4. Snapshot + read-your-writes ==
command returned version 5; read model has: status=placed items=3 total_minor=22400 email=lan@example.com version=3
read model caught up after 1 projector pass(es): status=shipped items=3 total_minor=22400 email=lan@example.com version=5

== 6. Erasure request for cust-7: crypto-shred, then rebuild the projection ==
rebuild replayed 5 events -> status=shipped items=3 total_minor=22400 email=[erased] version=5
event 1 still holds ciphertext: QzOdz/ffJ+GQIKUQ...

gap-demo.ts: why tailing by global_position skips events

naive reader sees positions: [ 2 ] -> checkpoint = 2
xmin-guarded reader sees positions: [] (waits for the open transaction)
after slow COMMIT, naive "> 2" sees: [] -> position 1 is skipped forever
xmin-guarded reader now sees (tx order): [ 1, 2 ]

Position 1 belonged to a transaction that committed after position 2.

Missing for production: command deduplication, lag metrics, a real KMS instead of a key table.

Trade-offs

Event sourcing gives you

• Full history and intent: audit, temporal queries, debugging by replay.
• New read models from past events without touching the write side.
• Per-stream concurrency with an explicit expected version.

Event sourcing costs you

• Every stored event shape is supported forever.
• No ad-hoc SQL on current state; projections lag and must be rebuildable.
• Erasure and bad-data fixes need designed workarounds.
• Costly to adopt and to abandon.

CQRS without event sourcing costs one projector, lag monitoring and idempotency, and keeps SQL on the source of truth.

Production checklist

1. List which screen needs which shape and how much staleness it tolerates.
2. Start with CQRS on existing tables; adopt event sourcing per bounded context where history drives decisions.
3. Enforce UNIQUE (stream_id, version) and map its violation to a retryable conflict that reloads and re-decides.
4. Tail projections in commit-safe order, store checkpoints with read-model rows, alert on projection lag in seconds.
5. Version every event type; test upcasters against fixtures of each stored version.
6. Decide where personal data lives before the first event; get legal sign-off on crypto-shredding.
7. Rehearse and time a full projection rebuild on production-sized data.

Common mistakes

Retrying a conflict by re-sending the same events

✕ Common

catch (e) {
  if (e instanceof ConcurrencyError) await append(stream, actualVersion, sameEvents);
}

✓ Prefer

catch (e) {
  if (e instanceof ConcurrencyError) return handle(command); // reload, fold, check rules again
}

Why

The decision used stale state; the rule must run again.

A second trap is treating the event log as a change log of columns.

Common pitfall

OrderStatusChanged { status: "cancelled" } everywhere. State-shaped events lose intent, so projections cannot tell a customer cancellation from fraud. Azure recommends intent events: OrderCancelledByCustomer { reason }.

Misunderstanding	Consequence	Do instead
“CQRS means event sourcing”	Event store adopted only to speed up reads	Projections from the outbox first
“Audit requires event sourcing”	A whole service rewritten for a history table	History table or CDC to an audit log
“Kafka topic = event store”	Two writers append conflicting events	Event store with expected version; Kafka to fan out

Self-check

1. Why does the max(version) check need the unique constraint behind it?
2. A projector tails global_position > checkpoint. Which commit order loses an event?
3. After a ConcurrencyError on CancelOrder, why can the retry end without appending anything?

Summary

Key takeaways

• CQRS separates read and write models; event sourcing makes the event log the source of truth. They are separate decisions.
• Slow reads usually need projections fed by outbox or CDC, not an event store.
• Event sourcing pays off where history drives decisions; apply it per bounded context.
• An event store needs expected-version appends, commit-safe projection order and a timed rebuild; plan upcasting and erasure before the first event.

References

• Azure — CQRS pattern: single vs separate stores, syncing via outbox, when not to use.
• Azure — Event Sourcing pattern: concurrency, snapshots, upcasting, crypto-shredding, brokers vs event stores.
• Martin Fowler — CQRS: why most systems should stay CRUD.
• Martin Fowler — Event Sourcing: rebuild, temporal query, replay with external gateways.
• Greg Young — CQRS Documents: the original definition, relational event storage, rolling snapshots.
• PostgreSQL 17 — System Information Functions: pg_current_xact_id, snapshot xmin semantics.
• PostgreSQL 17 — Sequence functions: sequences have gaps; gap-demo.ts shows they are not commit-ordered either.
• GDPR Article 17: the right to erasure.