Skip to content

Path 7 · lesson 5 of 8

Advanced30 minApache Kafka 4.x (configuration and operations pages read for 4.1) · Spring for Apache Kafka 3.3 on Spring Boot 3.x · Java 21 · PostgreSQL

CQRS: read models built from events

Keep one model for changing orders and separate query tables built by Kafka consumers. Learn what the lag costs, how to rebuild a projection, and when the pattern is not worth it.

What you will learn

  • Separate the model that accepts order commands from the tables that answer order queries, and say what each side owns
  • Build a projection as a Kafka consumer that upserts a query table idempotently and ignores stale events with a version check
  • Choose a read-your-own-writes tactic for a screen that must show the user's own change immediately
  • Rebuild a projection by replaying a topic from the earliest offset with a new group id, and state what retention or compaction that requires
  • Recognise the cases where a single model, a database view or a read replica is the better answer

Before you start

The concept

CQRS (Command Query Responsibility Segregation) means using one model to change data and a different model, or several, to read it. A command asks the system to do something: place an order, cancel it. A query asks what is true and changes nothing. In most applications both go through the same tables. CQRS splits them.

In the order system the Order Service owns the write model: normalised orders and order_line tables with the constraints and rules that decide whether a command is allowed. Every accepted change is published as an event on orders.events. Separate consumers, called projections, read those events and maintain read models: tables shaped for one screen or one query, such as an order_summary row per order that already contains the customer name, the item count and the payment and shipping status.

  • Write model: validates commands and stores the authoritative state. It is the only place a business rule is enforced.
  • Events: the facts the write model publishes after it commits, here through a transactional outbox.
  • Projection: a Kafka consumer group that turns events into rows of one read model. It contains no business rules, only mapping.
  • Read model: a query table (or a search index, or a cache) that can be thrown away and rebuilt from the events.

Advantages

  • Each query gets a table shaped for it: no joins across five tables on every page view
  • Reads and writes scale separately, and a heavy report cannot slow down checkout
  • A new read model can be added later by replaying the events, without touching the write side
  • The write model stays small because it no longer carries columns that exist only for screens
  • A read model can live in a different store: a search index, a cache, a warehouse

Disadvantages

  • Read models are eventually consistent: a query can return the state from before the user's last command
  • Every projection is a consumer to run, monitor for lag and rebuild after a bug
  • Events become a contract: a careless change breaks projections owned by other teams
  • Duplicates and replays must be absorbed by idempotent upserts and version checks
  • A rebuild needs the history to still exist: retention or compaction has to be planned
  • More moving parts for every feature, which is wasted on a simple CRUD domain

Why it exists

Start with one model. The Order Service has orders, order_line, and references to customers, payments and shipments that live in other services. The "My orders" page needs, for each order, the customer name, the number of items, the total, whether it was paid and whether it shipped. There are three ways to produce that page from a single model, and each one fails in its own way as the system grows.

ApproachWhat happens under loadWhat it does to the design
Join everything at query timeThe page that is read thousands of times a second runs the most expensive query in the system, on the same database that takes ordersIndexes added for the page slow down every insert. A reporting query can hold locks and connections that checkout needs.
Call Payment and Inventory over HTTP for each orderOne slow service makes the page slow, and one unavailable service makes it failThe read path is now coupled to the availability of every service it asks.
Add display columns to the orders tableWorks for a whileThe write model fills with denormalised columns that other services' events must update, and the rules about who may write which column get lost.

The underlying problem is that the two sides want opposite things. Commands want a normalised model with constraints, small transactions and few indexes. Queries want wide, pre-joined rows and many indexes, and there are usually far more queries than commands. One schema serves both badly.

Kafka makes the split cheap to implement because the events already exist. The order, payment and inventory services publish what happened so that the other services can react. A projection is one more consumer group on those topics. It needs no change in the producing services, it keeps its own offsets, and it can be stopped, fixed and replayed without affecting anyone else.

How it works inside

A projection is a consumer with an upsert

The projection reads orders.events in its own consumer group, maps each event to the columns of order_summary and writes the row in a local database transaction. The container commits the offset after the method returns. That is at-least-once consumption, so the same event can arrive twice: after a crash between the database commit and the offset commit, after a rebalance, after an outbox relay republished it, or because someone reset the group's offsets. The write therefore has to give the same result however many times it runs.

