CQRS and materialized views

CQRS (command query responsibility segregation) splits a system’s data into two sides. Commands change a write model, shaped to enforce the rules. Queries read read models, each shaped for one screen or one question. The read models are built from the write model’s changes, and kept in step by a stream of events. A read model that is computed and stored ahead of the query is a materialized view.

The hard part is the stream between the two sides. Views run behind. Events arrive twice, or out of order. Views drift. And users save something, and then do not see it.

The problem it solves

An order service runs on one Postgres primary. It takes 300 writes a second: orders placed, paid, shipped, cancelled. The schema is normalized for the write side: orders, order_items, payments and shipments, with constraints that stop an order being shipped before it is paid. That shape is right for writes. It is wrong for nearly every read:

Read	Rate	What it needs	Cost on the write schema
“My orders” page	20,000/s	One customer’s orders, newest first, with item names and status	A 4-table join, 20 to 40 ms, on the primary or a replica
Seller dashboard	50/s	Revenue per day per status for the last 90 days	An aggregate over about 2 million rows: 2 to 5 s per query
Product search	3,000/s	Free text, typo tolerance, facets by brand and price	Not possible with B-tree indexes; LIKE '%term%' scans the table
Support console	20/s	Every event for one order, with who did what	Several tables stitched together by timestamp

Each read can be helped with an index. But every index slows every write, and the shapes conflict. The dashboard wants rows grouped by seller and day. The “my orders” page wants them by customer. And search wants an inverted index the database does not have. Read replicas copy the same schema. So they add capacity, but no new shapes.

CQRS gives each read its own store, in the shape that read wants, answered in one lookup. It feeds them all from the write side’s changes. The write model stays the strongly consistent source of truth. Every read model is eventually consistent, behind by the pipeline’s lag. The name comes from Bertrand Meyer’s command-query separation, applied to whole systems by Greg Young around 2010.

How it works

Step 1: Denormalize inside the same transaction

The cheapest read model is a column or table updated in the same transaction as the write. An order_count and last_order_at on the customer row, or an order_summaries table with one row per order holding exactly what the “my orders” page shows. The write path updates it in the same BEGIN ... COMMIT, by application code or a trigger.

It has no lag. It costs write latency, write amplification, and coupling. The order service owns every read shape. And the summary shares the CPU and disks of the writes it was meant to relieve. A counter that every order updates (sales per seller per day) also becomes a hot row that serializes writes. The payment system’s ledger runs into this with its balance rows.

Step 2: Materialized views the database maintains

Most databases can store a query’s result, and refresh it. In Postgres, CREATE MATERIALIZED VIEW seller_daily AS SELECT ... runs the query once, and stores the rows. REFRESH MATERIALIZED VIEW CONCURRENTLY seller_daily recomputes it without blocking readers. (It needs a unique index on the view.) A cron job refreshes it every 5 minutes. And the dashboard reads a few hundred rows, instead of aggregating 2 million.

Postgres recomputes the whole query on every refresh. So the cost grows with the data, not with the changes. A refresh that took 4 seconds at launch takes 4 minutes two years later. And staleness is the refresh interval plus the refresh time.

Go deeperViews the database maintains incrementally1 min

Some engines apply each change to the view, instead of recomputing it. Examples are Oracle’s fast refresh with materialized view logs, SQL Server’s indexed views, and ClickHouse’s materialized views (which run on each inserted block). So do streaming databases, such as Materialize and RisingWave. Incremental maintenance limits which queries are allowed: joins and aggregates, with restrictions. And it still runs on the database it reads from. So it removes the refresh cost, but not the shared resources.

TradeoffA materialized view in the source database

We gain

No pipeline to run, SQL for the view definition, and one place to look. For a dashboard that can be minutes stale, it is often the whole answer.

We give up

Full refreshes grow with the table and load the primary or the replica that runs them. The view shares the database’s resources and its shapes: no search index, no per-user timeline in RAM.

Step 3: A separate read store, fed by changes

Sometimes a read needs a different engine (a search index, Redis, a columnar store), or more capacity than the source can give. Then the view moves out. Every committed change flows through a log. One projector per view consumes the log, and writes the view.

