What is a CQRS read model projection and how does it work?

A projection is a function that folds an ordered stream of events into a shape built for reading. Given the same events in the same order, it must produce the same read model. That determinism is the whole contract. It is what lets you treat the read store as a cache of the event log rather than as a source of truth.

The loop has four steps:

  1. Consume events in order from a partition or stream, starting from the last recorded position.
  2. Apply a handler chosen by event type. The handler computes the new row or document from the event payload and the current read-model state. It does not call external services or read the wall clock.
  3. Write the result to a store chosen for the query: a relational table for filtered lists, a search index for full-text queries, a key-value store for lookups by ID.
  4. Record the offset or sequence number of the applied event. Store it in the same transaction as the write where possible, so state and position cannot drift apart.
 Commands ──► Command side ──append──► Event log (ordered, durable)
                                          │
                     ┌────────────────────┼────────────────────┐
                     ▼                    ▼                    ▼
              Projection A          Projection B          Projection C
              (orders list)         (search index)        (customer totals)
                     │                    │                    │
                     ▼                    ▼                    ▼
                 SQL table           Search store          KV store
                     └────────────► Query API ◄────────────────┘

Because every read store is derived, it is disposable. If a schema changes, a bug corrupts data, or a new query needs a different shape, you drop the store, reset the position to zero, and replay the log. The event log is the only thing you must protect.

+----------+    +--------------+  append  +------------------------------+
| Commands |--->| Command side |--------->| Event log (ordered, durable) |
+----------+    +--------------+          | seq 1, 2, 3, ... N, N+1, ... |
                                          +------------------------------+
                                  resume at checkpoint+1 |
                                                         v
+--------------------------------------------------------------------------+
| Projection consumers A, B, C (each runs this loop independently)         |
| 1. CONSUME  next event in log order, from checkpoint + 1                 |
| 2. APPLY    handler[event.type](state, event) -> new state               |
|             pure: no external calls, no wall clock                       |
| 3. WRITE    upsert row/document into read store ---+ same                |
| 4. RECORD   checkpoint = event.seq ----------------+ transaction         |
+--------------------------------------------------------------------------+
         |                            |                            |
         v                            v                            v
+----------------+           +----------------+           +----------------+
| SQL table      |           | Search index   |           | KV store       |
| orders list    |           | full-text      |           | totals by ID   |
+----------------+           +----------------+           +----------------+
         |                            |                            |
         +----------------------------+----------------------------+
                                      |
                                      v
                           +--------------------+
                           | Query API (reads)  |
                           +--------------------+

REBUILD: drop read store -> reset checkpoint to 0 -> replay from seq 1

Each projection resumes from its checkpoint, applies a pure handler per event type, and commits the read-store write and the new checkpoint together, so any read store can be dropped and rebuilt by replaying the log.

Why use rebuildable projections, and who should use them?

The practical payoff is that three classes of work change character. A schema change to a read model stops being a migration script that has to run against live data and be reversible. Instead it is a new projection version that builds alongside the old one. A bug in a handler that wrote wrong totals for three weeks stops being a forensic cleanup. You fix the handler and replay, and the corrected view comes out of the same facts. A product request for a new query shape, such as "orders by region by week," stops requiring a backfill job. You write a projection that subscribes from the beginning. Each case becomes ordinary code review and deployment instead of an incident or a one-off script that nobody wants to run twice.

The teams that benefit most already have the log. If you practice event sourcing, or your services integrate through Kafka topics with long retention or compaction, the hard part is already paid for. Read-heavy services with many divergent query needs also gain a lot, such as search, reporting, per-tenant dashboards, and denormalized API responses. One normalized schema serves all of these poorly, but separate projections can serve each one well.

The cost is real. You need to:

  • retain events long enough to replay them
  • version event schemas
  • run and monitor projection workers
  • budget for rebuild time as history grows

The pattern fits when the log is the authoritative record and the read side changes more often than the write side.

How do you make projection handlers idempotent and deterministic?

Most brokers, Kafka included, deliver at least once from a projection's point of view. A worker can write to the read store, crash before committing its consumer offset, and receive the same events again on restart. If the handler blindly increments a counter or appends a row, the view drifts. Idempotency means applying an event twice leaves the same state as applying it once.