An upsert by primary key (INSERT ... ON CONFLICT (order_id) DO UPDATE) is idempotent when the event carries the complete new values. A projection that increments a counter is not, and needs a processed-event table as in the idempotent consumer pattern.

Why the version check is needed as well

Idempotence protects against applying the same event twice. It does not protect against applying an older event after a newer one. Kafka keeps the order of one order's events within a partition when they share a key, but a replay of a dead letter record, a retry topic, or a change in the partition count can deliver version 3 after version 5. So the write model stamps each event with the aggregate's version, a counter it increments on every change, and the projection only applies an event whose version is higher than the stored one: WHERE order_summary.version < EXCLUDED.version. An older or equal version updates zero rows and is skipped.

Eventual consistency, measured

The read model lags the write model by the outbox relay's delay plus the projection's consumer lag plus its own write. In a healthy system this is tens to hundreds of milliseconds. During a rebalance, a deployment or a slow database it can be many seconds. The lag is visible: it is the consumer lag of the projection's group, and the difference between the event's timestamp and the time the row was written.

Read your own writes

The lag matters most right after a user's own command: they place an order, the app shows "My orders", and the new order is missing. Other users never notice a 200 ms delay. The person who just clicked does. The usual tactics:

TacticHow it worksCost
Return the result from the commandThe command response contains the new order and its version. The client shows that, and does not query the read model for it.Works for the screen right after the command only
Version tokenThe client sends the version it got from the command with its next query (minVersion). The query side answers only if the read model has reached it, and otherwise tells the client to retry shortly.A short wait or a retry in the client. Needs the version in the read model, which the projection already stores.
Read the write model for one entityThe order detail page of the order's owner is served from the write database by primary key. Lists and searches use the read model.A second read path to maintain. Acceptable only for cheap primary-key reads.
Optimistic UIThe client adds the new order to the list locally and replaces it when the read model catches up.Client complexity, and a correction if the command later fails

Rebuilding a projection

A read model is derived data. When its mapping has a bug, or a new column is needed, the fix is to build it again from the events. The safe procedure builds the new version next to the old one:

  1. Create a new table (order_summary_v2) and deploy the corrected projection with a new group id (order-summary-projection-v2) and auto.offset.reset=earliest. A group with no committed offsets starts where that setting says, and its default is latest, which would skip the whole history.
  2. Let it consume from the earliest offset until its lag reaches the same level as the live projection.
  3. Switch the query API to the new table, then stop the old projection and drop the old table and group.

Resetting the offsets of the existing group with kafka-consumer-groups.sh --reset-offsets --to-earliest also replays, but the tool requires the group's consumers to be stopped, and queries see a half-built table while it runs. Use it for small projections only.

"From offset 0" is only as complete as the topic. With the default cleanup.policy=delete and retention.ms of 7 days, a replay yields the last week of events, and orders older than that are missing from the rebuilt table. There are three ways to make a rebuild complete:

  • Keep everything: retention.ms=-1 on the event topic. Simple, with disk growing for ever, and a replay that takes longer each month.
  • Compact: cleanup.policy=compact with the order id as key keeps at least the latest event per order. That is enough for a state-carrying event, and it is wrong for delta events, because the earlier ones are removed.
  • Snapshot plus tail: load the read model from an export of the write database, then start the projection from an offset taken before the export. The version check makes the overlap harmless.

Failure scenarios

Crash pointResulting stateRecovery
Order Service, after its commit, before the event is publishedWrite model has the order, read model does notThe outbox relay publishes the row on its next poll. The read model catches up. Nothing is lost.
Projection, before its database commitNo change in the read model, offset not committedThe event is redelivered and applied normally.
Projection, after its database commit, before the offset commitRow updated, offset not advancedThe event is redelivered. The version is not higher than the stored one, so the upsert changes zero rows.
Read database unavailableProjection fails every record and its lag grows. Commands keep working.Queries fail or serve stale data from a cache. When the database returns, the projection drains the backlog.
An event the projection cannot mapThe record fails on every attemptThe error handler sends it to a dead letter topic after its retries, so the partition is not blocked. That one order is stale until the record is fixed and replayed.
A bug wrote wrong values for a weekRead model is wrong, write model and events are rightRebuild into a new table with a new group id, as above. This is the failure CQRS recovers from best.
Projection stopped for longer than the topic's retentionEvents were deleted before they were readOn restart the committed offset no longer exists and auto.offset.reset decides where to continue. The gap can only be filled from a snapshot of the write model.
Crash or fault at each point, what is left behind, and how the system recovers.