CQRS: one write model, several read models, fed by one log

Scroll or pinch to zoom, drag to pan

• Color key
• Service
• Database

The service must not write the read store itself as a second write after the commit. That is a dual write, and a crash or a race between two updates leaves the view wrong forever, as the databases page explains. The log is fed in one of two ways, both covered in detail elsewhere:

• Change data capture (CDC). A connector such as Debezium reads the database’s own replication log: the Postgres WAL through a logical replication slot, or the MySQL binlog. It publishes every committed row change. Nothing in the service changes, and nothing committed is missed.
• Domain events through an outbox. The service writes an event row (“OrderPaid, order 9, v2, amount 48”) in the same transaction as the change. A relay or CDC publishes it. The transactional outbox gives at-least-once publishing. Distributed transactions shows it with the inbox on the receiving side.

A CDC row change couples every view to the write schema. A domain event is a contract the team versions. Many systems run CDC on the outbox table only. That gives CDC’s reliability, plus an event’s contract.

Step 4: The projector: ordered, idempotent, checkpointed

A projector is a consumer, so the usual delivery rules hold: at least once, in order within a partition. Three duties are specific to a view.

Ordered per key. The log is partitioned by order id, so one consumer reads order 9’s events in order. Order across orders is not needed (what per-key order costs).

Idempotent by a version per key. Each event carries the entity’s version: a counter the write model increments, or the commit LSN. Each view row stores the version it holds. An event that is not newer is skipped.

The usual idempotent consumer is an inbox of event ids. It stops repeats. But it accepts an older event it has never seen. That’s what a snapshot load overlapping the log delivers, or a second feed path. The version check rejects both, with no table of seen ids.

Checkpointed with the view. The projector saves the offset it has applied, in the same transaction as the view rows. So the view and its position never disagree, and a restart seeks there. A broker-side commit also works, since the version check absorbs the replay after a crash. But rebuilds and reconciliation need the view’s own position.

The event carries the order’s whole new status, so a gap in versions is harmless. A view that adds deltas (“one more PAID, one fewer PLACED”) is right only if every transition is applied exactly once. The leaderboard and the ad click aggregator both write absolute totals to their views for exactly this reason.

TradeoffVersion per key plus a checkpoint saved with the view

We gain

Duplicates, redeliveries and stale events change nothing, so at-least-once delivery gives an exactly-once effect on the view. A crash costs a short replay and no repair.

We give up

Every event must carry a version that only grows per key, and every view row stores it. Views that cannot hold a version per key (a plain counter) must keep a seen-set or write absolute values instead.

Step 5: Rebuild a view from the log

A view is a function of the log. A projector bug, a new field or a new shape is fixed by replaying from the start: a read model is disposable. A safe rebuild never edits the live view:

Rebuild into a shadow copy, then swap

Scroll or pinch to zoom, drag to pan

• Color key
• Service
• Database

Three things decide whether a rebuild is possible and how long it takes:

• The log must still hold the history. Kafka keeps 7 days by default. A compacted topic keeps the latest event per key forever. That’s enough for state-carrying events. It’s not enough for a view that needs every transition, like counts per day, or an audit trail. For those, archive raw events to object storage. Or start from a source snapshot, plus the log from its position, as the search reindex does.
• Throughput is per partition. Take 20,000 events a second per partition, a typical batched rate. (The range is wide, see At scale.) With 48 partitions, 2 billion events take about 35 minutes. On one partition, more than a day.
• It must not starve live traffic. Throttle it, and load the new store with replicas and refreshes off.

The snippet runs that sequence in miniature. A v1 projector without the version check let a late copy of o1’s v2 overwrite v3. Projector v2 replays into a shadow copy, chases live traffic to the head, is compared with v1, and is swapped in by moving one reference.

JProjector.javaPyread_alias.pyC#OrderProjector.csGoorder_projector.goTSorder-projector.ts

JavaPythonC#GoTypeScript

JavaPythonC#GoTypeScript

import java.util.*;

