CQRS and the Consistency Window Between Writes and Read Models

Command Query Responsibility Segregation is often described as two models where there used to be one, which understates the change and invites a shallow reading. Splitting a repository into a CommandRepository and a QueryRepository over the same tables is not CQRS; both still read the same normalized rows, and the write model still constrains the read shape. The style that matters separates the data too: writes go to a model that enforces invariants, and reads come from one or more models built for the queries, updated after the fact.

Once the read model is a separate copy, it is updated by something asynchronous or by a second write in the same transaction. The asynchronous case is the interesting one, and everything difficult about CQRS follows from it: the read model trails the write model, and a client can write and then read something the write has not reached.

A projection is a consumer with a checkpoint

The mechanism on the read side is a loop, fed by whatever change notification the write side emits. It reads ordered changes from a source — an event stream, a change log, a table of outbox rows — applies each to its own store, and records how far it has got. The checkpoint is the part that makes the loop restartable, and it is stored per projection, in the same store as the projection’s data when possible, so that applying a change and advancing the checkpoint commit together. Two separate commits create a window where a change is applied and the checkpoint is not, so a restart reapplies it. That is acceptable only because the apply step is idempotent.

Idempotence is therefore not a refinement but a precondition. Applying a state-carrying change by assignment is naturally idempotent; applying a delta is not. A projection that maintains a counter incremented per event will over-count after any restart that replays an event, and the error persists silently because the number looks plausible.

-- idempotent apply of a versioned change; a replay of an older version is a no-op
INSERT INTO order_summary (order_id, status, total_cents, version)
VALUES ($1, $2, $3, $4)
ON CONFLICT (order_id) DO UPDATE
   SET status = EXCLUDED.status,
       total_cents = EXCLUDED.total_cents,
       version = EXCLUDED.version
 WHERE order_summary.version < EXCLUDED.version;

The WHERE clause on the conflict branch is what makes out-of-order delivery safe. Without it, a late-arriving older event overwrites a newer projection row, and consecutive events for the same aggregate must then be strictly ordered for the projection to converge.

Consumers must also tolerate redelivery of the same change, which at-least-once transports guarantee will happen. The version comparison covers that case as well, since an equal version is not greater than the stored one.

Lag is measured in offsets and observed in behaviour

The projection’s lag is the distance between the newest change in the source and the checkpoint. It is not a constant, and it grows whenever the apply step is slower than the producer: a projection that rebuilds an aggregate from many events, or writes to a store with a per-row lock, falls behind under load and catches up when the load drops. Reporting lag as a duration is convenient for dashboards; reporting it as an offset difference is what makes it actionable, because it says how many changes are missing rather than how long ago the oldest missing one was produced.

Three client behaviours expose the lag, and they need different answers.

A client that submits a command and then immediately requests the resource is the read-your-writes case. If the command was processed synchronously in the write model and the read is served from the projection, the read can return the previous state, and the user sees their own change disappear. The general fixes are to serve that one read from the write model, to return a token from the command that the read can wait on, or to route subsequent reads for that resource to the writer until the projection catches up.

A client that lists resources and expects the item it just created to appear is the same problem over a different query shape, and it is harder because the write model may not support the list query at all. Returning the created resource in the command response and inserting it optimistically into the client’s list is a partial answer that fails on a page refresh.

A client that acts on a stale read and issues a conflicting command is the dangerous case. The write model’s own invariants still hold, so the command is rejected or resubmitted against current state, and the user sees an error for an action the interface offered. Guarding the command with a version the client read from the projection turns the stale read into a detectable precondition failure rather than a surprise.

The version token, and where it lives

A version token in the command response is the most precise of these mechanisms and the one that costs the most plumbing. The write model already has a version per aggregate if it uses optimistic concurrency; exposing it and having the projection record it allows a read endpoint to accept “at least version v” and either answer or wait briefly.

The waiting has a bound, and that bound should be short — a wait measured in hundreds of milliseconds at most, with a fallback that reads the write model directly. A read that blocks for an unbounded time waiting for a projection converts lag into latency for the unlucky client, and a projection that has stopped turns every such read into a timeout.

Where tokens cannot be threaded through, a time-based window is the crude substitute: route reads to the write model for a fixed period after a write by the same client. Its accuracy depends on lag being smaller than the window, which is exactly what fails under load. It is defensible as a temporary measure, not as the design.

Serving projections from the same database

The lag disappears if the projection is updated in the same transaction as the write. This is a real option, and it converts CQRS into a question about schema rather than about consistency: the write model writes normalized tables, and the same transaction maintains denormalized read tables.

What it costs is write latency and contention. Each command now updates every read model in its transaction, so a new read model lengthens the critical path of every write, and the number of tables in one transaction grows. It also couples deployments, since adding a read model is a change to the write path. And it forgoes rebuild-from-source: a projection maintained inside the write transaction cannot be replayed, because the source of truth is the tables after the fact, not a sequence of changes.

The hybrid that is often right is one projection maintained transactionally for the read path that must be immediate, and further projections maintained asynchronously for queries where a small lag is invisible. The split should be stated per read model, because the consistency guarantee is a property of the model rather than of the system.

Failure modes that compound

A projection that throws on one change and retries forever stops at that offset. Everything after it is never applied, and the read model is frozen at a point in time while the write model moves on. The fix is a policy for unprocessable changes: after a bounded number of attempts, record the change and its error, advance the checkpoint, and surface the gap. That trades permanent divergence for a known, auditable hole, which is the better failure.

Rebuilds are the second. A read model whose schema has changed needs to be rebuilt from the source, which requires the source to retain enough history. If the source is a log with a retention window, a rebuild older than the retention window is impossible, and the read model can only be repaired forward. Projects that depend on rebuilds need the retention period to exceed the longest expected time between schema changes, or a periodic snapshot to start from.

The third is proliferation. Every read model is a consumer, a schema, a deployment, a checkpoint, and a rebuild procedure, and a system with a dozen of them has a dozen places where lag can hide. The style pays when queries genuinely differ in shape — a search index, a reporting aggregate, a per-tenant summary — and does not pay when the only difference is a WHERE clause that an index would have served from the primary store.

What the write model gains

The benefit on the other side is not only read performance. A write model that serves no queries can be shaped around the invariants: aggregates that load exactly what a command needs, no join tables kept for display, no settlement of read concerns in the same schema. That freedom is the same one that event sourcing relies on, and the two styles combine — the append-only log supplies the ordered change sequence that projections consume. They are not the same decision, though: CQRS can be built over an ordinary relational store with a change table, and event sourcing can be used with a single model for both reads and writes.