Architecture

Drawing diagram…

Flowchart: the client sends commands to the Order Service, which writes to its database and outbox. The outbox relay publishes to the orders.events topic keyed by order id. Two projection consumer groups and the payment service read the topic. One projection upserts the order_summary table in a read database, the other feeds a search index. The client's queries go to a separate query API that reads those stores.

flowchart LR
  UI["Client"] -->|"commands, POST and DELETE"| OS["Order Service, write model"]
  OS --> WDB["Write database, orders and outbox"]
  WDB -->|"outbox relay"| T["Topic orders.events, key orderId"]
  T --> P1["Group order-summary-projection"]
  T --> P2["Group order-search-projection"]
  T --> PAY["Group payment-service"]
  P1 -->|"upsert if version is newer"| RDB["Read database, order_summary"]
  P2 --> IDX["Search index"]
  UI -->|"queries, GET"| QS["Order Query API"]
  QS --> RDB
  QS --> IDX
Commands go to the write model. Queries go to tables that projections keep up to date from the event topic.
  • The two arrows leaving the client are the pattern: commands take the upper path into the write model, queries take the lower path into the read models. No query touches the write database.
  • The topic is the only connection between the sides. The write side does not know which projections exist, so adding the search projection needed no change in the Order Service.
  • Each projection is its own consumer group with its own offsets. One can be stopped, replayed or far behind without affecting the others or the Payment Service.
  • The read database can be a different instance or a different technology. If it is lost, it is rebuilt from the topic, provided the topic still holds the history.

Message flow

Drawing diagram…

Sequence diagram: the client posts an order and receives the order id with version 1. It immediately queries the summary with minimum version 1, the query API finds no row and answers that the change is not visible yet. The event is then published, the projection upserts the summary row and commits its offset. The client's retry returns the summary. A redelivered copy of the same event changes zero rows.

sequenceDiagram
  participant C as Client
  participant O as Order Service
  participant K as Kafka orders.events
  participant P as Summary projection
  participant R as Read DB
  participant Q as Query API
  C->>O: POST /orders
  O->>O: commit order version 1 and outbox row
  O-->>C: 201 orderId o-1001 version 1
  C->>Q: GET summary o-1001 minVersion 1
  Q->>R: SELECT by order id
  R-->>Q: no row yet
  Q-->>C: 409 not yet visible, Retry-After 1
  O->>K: OrderCreated o-1001 version 1
  K->>P: OrderCreated o-1001 version 1
  P->>R: upsert order_summary where stored version is lower
  P->>K: commit offset
  C->>Q: GET summary o-1001 minVersion 1
  Q->>R: SELECT by order id
  R-->>Q: row at version 1
  Q-->>C: 200 summary
  K->>P: OrderCreated o-1001 version 1 redelivered
  P->>R: upsert changes 0 rows
The client's first query arrives before the projection has applied the event. The version token turns a wrong answer into a short retry.
  1. The command returns before the read model knows about the order. That gap always exists; the design decides how the client experiences it.
  2. With minVersion, the query side can tell the difference between "this order does not exist" and "this order is not visible yet", and the client retries instead of showing an empty list.
  3. The last two messages are a duplicate delivery. The version check makes it a no-op without a separate dedupe table.

In a real system

A typical order platform: the Order Service owns the write model in PostgreSQL and publishes to orders.events, 12 partitions, replication factor 3, key orderId, through an outbox. The topic uses cleanup.policy=compact because each event carries the order's full current state, so a rebuild needs only the latest event per order. The "My orders" page and the support console read order_summary in a separate read database.

Read modelConsumer groupFed fromStoreServes
order_summaryorder-summary-projectionorders.eventsPostgreSQL read databaseMy orders, order status page
Order searchorder-search-projectionorders.eventsA search indexSupport console search by name, email, SKU
Daily revenuerevenue-projectionpayments.eventsWarehouse tableFinance dashboard