// Fixing a projection the safe way. The live view was built by a v1 projector that applied every
// event as it came, so a late, older copy of an event sent an order back in time. The fix is a v2
// projector with a version check per key, replayed from offset 0 into a shadow copy while v1 keeps
// serving reads, then compared and swapped in with one atomic switch.
public class Projector {
    // State-carrying: the event holds the order's whole new status, not a delta, so applying the
    // newest version is enough and a duplicate or an older copy can simply be skipped.
    record Event(long offset, String orderId, long version, String status) {}

    record Row(long version, String status) {}

    // One copy of the read model. In production a table, a Redis keyspace or a search index;
    // everything one handle() call changes here is one transaction in that store.
    static class ViewStore {
        final Map<String, Row> rows = new TreeMap<>();
        final Map<String, Integer> countByStatus = new TreeMap<>();
        long checkpoint = -1; // last offset applied, saved with the rows: resume at checkpoint + 1

        @Override
        public String toString() {
            StringJoiner out = new StringJoiner(", ");
            rows.forEach((k, r) -> out.add(k + " " + r.status() + " v" + r.version()));
            return out + " | counts " + countByStatus;
        }
    }

    static class OrderProjector {
        final ViewStore store;
        final boolean checkVersions;
        int applied, rejected;

        OrderProjector(ViewStore store, boolean checkVersions) {
            this.store = store;
            this.checkVersions = checkVersions;
        }

        void handle(Event e) {
            Row cur = store.rows.get(e.orderId());
            // The version check: skip anything not newer than what the view holds for this key.
            // It rejects a relay's duplicate (same version) and a late copy (older version) alike.
            if (checkVersions && cur != null && e.version() <= cur.version()) {
                rejected++;
            } else {
                // Counts follow the row's state (leave the old status, enter the new one), never a
                // blind +1, so a skipped event moves nothing and a rebuild lands on the same totals.
                if (cur != null) store.countByStatus.merge(cur.status(), -1, Projector::sum);
                store.countByStatus.merge(e.status(), 1, Projector::sum);
                store.rows.put(e.orderId(), new Row(e.version(), e.status()));
                applied++;
            }
            store.checkpoint = e.offset(); // same transaction as the row
        }

        void catchUp(List<Event> log) {
            for (long o = store.checkpoint + 1; o < log.size(); o++) handle(log.get((int) o));
        }
    }

    // The name readers resolve: a search alias, a view name, a config key. Moving it is one write,
    // so no reader ever sees a half-built copy, and moving it back is the rollback.
    static class ReadAlias {
        volatile ViewStore current;

        ReadAlias(ViewStore store) {
            current = store;
        }
    }

    // A count that reaches zero is removed, so two copies with the same orders compare equal.
    static Integer sum(Integer a, Integer b) {
        int s = a + b;
        return s == 0 ? null : s;
    }

    public static void main(String[] args) {
        List<Event> log = new ArrayList<>(List.of(
            new Event(0, "o1", 1, "PLACED"),
            new Event(1, "o2", 1, "PLACED"),
            new Event(2, "o1", 2, "PAID"),
            new Event(3, "o1", 2, "PAID"), // the outbox relay published it twice
            new Event(4, "o2", 2, "CANCELLED"),
            new Event(5, "o1", 3, "SHIPPED"),
            new Event(6, "o1", 2, "PAID"))); // a stale copy of v2, arriving late

        ViewStore live = new ViewStore();
        OrderProjector v1 = new OrderProjector(live, false);
        v1.catchUp(log);
        ReadAlias reads = new ReadAlias(live);
        System.out.println("live, built by v1: " + reads.current);

        // v2 replays into a new copy. v1 keeps serving and applying new events, so the copy chases
        // a moving head; it is swapped in only once it has caught up.
        ViewStore shadow = new ViewStore();
        OrderProjector v2 = new OrderProjector(shadow, true);
        v2.catchUp(log);
        log.add(new Event(7, "o3", 1, "PLACED")); // live traffic during the rebuild
        v1.catchUp(log);
        while (shadow.checkpoint < log.size() - 1) v2.catchUp(log);
        System.out.println("v2 applied " + v2.applied + ", rejected " + v2.rejected + " as duplicate or stale");

        // Compare at the same checkpoint before switching. Every difference must be one the fix
        // explains; anything else means v2 has a bug of its own.
        for (String k : shadow.rows.keySet()) {
            Row a = live.rows.get(k), b = shadow.rows.get(k);
            if (!b.equals(a)) System.out.println("differs at " + k + ": live " + a + ", rebuilt " + b);
        }
        reads.current = shadow; // the swap; keep the old copy a day for rollback
        System.out.println("reads now see: " + reads.current);
    }
}