Techniques that hold up

  • Store the position with the data. Persist the last applied offset or event version in the same database transaction as the view update. On restart, resume from that stored position rather than the broker's committed offset. If the two are written separately, a crash between them makes them disagree.
  • Upsert keyed by aggregate ID. Use insert-or-update on the aggregate's ID so a replayed creation event doesn't fail on a duplicate key or create a second row.
  • Guard with the version. Each event carries the aggregate's sequence number. Apply the update only when the incoming version is greater than the stored one. Duplicates and stale events then match zero rows and are skipped.

The example handler that follows does all three in one transaction: an upsert on the order ID, a WHERE stored_version < incoming_version condition, and a write of the partition offset to a checkpoint table.

Determinism

A handler's output must depend only on the current row and the event. It must not read the wall clock, which differs on replay; use the event's own timestamp instead. It must not call external services, whose responses change over time. It must not query other read models, which may be at a different point in their own rebuild. If any of these inputs leak in, a rebuild produces different data than the original run, and you can no longer treat the log as the source of truth.

# Projection handler for an orders read model (PostgreSQL via psycopg2).
# Deterministic: output depends only on the stored row and the event payload.

UPSERT_ORDER = """
INSERT INTO order_view (order_id, status, total_cents, updated_at, version)
VALUES (%(order_id)s, %(status)s, %(total_cents)s, %(occurred_at)s, %(version)s)
ON CONFLICT (order_id) DO UPDATE
SET status      = EXCLUDED.status,
    total_cents = EXCLUDED.total_cents,
    updated_at  = EXCLUDED.updated_at,
    version     = EXCLUDED.version
-- Version guard: duplicate or stale events match zero rows and are skipped.
WHERE order_view.version < EXCLUDED.version
"""

SAVE_CHECKPOINT = """
INSERT INTO projection_checkpoint (projection, topic_partition, last_offset)
VALUES ('order_view', %(partition)s, %(offset)s)
ON CONFLICT (projection, topic_partition) DO UPDATE
SET last_offset = GREATEST(projection_checkpoint.last_offset, EXCLUDED.last_offset)
"""

def handle_order_event(conn, event, partition, offset):
    params = {
        "order_id": event["aggregate_id"],
        "status": event["data"]["status"],
        "total_cents": event["data"]["total_cents"],
        # Use the event's timestamp, never datetime.now(): replay must match.
        "occurred_at": event["occurred_at"],
        "version": event["version"],
    }
    # One transaction: view row and checkpoint commit together or not at all.
    with conn:  # commits on success, rolls back on exception
        with conn.cursor() as cur:
            cur.execute(UPSERT_ORDER, params)
            # Advance the checkpoint even when the guard skipped the update,
            # so a restart resumes after this offset instead of re-reading it.
            cur.execute(SAVE_CHECKPOINT, {"partition": partition, "offset": offset})

A projection handler that upserts by order ID, skips duplicate or stale events with a version guard, and saves the partition offset in the same database transaction as the view row.

How should you handle ordering and checkpointing with Kafka projections?

Kafka orders events only within a partition. Key every event by aggregate ID, so all events for one order or account land in the same partition and the projection applies them in the order they were appended. Across partitions there is no ordering. A projection that joins two aggregates, such as orders and customers, can receive OrderPlaced before the CustomerRegistered it references. You have three options:

  • Tolerate interleaving. Upsert partial rows and fill in fields as the other events arrive. Compare per-aggregate version numbers instead of trusting arrival order.
  • Publish denormalized events. Have producers include the fields consumers need, so the projection never has to join.
  • Build a local lookup projection. Project the other stream into a small table the handler reads from.

Checkpointing decides what a crash costs you:

Concern Commit consumer offset after the write Store offset in the read model transaction
Crash between write and checkpoint Event is reprocessed, producing a duplicate Write and offset commit or roll back together
Gaps Possible if offsets are committed before processing finishes, which auto-commit can do None
Requirement on handlers Must be idempotent Idempotency is still useful but not load-bearing
Requirement on the store Any store Transactions, with offsets kept in the same database as the view
Rebuild Reset the consumer group's offsets Truncate the view and the offset table together

Storing offsets atomically gives effectively-once updates. The cost is coupling: the read model must be transactional. On startup, or when partitions are assigned after a rebalance, the consumer reads its saved offsets per partition and seeks to them. It does not rely on the group's committed offsets. If the projection writes to a non-transactional store such as a search index, you lose that guarantee and fall back to idempotent handlers.

import json
import psycopg2
from confluent_kafka import Consumer, OFFSET_BEGINNING

conn = psycopg2.connect("dbname=readmodel user=projector password=change-me host=localhost")
consumer = Consumer({
    "bootstrap.servers": "localhost:9092",
    "group.id": "order-summary-projection",
    "enable.auto.commit": False,  # the database, not Kafka, is the source of truth for offsets
})