The summary projection runs three instances with listener concurrency 4, so 12 consumer threads for 12 partitions. More threads than partitions would sit idle. Because the key is the order id, all events of one order are handled by one thread in order, and different orders are processed in parallel.

When to use it

  • Reads vastly outnumber writes and need shapes the write model cannot serve cheaply: lists that join several aggregates, full-text search, dashboards.
  • The data for one screen is owned by several services, and calling them all at query time would couple the page to their availability.
  • The events already exist because other services react to them. A projection is then one more consumer.
  • The business accepts that a list may be a moment behind, and the few places that cannot accept it have a read-your-own-writes tactic.
  • One part of the system has this problem. Apply the pattern to that part, not to every service.

When it is over-engineering

  • A CRUD service where the screens show the same rows the forms edit. One model is simpler and always consistent.
  • The read problem is only load. A database read replica or a cache gives read scaling without a second model.
  • The read problem is only shape, inside one database. A view or a materialized view is maintained by the database, with no consumer to operate.
  • The team cannot yet operate consumers well: no lag alerting, no dead letter handling, no practised replay. A projection that silently falls behind is worse than a slow join.
  • Users must always see their change in every list immediately, and no tactic above is acceptable. Then the read has to come from the write model.

Java and Spring Boot

The query side of the order system: the read model schema, a projection consumer with an idempotent, version-checked upsert, and a query endpoint that supports a version token. The write side is the Order Service with the outbox from the first lesson of this path. Its events carry the order's current state and a version.

order-query/src/main/resources/db/migration/V1__order_summary.sqlSQL
-- Read database of the order query service. Nothing here is authoritative:-- every row can be rebuilt from the orders.events topic.CREATE TABLE order_summary (    order_id      varchar(36)  PRIMARY KEY,    customer_id   varchar(36)  NOT NULL,    customer_name varchar(200) NOT NULL,    item_count    integer      NOT NULL,    total_cents   bigint       NOT NULL,    status        varchar(16)  NOT NULL,        -- PENDING, PAID, SHIPPED, CANCELLED    version       bigint       NOT NULL,        -- version of the order aggregate this row reflects    occurred_at   timestamptz  NOT NULL,        -- when the write model made the change    projected_at  timestamptz  NOT NULL DEFAULT now());-- The query this table exists for: one customer's orders, newest first.CREATE INDEX order_summary_by_customer ON order_summary (customer_id, occurred_at DESC);-- Transaction of the projection, one per event:--   BEGIN;--   INSERT INTO order_summary (...) VALUES (...)--   ON CONFLICT (order_id) DO UPDATE SET ... , version = EXCLUDED.version--   WHERE order_summary.version < EXCLUDED.version;      -- 1 row: applied. 0 rows: duplicate or stale.--   COMMIT;-- The Kafka offset is committed by the listener container after this commit.-- Queries served:--   SELECT ... FROM order_summary WHERE order_id = ?;--   SELECT ... FROM order_summary WHERE customer_id = ? ORDER BY occurred_at DESC LIMIT 20;-- Projection lag as the read side sees it (seconds between the change and its projection):--   SELECT max(extract(epoch FROM projected_at - occurred_at)) FROM order_summary--   WHERE projected_at > now() - interval '5 minutes';-- Rebuild: create order_summary_v2 with the same shape, run the projection with a new-- group id from the earliest offset, switch the queries, then DROP TABLE order_summary.

Why it is written this way

  • The table is denormalised on purpose: customer_name and item_count are copied in so the list query is a single index scan with no join.
  • version does two jobs. It makes the upsert reject stale events, and it lets the query API answer whether the read model has caught up with a given command.
  • The WHERE clause on DO UPDATE is what turns a duplicate or an out-of-order event into zero affected rows. Without it the upsert is idempotent but an old event could overwrite newer state.
  • projected_at - occurred_at gives the end-to-end delay as users experience it, which consumer lag in records does not.

Configuration

Settings that decide where a projection starts, how far back it can replay and how long its position is remembered. Defaults are from the Kafka 4.1 consumer, topic and broker configuration references.