Step 6: Read your own writes while the view lags

The user pays for order 9. The page reloads from the “my orders” view. And the view still says PLACED, because the projector is 800 ms behind. The one reader who always notices lag is the writer. The consistency models page covers session guarantees in general. With projections, there are four practical fixes:

• Version token. The command returns the version it committed (order 9, v2). The client sends it on its next read. The query API compares it with the version the view row holds. If the view has it, serve the view. If not, wait up to about 100 ms, then read that one entity from the write model.
• Read from the write model for the writer. For about 10 seconds after a write, the writer’s reads of their own resources go to the primary. It’s simple. But it loads the primary when writers are many.
• Optimistic UI. The client shows what it wrote at once and lets the view catch up. No server cost, but only the device that wrote sees it.
• Update the view in the command’s transaction. Step 1 again, for the one view that must never lag (a cart, a balance). Every other view stays asynchronous.

Read-your-writes with a version token

Scroll or pinch to zoom, drag to pan

• Color key
• Service

The token cannot be a Kafka offset. The command returns before the relay or CDC publishes the event, so no offset exists yet. A version per entity needs no coordination, since the view already stores it per row. Sometimes a token must cover all of a session’s writes. Then it carries the commit LSN instead, and the projector records the source LSN it has applied, per partition.

Play with the whole loop. The write model and the log are on top. Projector A keeps “my orders”, by upserting each order’s state. Projector B keeps counts and revenue by status, by applying each transition, the way a naive counting projection does. Each view is checked against a correct replay of the log, up to its own checkpoint. So the demo can tell behind (lag) from wrong (divergence).

Commands to the write model

Place orderPay, ship or cancel oneSteady traffic

Trouble in the stream

Redeliver last eventLate, older versionNew type: OrderRefunded

Projectors

Version check per key: onReset

Write model orders table, source of truth

Event log newest first, A and B mark where each reads next

A: My orders upsert the order's state, grouped by customer

FastSlowPaused

Rebuild from offset 0

B: Sales by status move counts and revenue per transition

FastSlowPaused

Rebuild from offset 0

Read the viewToken, else primary

Read as the writer

Three orders exist and both views are caught up. Slow down a projector, then place a few orders.

Things to try:

• Set A to Slow, place three orders, then Read as the writer. The view lacks the new order. Switch to “Token, else primary”, and read again. The view holds no row (or an older version) for the token, so the read goes to the write model.
• Turn on steady traffic with A on Slow. 1.4 writes a second against 0.7 applied: the lag grows without limit. A consumer slower than its input never catches up.
• Redeliver the last event, then a late, older version. Both are rejected by version. Turn the check off and repeat. A shrugs off the duplicate, because an upsert of the same state is harmless. But it goes back in time on the stale event. And B double-counts both.
• Inject OrderRefunded. A stores it. B has no code for the type, retries forever, and its lag climbs. Park it in the dead-letter queue: B catches up with wrong totals. Deploy v2 and rebuild B: the shadow copy replays from offset 0 and is swapped in correct.

Step 7: Event sourcing, at a glance

CQRS keeps a write model in tables, and derives views from its changes. Event sourcing goes one step further: the events are the write model. An order is not a row with a status. It is the list OrderPlaced, ItemAdded, OrderPaid, and its current state is a fold over that list.

A command loads the entity’s events, or a snapshot plus the events after it. It checks the rules against the folded state. Then it appends new events, with an expected version. So two concurrent commands on one entity conflict, instead of overwriting each other.

It gives a complete history, audit by construction, and any new view by replay. In the stock exchange the journal of sequenced inputs is the state, and the order books and the hot standby are derived by applying it. The payment system’s ledger is the same idea for money: append-only lines, with balances derived and rebuilt from them.

