Data Management Patterns Explained: System Design

In a nutshell

CQRS separates the model that handles writes from the models that serve reads, so each can be optimised.
Event sourcing stores every change as an event; current state is rebuilt by replaying them.
Materialized views and index tables precompute data in the shape queries need.
Valet key and static content hosting keep file traffic away from your servers.

CQRS

Command Query Responsibility Segregation splits the system in two. Commands (place order, change address) go to a write model that enforces business rules, usually normalised. Queries read from one or more read models shaped for the screens that use them, kept up to date from the write side's changes.

CQRS with read models updated from events

All steps Writes go through the command side. Changes flow as events into read models built for specific queries. Reads may lag writes slightly.

Diagram as text

Clients send commands that change state.
The command side validates them and saves to the write store.
Change events update the read models asynchronously.
Queries are served from read models shaped for them.

Use when: reads and writes have very different shapes or loads (complex search over simple writes), or many different views of the same data are needed. Avoid for simple CRUD: it adds eventual consistency and moving parts.

Event sourcing

Instead of storing the current balance of an account, store every event that changed it: deposited 500, withdrew 200, deposited 100. The current state is a fold over the events. You get a full audit trail, can rebuild state as of any moment, and can build new read models by replaying history.

State as a fold over events, including "as of" queries

Java

import java.util.*;

class Account {
    record Event(long seq, String type, long amount) {}

    // Replays events up to and including `untilSeq`.
    static long balance(List<Event> events, long untilSeq) {
        long balance = 0;
        for (Event e : events) {
            if (e.seq() > untilSeq) {
                break;
            }
            switch (e.type()) {
                case "deposited" -> balance += e.amount();
                case "withdrew" -> balance -= e.amount();
                default -> throw new IllegalArgumentException("unknown event " + e.type());
            }
        }
        return balance;
    }
}

Python

def balance(events, until_seq=None):
    """events: list of (seq, type, amount) in order. Replays up to and including until_seq."""
    total = 0
    for seq, kind, amount in events:
        if until_seq is not None and seq > until_seq:
            break
        if kind == "deposited":
            total += amount
        elif kind == "withdrew":
            total -= amount
        else:
            raise ValueError(f"unknown event {kind}")
    return total

Materialized view and index table

Materialized view: precompute the results a query needs (order totals per customer per month) and keep them updated, instead of computing them on each read. Index table: in stores that can only look up by primary key (many NoSQL databases), maintain your own secondary index, for example a table from email to user ID, updated whenever users change.

Valet key and static content hosting

Valet key: give a client a short-lived, narrowly scoped token (a presigned URL) so it can upload or download one object directly from storage, without your servers relaying the bytes. Static content hosting: serve images, scripts, stylesheets and even entire static sites from object storage behind a CDN instead of from application servers.

Sharding and cache-aside

Both have their own topics. Sharding splits data across databases by a key; cache-aside loads data into a cache on demand and invalidates it on writes.

Pattern	Problem it solves	Main cost
CQRS	Reads and writes need different models	Eventual consistency, more components
Event sourcing	Need full history and replays	Event versioning, snapshots, complexity
Materialized view	Expensive queries repeated often	Keeping views up to date
Index table	Lookups by non-key fields in key-value stores	Extra writes; consistency between tables
Valet key	File transfer load on servers	Token scoping and expiry
Static content hosting	Servers busy serving files	Cache invalidation for changed files
Sharding	Data or writes too big for one database	Cross-shard queries, resharding
Cache-aside	Repeated reads of slow data	Staleness, invalidation

Common mistakes

Applying CQRS and event sourcing to simple CRUD screens.
Changing the meaning of stored events instead of adding new event versions.
Forgetting that read models lag, then showing users stale data right after their own write.

Interview questions

Try answering out loud before you open each one.

When would you use event sourcing?

When the history of changes is itself valuable: financial ledgers, audit requirements, undo or time-travel features, or when many different read models must be derived. Pair it with CQRS read models and snapshots. For ordinary CRUD where only the current state matters, it adds cost without benefit.

Your notes

Saved in this browser as you type.

Finished this topic?

Mark it read to track your progress, then carry on with the next one.

Next topic