auto.offset.resetDefault latest
What it does
Where a consumer starts when its group has no committed offset for a partition, or the committed offset no longer exists on the broker. earliest, latest, by_duration:<duration> or none.
When to change it
Set earliest for every projection. With the default, a new or rebuilt projection starts at the end of the topic and silently misses all existing orders.
group.idDefault none
What it does
The consumer group whose committed offsets define the projection's position. One read model, one group.
When to change it
Put a version in the name (order-summary-projection-v2). Changing it is how a rebuild starts from the beginning without touching the live projection.
cleanup.policy (topic)Default delete
What it does
delete discards old segments by time or size. compact keeps at least the latest record per key. Both can be combined.
When to change it
Use compact on an event topic only if every event carries the full state of its key. For delta events keep delete and decide retention explicitly.
retention.ms (topic)Default 604800000 (7 days)
What it does
How long a log is kept before old segments are discarded under the delete policy. -1 means no time limit.
When to change it
A rebuild from the topic can only reach back this far. Raise it, set -1, or plan a snapshot from the write database for anything older.
delete.retention.ms (topic)Default 86400000 (1 day)
What it does
How long tombstones are kept on a compacted topic. It also bounds how long a reader starting from offset 0 may take and still see every delete.
When to change it
If a full rebuild takes longer than a day, raise this, or the rebuilt table can contain orders that were deleted.
offsets.retention.minutes (broker)Default 10080
What it does
How long committed offsets are kept after a group has lost all its members (7 days).
When to change it
A projection that stays stopped longer than this loses its position and falls back to auto.offset.reset. With earliest that means a full, harmless replay.
max.poll.recordsDefault 500
What it does
Maximum records returned by one poll.
When to change it
During a rebuild the projection writes one transaction per record. If a batch of 500 cannot finish within max.poll.interval.ms (default 5 minutes), lower this before raising the interval.

Hands-on exercise

Can this topic rebuild your read model?

Use the log explorer to see what a projection that starts from the earliest offset actually receives under time-based retention and under compaction, and decide which policy the order events topic needs.

Steps · 0 of 5 done

What a replay from the earliest offset can still read

Simulation · not a real cluster

A partition of order events keyed by order id. Compare what is left after retention deletes a segment with what is left after compaction.

What can go wrong

A rebuilt projection that starts at the end
Symptom
After deploying a projection with a new group id, the new table contains only orders placed since the deployment.
Cause
The new group had no committed offsets and the consumer used the default auto.offset.reset=latest, so it began at the end of every partition.
Fix
Set auto.offset.reset=earliest on projection listeners, and check the group's starting offsets with kafka-consumer-groups.sh --describe before switching queries to the new table.
Rebuilding from a topic that has already forgotten
Symptom
The rebuilt table has every recent order and none older than a week.
Cause
The topic uses the delete policy with 7 days of retention. Replaying from the earliest offset replays what is left, which is not the full history.
Fix
Decide the rebuild strategy when the topic is created: compaction with full-state events, unlimited retention, or a documented snapshot-plus-tail procedure.
An upsert with no version check
Symptom
An order shows as PAID, then goes back to PENDING after a dead letter replay or an offset reset.
Cause
The projection overwrites the row with whatever event arrives. A replayed older event replaced newer state.
Fix
Carry an aggregate version in every event and apply an event only when its version is higher than the stored one.
Counters in a projection
Symptom
A customer's "orders placed" count in the read model is higher than the number of their orders, and grows after every restart.
Cause
The projection increments on each OrderCreated. Redelivered events are counted again, because an increment is not idempotent.
Fix
Derive counts from idempotent rows (SELECT count(*) over the summary table or a materialized aggregate), or record processed event ids in the same transaction as the increment.
The user's own change is missing
Symptom
Support tickets say "I placed an order and it is not in my orders". It appears on refresh.
Cause
The app navigates to a list served by the read model immediately after the command, inside the projection's lag.
Fix
Show the result returned by the command, or send a version token with the next query. Do not hide the problem with a fixed client-side sleep.
Business rules that read the read model
Symptom
Two orders are accepted for the last unit in stock, or a cancelled order is cancelled again.
Cause
A command handler validated against a projection, which was behind. Read models are for display, not for decisions that must be correct.
Fix
Validate commands against the write model inside its transaction. Use the read model only where a stale answer is acceptable.
CQRS applied everywhere
Symptom
A simple settings screen needs an event, a topic, a projection and a rebuild plan, and shows stale values after saving.
Cause
The pattern was adopted as an architecture for the whole system instead of for the queries that needed it.
Fix
Keep one model for CRUD parts. Introduce a read model for a specific query when a join, a replica or a view has been shown not to be enough.