What it costs: every query needs a projection from day one. Events are forever, so every mistake in one is read by every future version of the code. Deleting personal data from an append-only log needs crypto-shredding (encrypt each person’s fields with their own key, then delete the key). And a CRUD screen (“edit the address”) becomes an event, a handler and a projection.

Use it where the history is the product: ledgers, trading, audit-heavy workflows, and collaborative editing. (The collaborative editor keeps an operation log.) Keep a state table plus an outbox everywhere else.

Go deeperEvolving event schemas1 min

Events are replayed for years by code written later, and during a rollout new events meet old code, so every change must work both ways.

• Add, never repurpose. New fields are optional with defaults. amount in dollars becoming cents is silent corruption on replay.
• Version the event, and let an upcaster turn v1 into v2 on read, so projector logic sees only the newest shape.
• Decide what an unknown type does. A convenience view skips and logs. An exact view (money, inventory) stops and alerts, because skipping makes it silently wrong.
• Check compatibility at publish time. A schema registry (Avro or Protobuf) rejects a producer schema that breaks the chosen rule: backward (the default), forward, or full.

Options and tradeoffs

Where the read model lives

Option	Lag	Read shapes available	Costs	Wins when
Indexes and read replicas	Replica lag, ms	The write schema only	Index writes, replica hardware	The queries fit the schema; this is not CQRS and often enough
Summary table in the same transaction	None	Anything relational	Write latency, hot rows, coupling	One read must be exact right after the write
Materialized view in the same database	Refresh interval, minutes	Anything SQL expresses	Full recompute per refresh on Postgres	Dashboards that tolerate minutes; small to medium data
Separate read store fed by a log	100 ms to seconds	Any engine: search, KV, columnar	A pipeline, idempotent projectors, rebuild tooling	Reads need another engine or far more capacity than the source

TradeoffStart in the database, move a view out only when it needs another engine

We gain

Most read problems are solved by an index, a replica or a materialized view, with no lag handling and no pipeline. The separate store is kept for the reads that need it: search, timelines, analytics.

We give up

A view moved out later needs a backfill and a cutover, and the team learns the pipeline under pressure instead of by design.

How the read model hears about changes

Feed	Reliability	Coupling	Extra moving parts	Wins when
Dual write from the service	Loses or reorders updates on crashes and races	Low	None	Never for a view that must converge
CDC on the business tables	Every commit, in commit order	High: views see table and column names	Connector, replication slot, Kafka	Many consumers of a legacy schema you cannot change
Outbox, relayed by CDC or polling	Every commit, at least once	Low: a published event contract	An outbox table, a relay	The default for services you own
Event sourcing	The events are the truth	Lowest between views, highest in the code	An event store, snapshots, upcasters	History is the product: ledgers, trading, audit

TradeoffDomain events through an outbox, relayed by CDC

We gain

Events commit atomically with the change, leave in commit order without polling the primary, and form a contract that survives a refactor of the tables.

We give up

The service writes and maintains the events. The outbox table must be pruned. And a stalled connector holds WAL on the primary (see How it fails).

What an event carries

Event shape	Example	Duplicate or late copy	Rebuild from a compacted topic	Wins when
Notification (id only)	OrderChanged 9	Harmless: the consumer fetches current state	Yes	Many consumers want different fields; source can take the reads
State-carrying (full new state)	Order 9 v3: SHIPPED, total 48, ...	Harmless with a version check	Yes	Views mirror entities: search docs, “my orders”
Delta (what changed)	Order 9: PLACED to PAID, +48 revenue	Double-counts without dedup or a version check	No: needs every event	Aggregates and audit trails

Notification events push load back onto the source: 10 consumers fetching order 9 is 10 reads per write, when the source is busiest. Most designs use state-carrying events for entity views and derive aggregates from them in the projector, writing absolute values.

TradeoffState-carrying events, with a version, for every entity view

We gain

Idempotency is one comparison, a compacted topic can rebuild the view, and no consumer reads back from the source.

We give up

Bigger events, and every change publishes the whole entity, which leaks fields consumers did not need unless the event contract is curated.

Go deeperGiving the writer their own write, compared1 min