def on_assign(c, partitions):
    # On startup and after every rebalance, seek each partition to the offset saved with the view.
    with conn, conn.cursor() as cur:
        for p in partitions:
            cur.execute("SELECT next_offset FROM projection_offsets WHERE topic = %s AND partition_id = %s", (p.topic, p.partition))
            row = cur.fetchone()
            p.offset = row[0] if row else OFFSET_BEGINNING
    c.assign(partitions)

consumer.subscribe(["orders"], on_assign=on_assign)

while True:
    msg = consumer.poll(1.0)
    if msg is None or msg.error():
        continue
    event = json.loads(msg.value())
    with conn, conn.cursor() as cur:  # one transaction: view row and offset commit or roll back together
        # Version guard keeps the handler safe if the view and offsets are ever restored separately.
        cur.execute("""INSERT INTO order_summary (order_id, status, version) VALUES (%s, %s, %s)
            ON CONFLICT (order_id) DO UPDATE SET status = EXCLUDED.status, version = EXCLUDED.version
            WHERE order_summary.version < EXCLUDED.version""",
            (event["order_id"], event["status"], event["version"]))
        # Store the NEXT offset to read, so a restart resumes after this event.
        cur.execute("""INSERT INTO projection_offsets (topic, partition_id, next_offset) VALUES (%s, %s, %s)
            ON CONFLICT (topic, partition_id) DO UPDATE SET next_offset = EXCLUDED.next_offset""",
            (msg.topic(), msg.partition(), msg.offset() + 1))

A projection that writes the read model row and its partition offset in one PostgreSQL transaction, then seeks to the stored offsets when partitions are assigned instead of using the consumer group's committed offsets.

How do you rebuild read models safely without downtime?

Never truncate a live read model and replay into it. Readers would see empty or partial data for the whole replay. Run blue/green projections instead. Start a new projection version that writes to a versioned table or index, such as orders_view_v2, replaying from the stream start or from a snapshot. It keeps its own checkpoints, so v1 keeps serving traffic. When v2's lag is near zero, switch reads through a view, alias, or config flag. Keep v1 running briefly for rollback, then retire it.

Replay only works if the source keeps full history:

Source Full replay possible? Why
Event-sourced event store Yes Events are the system of record and are kept indefinitely
Kafka topic, unlimited retention Yes Every record is kept, but storage grows without bound
Kafka compacted topic No Only the latest value per key survives, so intermediate events are gone. Counts, timelines and state transitions cannot be rebuilt

Old events arrive in old shapes. Use three techniques together:

  • Upcasters transform each event into its current version as it is read.
  • Tolerant readers let handlers ignore unknown fields.
  • Versioned event types mean a changed meaning gets a new type, not a silent edit to an existing one.

Rebuilds cost time and load. During cutover, writes and storage roughly double, and replay adds read load on the broker. To control this, batch database writes, run one worker per partition, and throttle the rebuild to protect live traffic.

Illustrative estimate (assumed numbers, not a benchmark):

rebuild time ≈ total events ÷ (partitions × events/s per partition)

With 600,000,000 events, 12 partitions and 2,000 events/s each: 600,000,000 ÷ 24,000 = 25,000 s, or about 7 hours.

Crypto-shredding makes some replays lossy by design. Once a person's key is deleted, their events can no longer be decrypted. This means v2 will legitimately differ from v1, so validate the cutover against expected differences, not exact equality.

-- PostgreSQL. v1 keeps serving through the orders_view view while v2 replays.
-- 1. New versioned table; v1 is never truncated.
CREATE TABLE orders_view_v2 (
  order_id    uuid PRIMARY KEY,
  status      text        NOT NULL,
  total_cents bigint      NOT NULL,
  updated_at  timestamptz NOT NULL
);

-- 2. Each projection version keeps its own checkpoint, so v2 starts at 0
--    (or at a snapshot position) without touching v1's progress.
INSERT INTO projection_checkpoints (projection, position)
VALUES ('orders_view_v2', 0);

-- 3. The projector replays into orders_view_v2 and advances its checkpoint
--    in the same transaction as each batch. Poll lag until it is near zero.
SELECT c.projection,
       (SELECT max(global_position) FROM events) - c.position AS lag
FROM projection_checkpoints c
WHERE c.projection IN ('orders_view_v1', 'orders_view_v2');

