Materialized Views, Projection Tables, CQRS Read Models, and Eventually Consistent Derived Data

Learning outcomes

Read models are valuable because a query can consume a shape designed specifically for it. That convenience introduces an operational system: a source log or owner state, a projector, offsets, duplicate handling, ordering assumptions, freshness indicators, rebuild procedures, and alerting when the copy diverges.

1. Derived read models are state machines

A materialized view stores the result of a query or transformation rather than recomputing it for every read. PostgreSQL 18 provides a concrete relational example: the materialized result is persisted and can be refreshed from its defining query. A projection table is a more general application-maintained copy. Command Query Responsibility Segregation (CQRS) separates the model used to accept commands from one or more read models optimized for queries. These concepts differ in implementation, but operationally they share one truth: derived state must be populated, refreshed, validated, and rebuilt.

2. Change delivery can duplicate, reorder, or pause

At-least-once delivery means a consumer can receive the same event more than once. Cross-partition streams can also interleave independent aggregates. A robust projector therefore needs an idempotency key such as event_id, an ordering rule such as per-aggregate sequence or version, and a policy for gaps. Global ordering is often unnecessary; preserving the order required by each invariant is enough. The projection should publish its source offset/version and oldest backlog age so “fresh” becomes measurable.

3. AtlasMart lab: duplicate/out-of-order delivery and rebuild

The broken projector below assumes delivery order is authoritative and counts duplicate messages. The corrected projector deduplicates by event ID, buffers per-order gaps, advances only contiguous sequence numbers, and proves rebuildability from the authoritative log.

from collections import defaultdict

# Authoritative event log. event_id is globally unique; seq is per order.
events = [
    {"event_id":"e1","order_id":"o1","seq":1,"type":"OrderPlaced","customer":"c1","amount":120},
    {"event_id":"e2","order_id":"o1","seq":2,"type":"OrderPaid","customer":"c1","amount":120},
    {"event_id":"e3","order_id":"o2","seq":1,"type":"OrderPlaced","customer":"c1","amount":80},
]
# Delivery contains a duplicate and an out-of-order message.
delivery = [events[1], events[0], events[2], events[0]]

# Broken projector: assumes delivery order is truth and counts duplicates.
bad = defaultdict(lambda: {"orders":0,"paid":0,"last_seq":0})
for e in delivery:
    row = bad[e["customer"]]
    if e["type"] == "OrderPlaced": row["orders"] += 1
    if e["type"] == "OrderPaid": row["paid"] += 1
    row["last_seq"] = e["seq"]
print("broken projection:", dict(bad))

# Safer projector: idempotent by event_id and order-aware sequence buffering.
seen = set()
next_seq = defaultdict(lambda: 1)
pending = defaultdict(dict)
good = defaultdict(lambda: {"orders":0,"paid":0})

def apply(e):
    row = good[e["customer"]]
    if e["type"] == "OrderPlaced": row["orders"] += 1
    if e["type"] == "OrderPaid": row["paid"] += 1

for offset, e in enumerate(delivery, start=1):
    if e["event_id"] in seen:
        continue
    seen.add(e["event_id"])
    pending[e["order_id"]][e["seq"]] = e
    while next_seq[e["order_id"]] in pending[e["order_id"]]:
        n = next_seq[e["order_id"]]
        apply(pending[e["order_id"]].pop(n))
        next_seq[e["order_id"]] += 1

print("correct projection:", dict(good))
print("consumer delivery offset:", len(delivery))
print("unique events applied:", len(seen))
print("authoritative events available:", len(events))

# Rebuild proof: discard the projection and replay authoritative log in sequence order.
rebuilt = defaultdict(lambda: {"orders":0,"paid":0})
for e in sorted(events, key=lambda x:(x["order_id"],x["seq"])):
    row = rebuilt[e["customer"]]
    if e["type"] == "OrderPlaced": row["orders"] += 1
    if e["type"] == "OrderPaid": row["paid"] += 1
print("rebuild matches:", dict(rebuilt) == dict(good))
print("freshness signal: projection offset/version must be observable, not guessed")

4. Freshness belongs in the contract

A derived read model should expose source version, stream offset, event timestamp, or computed lag. Clients can then decide whether a 30-second-old search result is acceptable while a support dashboard waits for a newer order version. A success status from the projector process is insufficient. Alert on increasing lag, stuck offsets, poison events, repeated retries, dead-letter volume, reconciliation mismatches, and rebuild duration.

5. Rebuild and reconciliation are different tools

Replay/rebuild discards derived state and recomputes it from a source of truth. Reconciliation compares existing derived state with authority to detect or correct drift. Rebuild requires retained source history or a source snapshot plus subsequent changes. Reconciliation is essential when historical events may not encode the entire current state. Both procedures need capacity planning because backfills can compete with production traffic for CPU, I/O, network, caches, and write throughput.

6. Production judgment and bridge

Use a derived read model when query shape/frequency justifies duplication and when staleness is acceptable or can be gated by source version. Protect it with tenant-aware projection code, least privilege, replayable change capture, idempotency, lag SLOs, reconciliation, and a tested rebuild. The final lesson combines the chapter by taking one normalized schema and deriving document, wide-column, and key-value physical models from explicit workloads.

Wrong approach: assume “the queue preserves order” and increment counters on every delivery

Ordering guarantees are scoped: a transport may preserve order only within a partition/key, and retries can duplicate messages. A projector that needs stronger ordering must encode that requirement in its key/sequence model. Idempotency plus per-aggregate sequence handling repairs the demonstrated double-count and stale-sequence failure.

Verification, cleanup, and production checklist

Verification is the deterministic program output plus the reasoning checks below. Cleanup is simply deleting the local lesson4.py file because the lab creates no external service or persistent database. In production, repeat the design with representative cardinality/skew, tail-latency, failure injection, tenant-isolation tests, restore/rebuild drills, migration rollback, and cost/capacity evidence before committing to a physical model.

References

Foundational statements are kept vendor-neutral. Version-sensitive examples use current official documentation and are labeled as examples rather than definitions.

• PostgreSQL 18 — Materialized Views — Current persisted-view example and explicit refresh behavior.
• Martin Fowler — CQRS — Architecture reference for separate command and query models.
• Debezium — Outbox Event Router — Current official example of change capture from a transactional outbox.
• Google SRE Workbook — Monitoring Distributed Systems — Operational reference for observable service behavior and meaningful signals.