Option	Who is covered	Cost	Wins when
Optimistic UI	The writing device	None on the server	Always, as the first layer
Version token, fallback to primary	The writer, any device that carries the token	A version comparison per read, rare primary reads	Lag is usually small, writers are many
Primary for N seconds after a write	The writer’s session	Primary load proportional to active writers	Few writers, simple stack
View updated in the write transaction	Everyone, at once	Write latency, the view tied to the write database	One view that must never lag (a cart, a balance)

Go deeperWhen CQRS is overkill1 min

Skip a separate read side when any of these hold:

• The read shapes fit the write schema, with an index or two.
• One database (with replicas) handles the read load. A Postgres primary with a few replicas serves tens of thousands of indexed reads a second.
• Every read must see the latest write. (An inventory check before a booking reads the source, whatever else exists.)
• The team has no one to run a CDC pipeline and a broker.
• The domain is CRUD: forms that edit records, where “commands” and “queries” touch the same shape anyway.

Applying CQRS to a whole system because one screen is slow multiplies the moving parts for every screen. Apply it to the reads that need it.

How it fails

Lag spikes. A write burst, a slow view store, a rebalance or a deploy, and the projector falls behind. Reads stay fast but get older. Measure lag in seconds (the age of the oldest unapplied event): 10,000 messages behind is 1 second at one rate and 10 minutes at another.

Poison events. An event the projector cannot apply (an unknown type, a failed validation) is retried forever. And every event behind it on that partition waits. Retry a few times, then park it in a dead-letter topic, and alert. Parking leaves the view wrong for that key, until the fix is deployed and the event replayed. So a view that must never skip (money) stops and pages instead.

Projection divergence. A projector bug, a skipped event, a manual fix in the view store, or a dual write someone added. Lag fixes itself. Divergence never does. Detect it with reconciliation. On a schedule, compare the view with the source at the same position: counts and checksums per key range, or recomputed sample keys. Fix it with a rebuild, or a targeted replay. The ad click aggregator reconciles every day against the raw log, before invoicing.

Double application. A delta projection (count = count + 1) meets a duplicate. It might come from a relay, a rebalance, or a rebuild run into the live view. Nothing signals the wrong count. Prevent it by design (see Step 4).

Ordering across partitions. Per-key order holds within one partition. A view that joins two streams has no such guarantee:

A view that joins two topics sees events in an order nobody wrote

Scroll or pinch to zoom, drag to pan

• Color key
• Service
• Queue or stream

Fixes, in order of preference:

1. Tolerate either order: a placeholder customer filled in later, or a join at read time.
2. Key both streams by the view’s key, so they share a partition.
3. Buffer until the dependency arrives, with a timeout.

Adding partitions to a topic causes the same problem inside one stream. A key’s new events land on a new partition, while its old ones are still unread.

A stalled CDC connector fills the source’s disk. A Postgres replication slot keeps every WAL segment its consumer has not confirmed. Debezium down for a day means a day of WAL on the primary, until the disk fills and writes stop. Set max_slot_wal_keep_size (Postgres 13 and later). Then Postgres invalidates a slot that falls too far behind instead. Alert on retained WAL. And re-snapshot the views after a slot is lost.

Failure	Blast radius	Detect	Fix
Lag spike	Stale reads for every view on the slow projector	Age of the oldest unapplied event, per partition	Batching, more partitions, read-your-writes fallback
Poison event	Every key on that partition stops updating	Lag on one partition, repeated errors on one offset	Retry, then dead-letter and alert; replay after the fix
Divergence	Wrong answers that look right, indefinitely	Reconciliation against the source at the same position	Rebuild, or targeted replay of the differing keys
Double application	Counts and sums inflated	Reconciliation; totals above the source	Version per key, absolute values, processed-event table
Cross-partition order	Joins with missing or placeholder data	Placeholder rows that never fill, join misses	Tolerant projector, co-partitioning, bounded buffering
Stalled CDC slot	The source database’s disk, then its writes	Retained WAL per slot, connector lag	max_slot_wal_keep_size, alerting, re-snapshot
Rebuild into the live view	Readers see half-built data, deltas applied twice	Sudden drops and jumps in the view	Shadow copy and an atomic swap, never in place

At scale