-- 4. Cutover: DDL is transactional in PostgreSQL, so readers see either
--    the old view or the new one, never a missing view.
BEGIN;
DROP VIEW orders_view;
CREATE VIEW orders_view AS
  SELECT order_id, status, total_cents, updated_at FROM orders_view_v2;
COMMIT;

-- Rollback, if needed: repeat step 4 pointing back at orders_view_v1.

-- 5. After the rollback window, stop the v1 projector, then retire v1.
DELETE FROM projection_checkpoints WHERE projection = 'orders_view_v1';
DROP TABLE orders_view_v1;

A PostgreSQL blue/green rebuild: replay into a versioned table with its own checkpoint, check lag, swap the read view atomically, then retire the old version instead of truncating in place.

How do you operate projections and handle eventual consistency in production?

Read-your-own-writes

Eventual consistency becomes a user-facing bug when someone submits a form and the next page doesn't show their change. The usual fix is to have the command side return the position its event was written at, such as a Kafka partition and offset or a stream version. The client sends that position with its next query. The read side then either waits briefly until the projection's checkpoint passes it, or responds with a "still updating" flag. Waiting adds tail latency that grows with projection lag. Either approach means every query path has to understand positions, which is a real cost in complexity. Apply it only to flows where users actually notice stale data.

Observability

Track lag for each projection, measured as both events behind and age of the last processed event. Also track handler error rates. Alert when a projection's checkpoint stops advancing while input keeps arriving. Throughput can look healthy while one partition is stuck.

The most likely cause of a stuck partition is a poison event. You have two options:

  • Park it. Route the event to a dead-letter topic and keep going. The partition stays live, but the view is now silently missing data, and later events for the same entity may build on a gap.
  • Block the partition. Stop until someone intervenes. The view stays correct but gets staler, and every entity on that partition is affected, not just the bad one.

Choose per projection, depending on whether staleness or incorrectness hurts that view more.

The cost of many projections

Each projection carries its own checkpoint, lag alert, dead-letter handling, rebuild procedure, and on-call knowledge. Ten narrow views over the same stream cost ten times as much to operate. When several of them serve one screen, merge them into one projection.

When should you avoid CQRS projections, and what are the alternatives?

Projections are the wrong tool when a workflow must read its own writes synchronously. A balance check before a withdrawal or a seat reservation cannot tolerate a view that lags by seconds, so those reads belong on the write model. Simple CRUD services with no competing query shapes gain little from a second store and pay for it with a consumer to run. A small team without capacity to own lag alerts, dashboards and rebuild runbooks will see projections drift quietly. Projections also need replayable history. If retention limits, log compaction or deletion obligations mean you cannot keep the full event stream, you cannot rebuild from it, and the main safety property disappears.

The alternatives give up different things:

Option Consistency Cost Rebuild characteristics
Query the write model directly Strong Lowest; adds query load to the primary Nothing to rebuild
Database read replicas Usually asynchronous, lag is small but nonzero One more database instance; same schema Re-seed from the primary
Materialized views in one database Depends on how the engine refreshes them; often stale until refreshed Storage plus refresh load on the primary Drop and refresh in place
Change data capture into search or analytics Eventual A pipeline and a second store to operate Re-snapshot the source tables. Past intermediate states are lost

Start with the write model. Add replicas when read load grows. Reach for CDC when you need a different query engine. Move to event-sourced projections only when you need replayable history and several independent views built from it.

How OneEnv can help

Disclosure: OneEnv is the product behind this blog.

OneEnv (oneenv.io) is a workspace-based governance platform for the things services share — environment variables, database structure, API routes and validation rules. A change to a shared value is staged on a branch, reviewed once, and then every affected service explicitly approves or rejects it before it goes live, so you can see who a change touches before it ships instead of finding out in production.

Applied to cqrs read model projections, this is where it helps:

Saves time

  • Every service that inherits a shared value is listed in one place, so nobody searches repos and chats to find out who is affected.
  • One review per branch replaces coordinating each team separately; each service still keeps its own approve/reject.
  • Changing a shared variable is one edit and one review instead of editing every service's .env by hand.

Saves cost

  • Fewer production incidents and rollbacks caused by a shared value, route or column changing under a consumer unannounced.
  • Less duplicated configuration to maintain: one shared definition inherited by many services.
  • A wrong or stale value is caught at review time by the services it affects, not by a failed deploy.
  • OneEnv has a free plan (1 workspace, unlimited services and members, full governance), so trying it costs nothing.

These are qualitative benefits, not measured results — how much you save depends on how many services share the value.

Want to try it? Start at oneenv.io.