How to troubleshoot

Queries return stale data for everyone

Check

kafka-consumer-groups.sh --bootstrap-server broker:9092 --describe --group order-summary-projection
How to read it
Compare CURRENT-OFFSET with LOG-END-OFFSET per partition. Growing LAG on all partitions means the projection is slow or stopped. LAG stuck on one partition points to one record that keeps failing. No lag at all means the events are not reaching the topic, so look at the outbox relay.
One order is wrong in the read model, the rest are right

Check

SELECT version, status, occurred_at, projected_at FROM order_summary WHERE order_id = 'o-1001'; then compare with the version in the write database
How to read it
A lower version in the read model means an event has not arrived or failed: check the dead letter topic for that key. An equal version with different values means the mapping is wrong, which calls for a fix and a rebuild.
Not sure a rebuild started from the beginning

Check

kafka-consumer-groups.sh --bootstrap-server broker:9092 --describe --group order-summary-projection-v2
How to read it
Shortly after start, CURRENT-OFFSET should be small and LAG close to the size of the log. A CURRENT-OFFSET already near LOG-END-OFFSET means the group started at latest.
Replay needed on an existing group

Check

kafka-consumer-groups.sh --bootstrap-server broker:9092 --group order-summary-projection --topic orders.events --reset-offsets --to-earliest --execute
How to read it
The tool requires the group's consumers to be inactive and fails otherwise. Without --execute it only prints the offsets it would set, which is a useful dry run.
How far back can a replay go?

Check

kafka-configs.sh --bootstrap-server broker:9092 --entity-type topics --entity-name orders.events --describe
How to read it
Look at cleanup.policy and retention.ms among the overrides. If neither is listed, the topic uses the broker defaults: delete after 7 days unless the broker was configured otherwise.

Key takeaways

  • CQRS is one model for commands and separate models for queries. Kafka projections are one way to build the query side.
  • A projection is a consumer group that upserts a query table. It must be idempotent and must reject stale events with a version from the write model.
  • Read models are eventually consistent. Decide how the user's own write becomes visible: command result, version token, or a read from the write model.
  • A read model is disposable: rebuild it into a new table with a new group id and auto.offset.reset=earliest.
  • A rebuild is only as complete as the topic. Plan compaction with full-state events, long retention, or a snapshot.
  • Never validate a command against a read model.
  • Most parts of most systems do not need this. Use it for the queries that a single model cannot serve.

Practical check

Small scenarios, one decision each. Pick what you would do, then read why.

Scenario 1 of 3

A team fixes a mapping bug in the order summary projection. They deploy the fix with group id order-summary-projection-v2 writing to order_summary_v2. An hour later the group shows lag 0 on all 12 partitions, but the new table has 4,100 rows while the old one has 2.3 million. orders.events is compacted and holds every order.

What most likely happened?

Scenario 2 of 3

After checkout the mobile app opens "My orders", served from the read model. About 3% of users do not see the order they just placed. The summary projection's lag is normally under 300 ms. A developer proposes having the Order Service write the order_summary row itself, in the checkout transaction, in the read database.

Which change fixes the user's problem without undoing the design?

Scenario 3 of 3

An internal admin tool manages 40 shipping carriers: name, contact email, active flag. Five people edit it a few times a week. A proposal adds a carriers.events topic, a projection into a carrier_view table and a separate query service, "to be consistent with the order platform".

What should the team build?

References

References: Apache Kafka 4.1: consumer configuration reference (auto.offset.reset, max.poll.records, max.poll.interval.ms) ↗ · Apache Kafka 4.1: topic configuration reference (cleanup.policy, retention.ms, delete.retention.ms) ↗ · Apache Kafka 4.1: broker configuration reference (offsets.retention.minutes) ↗ · Apache Kafka 4.1: basic operations, managing consumer groups and resetting offsets ↗ · Spring for Apache Kafka 3.3 reference: @KafkaListener annotation (groupId, properties) ↗ · PostgreSQL documentation: INSERT, the ON CONFLICT clause ↗ · Martin Fowler: CQRS ↗ · Microsoft Azure Architecture Center: CQRS pattern ↗ · Report a problem