Numbers to remember. These are typical ranges from common deployments, not guarantees: hardware, batch size, payload and the view store decide where a system lands. Measure your own.

What	Typical range
CDC lag, commit to Kafka, steady state	About 100 ms to 1 s; minutes during a snapshot or after a connector restart
Polling outbox relay	Half the poll interval on average: 50 to 500 ms at 100 ms to 1 s polls
Kafka produce to consume, same region	A few ms to tens of ms
Elasticsearch visibility after a write	The refresh interval, 1 s by default
Projector, one row write per event	500 to 2,000 events a second per partition (0.5 to 2 ms per round trip)
Projector, 500 events per batch transaction	10,000 to 50,000 events a second per partition
Rebuild, 2 billion events, 48 partitions at 20,000/s	About 35 minutes
Postgres full refresh of a materialized view	Grows with the table: seconds at millions of rows, minutes at billions
Kafka default retention	7 days; compacted topics keep the latest event per key indefinitely

The end-to-end lag a user sees is the sum of four delays: CDC or relay delay, broker latency, the projector’s batch delay, and the view store’s own visibility delay (an Elasticsearch refresh). A healthy pipeline is typically 0.5 to 2 seconds from commit to visible.

At 10x (3,000 writes a second), the projector’s per-event round trip is the first limit. Batch: read 500 events, keep only the newest version per key, write them in one transaction with the checkpoint. Add partitions early, because adding them later moves keys. Rebuild time becomes a planning number: keep it under how long you can live with a broken view.

At 100x (30,000 writes a second, dozens of views owned by different teams), the stream becomes a platform. Schemas are checked by a registry. Each view has an owner, a lag SLO and a reconciliation job. Rebuilds come from an archive in object storage. Some views become stream-processing jobs (Flink, Kafka Streams) with local state. Each region runs its own projectors, with its own lag.

What to monitor:

• Lag per view and per partition, in seconds: the age of the oldest unapplied event. Alert on the SLO (for example, p99 under 5 seconds), not on message counts.
• Dead-letter rate and the oldest parked event per view. A parked event is a known-wrong key.
• Reconciliation differences per view, which should be zero. Trend them.
• Version-check rejections per view. A sudden rise means something is replaying into the view.
• Read-your-writes fallbacks to the primary per second: a cheap proxy for user-visible lag.
• For CDC: retained WAL per replication slot, and connector state.

In the interview

Interviewer asksThe search index and the order database disagree for some orders. How did it happen, and how do you fix it?

First, lag or divergence? Compare them at the same position, for those keys. Behind heals itself. Wrong at its own checkpoint means one of a few things. A dual write. A projector without a version check, which let a stale event win. A dead-lettered event, never replayed. Or a manual edit. For the data: a targeted replay of those keys, or a rebuild into a new index and an alias swap. For the system: versioned writes (Elasticsearch external versioning with the row’s LSN), the index fed only from the log, and a nightly reconciliation of counts and checksums per key range.

Interviewer asksA user pays for an order and the order page still says unpaid. The view is fed by CDC. What do you do?

The view is behind by the pipeline’s lag, usually under a second. I would not make the view synchronous. The pay command returns a token: the order id, and the version it committed. The page sends it. The query API compares it with the version the view holds for that order. It serves the view if it has caught up. Otherwise it waits briefly, then reads that order from the primary. The client also shows PAID optimistically. Other users get no promise, and need none.

Interviewer asksWe need a new field in the 'my orders' view, for orders going back three years. How do you backfill it?

Projector v2, with its own consumer group, writes a new copy of the view. If a compacted topic holds three years of state-carrying events, replay it from offset 0. If not, load a source snapshot, note its log position, and replay from there. The version check makes the overlap harmless. Throttle it, to protect live lag. Once v2 reaches the head, compare it with v1. Switch reads in one step, and keep v1 a day for rollback.

Interviewer asksShould we use event sourcing for this e-commerce system?

For most of it, no. Catalog, carts and order status are well served by tables, an outbox, and projections where reads need another shape. Event sourcing makes every read a projection, every event permanent, and privacy deletes a project. I would use it where history is the product: the payment ledger, append-only with derived balances, is already close to it. Elsewhere the outbox already gives us the stream and replayable views.

Common mistake

Projecting with increments and trusting exactly-once delivery

count = count + 1 per event is right only if every event arrives exactly once. Nothing gives that end to end. Relays republish, rebalances replay, and rebuilds replay everything. Kafka’s exactly-once covers Kafka, not your view store. Skip anything not newer than the stored version. Or write absolute values (“seller 7’s Tuesday revenue is 4,180”).

Common mistake

Reaching for CQRS and event sourcing as a package

CQRS needs only a write model, a change stream and views. Event sourcing is a separate, heavier decision about the source of truth. Say the reads fit an index and a replica. Then proposing both adds a broker, projectors and permanent event schemas, to a problem a materialized view solves.

Mid-level covers

Separates the write database from a read store, such as a search index or a cache. Feeds it asynchronously through a queue, instead of writing both. Says the read side is eventually consistent.

Senior adds

Feeds views from an outbox or CDC, never a dual write. Makes projectors idempotent with a version per key and saves the checkpoint with the view. Rebuilds into a shadow copy with an atomic swap, and knows retention decides whether that is possible. Gives the writer read-your-writes with a version token, tells lag from divergence, handles poison events, and says when CQRS is not worth it.

Seen in

• News feed: each timeline is a precomputed view of post IDs, fed by fan-out.
• Typeahead: the top suggestions per prefix are a view of the query log, built offline and swapped in whole.
• Ad click aggregator: rollups are projections of the click log, written as absolute totals, reconciled daily and rebuilt by replay after a bug.
• Leaderboard: Redis sorted sets fed by an outbox, applied with ZADD GT of absolute totals so duplicates cannot lower a score.
• Payment system: an outbox for payment events, and seller balances derived from append-only ledger lines and rebuilt nightly.
• Ticketmaster and hotel reservation: search fed by CDC, and a per-user (or per-guest) orders table fed from the outbox once the write side shards.
• Proximity service: an in-memory geo index, loaded from a snapshot and kept fresh by state-carrying CDC events keyed by business.
• Job scheduler: run history flows through an outbox and Debezium, into a Cassandra view. Live state stays in the run store.
• Distributed cache: a binlog-driven invalidator, the same log-driven freshness applied to cache keys.
• Stock exchange: the journal is the state, and the books and the standby are derived by applying it.
• Pastebin: a feed of new pastes built as a read model from events, not a query across shards.

Recap

The shape. Reads need shapes the write schema cannot serve cheaply: search, aggregates, timelines. A write model for invariants, one read model per query shape, fed by changes.

The ladder. Indexes and replicas. A summary table in the same transaction. A materialized view. Then a separate store fed by a log, when a read needs another engine.

The feed. Never a dual write. CDC from the WAL or binlog, or domain events through an outbox (often relayed by CDC). State-carrying events make idempotency a version comparison.

The projector. Ordered per key by partitioning, idempotent by a version per key, and checkpointed, ideally in the same transaction as the view. Absolute values, never blind increments.

Rebuilds. A view is a function of the log. Replay into a shadow copy, compare, and swap atomically. Retention or an archive decides whether you can. Partitions decide how fast.

Lag. Typically 0.5 to 2 seconds end to end. Give the writer read-your-writes with a version token and a primary fallback, plus optimistic UI. Measure lag in seconds per partition.

Failure. Poison events stall a partition: retry, dead-letter, alert, replay. Divergence never heals: reconcile. Cross-partition joins must tolerate any order. A stalled CDC slot fills the disk.

Event sourcing. Events as the source of truth: full history and replay, at the cost of permanent schemas and hard deletes. For ledgers and trading, rarely for CRUD.

Further reading

• On this site: message queues, for the outbox, ordering and delivery semantics. Distributed transactions, for the inbox and sagas. Consistency models, for session guarantees. Search, for keeping an index in sync. And databases, for polyglot persistence.
• CQRS, Martin Fowler: the pattern, and a clear warning about where it adds more complexity than it removes.
• Event Sourcing, Martin Fowler: events as the record of state, with rebuilds, temporal queries and their costs.
• Debezium connector for PostgreSQL: how CDC reads the WAL through a replication slot, snapshots, and what happens when the connector stops.