CS Fundamentals Trainer · Guides
Event sourcing and CQRS
Storing facts instead of state: the event store, optimistic concurrency, replay and snapshots, read models and projectors, eventual consistency, event design and upcasting, and when it is not worth it.
38 min read
Most applications store the current state: one row per account holding the balance, overwritten on every change. That is simple, and it throws information away. You can see that the balance is 20, but not that it was 100 a minute ago, who changed it, or why. Event sourcing keeps the history instead: the system stores every change as an immutable event ("deposited 100", "withdrew 80"), and the current state is derived by replaying them. CQRS (command query responsibility segregation) goes with it naturally: separate the model that decides and changes things from the models that answer queries.
Both are powerful, widely misapplied, and often described through vague slogans. In this guide I built a small event store on SQLite and measured what replay, snapshots, concurrency control and read-model projections really do (code in the appendix). It connects to message queues and sagas for integration, to microservices for the architecture around it, and to the log-structured ideas of storage engines and consensus, where a replicated log is also the heart of the system.
1. State or events?
Take a bank account. With state storage the table has one row:
| account | balance |
|---|---|
| A | 20 |
With event sourcing the table holds what happened, in order, and nothing is ever updated or deleted:
| stream | version | event |
|---|---|---|
| account A | 1 | Opened |
| account A | 2 | Deposited 100 |
| account A | 3 | Withdrew 80 |
The balance, 20, is a fold over the events: start with nothing, apply each event in order. The log is the source of truth; the balance is a view of it that can always be recomputed.
Why would you do this?
- A complete audit trail by construction, not as an afterthought that can drift from the data. In some domains (finance, healthcare, inventory, legal) the history is the product.
- Temporal queries: "what was the balance on the 3rd?" is a replay up to a point in time. A state table cannot answer it once it has been overwritten. In my test the balance of the account after each event was 0, 100 and 20, and any of those moments can be reconstructed.
- Debugging and testing: reproduce a bug by replaying the exact events; test behaviour as "given these events, when this command, then these new events".
- New views of old data: a new report or search index can be built by replaying history that you already have, without having recorded the data for that purpose in advance.
- Decoupling: other components subscribe to the events instead of polling your tables.
And the price, which this guide also spells out: complexity, eventual consistency in the read side, schema evolution that never ends, and the fact that most applications do not need any of the benefits above.
2. The event store
An event store is an append-only log with a few strict rules:
- Events are immutable: you never edit or delete one. A mistake is corrected by appending a new event ("deposit reversed"), the way an accountant corrects a ledger.
- Events are grouped into streams, usually one stream per entity (an aggregate):
account-A,order-1234. Within a stream every event has a version (1, 2, 3...), and(stream, version)is unique. - There is also a global position across all streams, which lets consumers read everything in order.
Diagram: The write side: commands become events
A command is a request to do something and may be refused ("withdraw 200"); an event is a fact that already happened and cannot be refused ("withdrew 80"). The handler loads the stream, rebuilds the state, checks the business rules, and if they hold, appends the new events. Name events in the past tense, in business language (OrderPlaced, AddressChanged), not as table changes (OrderRowUpdated): the meaning is the value.
Optimistic concurrency
What if two commands for the same account run at the same time? Each loads the stream, sees a balance of 100, and decides that withdrawing 80 is allowed. Without protection both would append, and the balance would be -60. The store prevents this with an expected version: the append says "add this event if the stream is still at version 2". I ran this with two threads:
| Client | Result |
|---|---|
| writer 1 | accepted (stream moves to version 3) |
| writer 2 | conflict: the stream is at version 3, expected 2 |
The final balance was 20, not -60. The loser reloads the stream, sees the new balance of 20, and now correctly rejects the withdrawal (or retries if the command still makes sense). This is optimistic concurrency control: no locks, a conflict is detected at write time, and the unique (stream, version) constraint makes it hold even under races. The consistency boundary is the stream: invariants must be enforced within one aggregate, which is why aggregates should be small (an account, an order) and not "the whole customer database". Rules that span aggregates need sagas and eventual consistency (the distributed transactions guide).
3. Rebuilding state, and snapshots
To rebuild an account you read its events and apply them in order. Step through the process, then switch on the snapshot:
Interactive animation: event replay
Replaying is cheap for a stream with ten events and expensive for one with millions. I measured it for a Python fold over events in SQLite:
| Events in the stream | Full replay | Replay from a snapshot taken 100 events before the end |
|---|---|---|
| 100 | 0.4 ms | 0.3 ms |
| 10,000 | about 22 ms | 0.25 ms (100 events read) |
| 1,000,000 | about 2.4 to 2.7 s | 0.4 ms (100 events read) |
A snapshot is a saved copy of the state at some version. To load the aggregate, read the latest snapshot and replay only the events after it. The cost of loading becomes constant, which is why snapshots are the standard answer when streams grow long. Two cautions: the events remain the source of truth (a snapshot is a cache and can be thrown away and rebuilt), and a snapshot is written in a shape of the state, so when you change the state class you must invalidate or migrate old snapshots. Often you do not need snapshots at all: streams per entity are usually short, and a stream that grows without bound is a sign that the aggregate is too big (close the books and start a new stream, for example one per month for an account).
4. CQRS: separate models for writing and reading
Event sourcing makes the write side excellent at deciding and poor at answering questions: "all accounts with a balance over 1,000" would mean replaying every stream. So you build read models (projections): tables or indexes shaped for the queries you need, kept up to date by a projector that subscribes to the events. That separation is CQRS.
Diagram: CQRS with event sourcing
Each read model can use the technology that suits it: a relational table, a search index (see search and full-text indexing), a cache, a graph. You can add a new one at any time and build it from the history.
A projector, built carefully
A projector reads events from a checkpoint (the last global position it handled), applies them to the read model, and stores the new checkpoint. The detail that matters is that the read-model change and the checkpoint move in the same transaction, so that a crash cannot apply an event twice or skip one. I wrote one that maintains a table of orders per customer:
- From 1,000
OrderPlacedevents it produced 50 customers with 1,000 orders and a total of 12,997. - I then deleted the read model and the checkpoint and replayed the log: it was rebuilt in 63 ms with an identical result.
- Running the projector again with nothing new applied 0 events: it is idempotent.
That rebuild is a major benefit: a bug in a projection is fixed by correcting the code and replaying, not by repairing data by hand. For a large history, rebuilds take time, so teams build the new version of a read model next to the old one, switch queries over when it has caught up, and drop the old one.
Eventual consistency
The read model is updated after the event is committed, so for a moment a query can return old data. How stale depends on how the projector gets its events. A simple model, in which the projector polls every interval and needs 5 ms to process a batch, gives:
| Poll interval | Median staleness | 99th percentile |
|---|---|---|
| 10 ms | 10 ms | 15 ms |
| 100 ms | 55 ms | 104 ms |
| 1,000 ms | 502 ms | 995 ms |
Subscribing to a push notification instead of polling shrinks it to the processing time plus the network, usually milliseconds, but never zero. The consequences have to be designed in:
- Read your own writes. After "place order", the user is sent to a page that reads from the read model, which may not have the order yet. Remedies: return the new version or position from the command and have the query wait until the projection has reached it (with a timeout); show the result from the command's own response (optimistic UI); or read from the write side for that one screen.
- Tell the user the truth where it matters ("your order is being processed").
- Process commands against the write side, never against a read model: the decision must use up-to-date state, not a possibly stale copy.
CQRS without event sourcing is common and often enough: a normalised write database plus denormalised read tables, materialised views or a search index fed by change data capture. Event sourcing without CQRS is rarely practical, because of the query problem above.
4.1 Running projectors in production: crashes, poison events and rebuilds
A projector is a small program that must run for years, and the interesting problems are not in the happy path. I tested four of them on an event store in SQLite (script in the appendix; small data sets, so read the ratios and the failure modes, not the milliseconds).
Recovering after a crash. A projector reads a batch, updates the read model and records how far it got (the checkpoint). If it dies between the two, a restart re-reads the batch. With increments such as balance = balance + amount, applying the batch twice double-counts. I injected a crash with 4% probability per batch into 300 runs over 2,000 events; 163 runs had at least one crash:
| Design | Runs with a crash | Runs that ended with a wrong read model |
|---|---|---|
| read model committed, then the checkpoint written separately | 163 | 163 |
| read model and checkpoint in one transaction | 163 | 0 |
If the read model lives in the same database as the checkpoint, use one transaction. If it does not (a search index, another store), make every update idempotent (set a value computed from the event, or store the event position on the row and skip anything not newer) so that re-applying a batch changes nothing.
A poison event is one that the projector cannot process: malformed data, an unknown type, a bug. I put 5,000 events through a projector with one event at position 2,500 that had text ("142 EUR") where a number was expected:
| Strategy | What happened |
|---|---|
| halt on the error | stopped at event 2,500; the other 2,501 events (50%) never reached the read model, and all 50 accounts were stale or wrong |
| skip it silently | 4,999 applied; one account permanently wrong, and nothing says so |
| retry three times, then park the event and its stream in a dead-letter table and carry on | 4,932 applied; 68 events parked (the poison event and every later event of that one account, to keep that account's events in order); the other 49 accounts stay current; an alert fires |
After fixing the cause (here an upcaster that reads "142 EUR" as 142) and re-driving the 68 parked events in order, 0 of 50 balances were wrong. The pattern is: retry for transient errors, quarantine at the smallest scope that preserves order (the stream, not the whole log), alert on the dead-letter count, keep the failing payload for diagnosis, and re-drive after the fix. Halting is the right choice only when order across streams matters or you would rather be down than wrong. Silent skipping is almost never right. Prevent poison events at the source by validating events when they are written, and by testing every stored version of every event type (next section).
Rebuilding a read model without disrupting production. You rebuild when you change a projection, fix a bug or add a new view. The naive way is to empty the table and replay. I did that with 150,000 events while a reader queried the total every few milliseconds:
| Rebuild | Time | Reads that returned a wrong total |
|---|---|---|
| in place: delete the rows, then replay | 1.0 s | 407 of 407 (100%) |
side by side: replay into a new table balances_v2, then swap the names in one transaction | 1.0 s | 0 of 449 |
The naive rebuild shows users partial data for as long as it runs; with a real log (hours of replay) that is an outage. The safe procedure is the blue/green one: (1) build the new read model next to the old one, from position 0, while the old one keeps serving; (2) let the live projector feed both, or catch up from the last position until the lag is a few events; (3) compare the two where they should agree (row counts, checksums, a sample of queries); (4) switch readers in one atomic step (rename, a view or an alias, a feature flag), under a short write lock so that no event falls between catching up and swapping; (5) keep the old one for a while for rollback, then drop it. The script measures steps 1 and 4; steps 2, 3 and 5 are the operating procedure and are not measured. Also rate-limit the replay so it does not starve the live system, and remember it needs capacity for reading the whole log.
5. Designing events
- Facts, not instructions or table updates.
MoneyDeposited {amount, at}is a fact.AccountUpdated {balance: 120}hides what happened. - Carry enough data to be useful on their own, such as the price at the time of the order, so that replaying them later does not depend on today's catalogue. Do not embed copies of whole entities that change.
- Keep them immutable and forever readable. Events outlive the code that wrote them, so the schema evolves under compatibility rules (below).
- Distinguish internal events from public ones. The events of an aggregate are an internal detail; events that other services consume are a contract, and are best published as separate, stable integration events, not by exposing the store directly.
- Name the intent:
OrderCancelledByCustomerandOrderCancelledOutOfStockallow different reactions whereOrderStatusChangeddoes not.
Schema evolution: upcasting
You cannot rewrite history, yet the shape of events changes. The common technique is upcasting: when an old event is read, a small function converts it to the current shape. In my test, version 1 of Opened stored the owner as one string, version 2 as first and last names:
| Stored | Read as (current shape) |
|---|---|
schema 1: {"owner": "Ada Lovelace"} | {"owner": {"first": "Ada", "last": "Lovelace"}} |
schema 2: {"owner": {"first": "Grace", "last": "Hopper"}} | unchanged |
Rules: store a schema version with each event; only make additive changes where possible; write upcasters for the rest; keep every old version testable. The alternative, a one-off migration that rewrites the stored events, is possible but defeats the idea of an immutable log and is risky.
Evolving events and replaying after code changes
Upcasting handles the simple case. The wider rules, because events are kept for the life of the system and every replay runs today's code over yesterday's data:
Ways to change an event, from safest to most disruptive
| Change | How | Notes |
|---|---|---|
| add an optional field | readers use a default when it is missing ("tolerant reader") | no migration; the preferred change |
| change a field's shape or meaning | a new schema version plus an upcaster from the old one; chain them (v1 to v2 to v3) so any stored version reaches the current shape | every old version must stay readable; keep one sample event of each version in the tests |
| the business meaning changed | a new event type (AddressChanged vs AddressCorrected), never a changed meaning under the old name | old events keep their old meaning forever |
| split or merge streams, or restructure heavily | copy-transform: read the old log and write a new one in the new shape, run both until verified, then cut over | a project, not an edit; the old log is kept as the archive |
| rewrite stored events in place | avoid; if unavoidable, back up, do it as a versioned migration, and verify by rebuilding the state before and after | breaks the "immutable history" promise and any hash chain |
Replaying after a code change. The state of an aggregate is fold(apply, events). That only stays true if apply is a pure function of the event: no clock, no random numbers, no database or service lookups, and no business decisions in it. Decisions belong in the command handler, and their outcome is recorded in the event. I showed the difference with a withdrawal fee. When the fee was computed inside apply at 1%, the balance was 444 when the events happened; the business changed the rule to a flat 5 and a replay produced 435: history changed silently. When the fee was stored in each event, the balance stayed 444 after the rule changed, and the new rule applied only to new commands.
Related rules:
- Do not add validation to
applythat rejects old events (a new "balance must not go negative" check would make an old, legitimate stream impossible to load). Enforce new rules when deciding;applyaccepts what was recorded. - Snapshots and read models are caches of a fold. After you change
applyor the projection, invalidate the snapshot (store a version number with it) or rebuild the read model. - Test with a replay of real history: run the new code over a copy of production events and compare the resulting states and read models with the old code's, so a change of meaning shows up as a diff before release.
- Version the projector too: record which version produced each read model, so you know which ones need a rebuild after a deployment.
Personal data and deletion
Immutability collides with the right to erasure (GDPR). Common strategies: keep personal data out of the events, storing only an identifier and keeping the data in a separate, deletable store; or use crypto-shredding: encrypt each person's data in the events with a per-person key, and delete the key when the person asks to be forgotten, leaving unreadable bytes. Plan this at the start; it is very hard to add later.
Is an event log an audit trail?
It is tempting to say "we store every change, so we have an audit trail". An event log gives you a good start, not something that meets a legal or regulatory requirement by itself. The gaps:
- Append-only is a convention, not a guarantee. An events table is ordinary rows; anyone with write access to the database can
UPDATEorDELETEone, and nothing in the system notices. A hash chain (each event's hash covers the previous hash) makes edits detectable: in my test the chain of 100,000 events took 83 ms to build and 80 ms to verify, and a changed amount was reported at position 50,000. But an attacker who can write the table can recompute the chain after the edit, and the chain then verifies. What catches that is anchoring the latest hash outside the attacker's reach (a write-once store, a signed timestamp, an external service, a separate team's system); with the anchor, the recomputed chain was detected because its head no longer matched. Write-once (WORM) storage, database permissions that forbidUPDATEandDELETE, and separation of duties (those who operate the application cannot rewrite the log) address the rest. - Business events are not access events. An audit often needs to know who read a record, who was denied, who logged in, who changed a permission, and who exported data. Most of that never becomes a domain event. Record it separately.
- Who, when and why must be in the event. Include the authenticated actor (and, for support staff acting on someone's behalf, both), the time from a trusted clock, the request or correlation id, and the reason or ticket where the process needs one. Timestamps from application servers drift and can be set wrong; for some uses a trusted time source or timestamping authority is required.
- Completeness. Events written after the transaction may be lost if the process dies; use the outbox/atomic write so that no change exists without its event, and no event without its change. Prove it with reconciliation checks.
- Retention and erasure pull in opposite directions. Some laws require records to be kept for years (bookkeeping and tax records, for example), and privacy law requires deleting personal data on request. Resolve it with the design from the previous section (personal data outside the log, or encrypted with a key that can be destroyed), and decide per data category.
- Access and readability. The log itself is sensitive, so control who may read it, and make sure it can still be read and interpreted in ten years: keep the schemas, the upcasters and the documentation.
- Requirements differ by sector and country. Financial reporting, healthcare, payments, e-signatures and tax-relevant bookkeeping each have rules about integrity, traceability, retention and evidence (in Germany, for example, the GoBD principles for bookkeeping records). These come from regulators and auditors, not from the architecture, so ask them what they accept as evidence before claiming that the design satisfies them. I am not a lawyer, and this section is not legal advice.
The honest summary: event sourcing makes a complete record of state changes cheap, which helps an audit. Making that record trustworthy as evidence needs tamper-evidence, anchoring, trusted identity and time, access logging, and an agreement with whoever audits you.
6. Integrating with the rest of the system
Other services want to react to your events. Two rules keep this reliable:
- Publish atomically with the commit. Do not append to the store and then publish to a broker as two separate steps (one can succeed and the other fail). Either publish from the store's own subscription (the log is the outbox: a relay reads it in order and publishes) or use the outbox pattern (message queues and delivery guarantees).
- Assume at-least-once delivery. Consumers will see duplicates and must be idempotent, for example by remembering the last position or event id they handled, exactly as the projector did.
A process manager or saga listens to events and issues commands to coordinate a multi-step business process across aggregates and services, with compensating commands when something fails.
7. When to use it, and when not
| Good fit | Poor fit |
|---|---|
| a domain where history, audit and "why" matter: ledgers, orders, inventory, claims, collaborative editing | simple CRUD, content management, settings screens |
| complex business rules and workflows that benefit from "given events, when command, then events" tests | reporting and ad-hoc querying over current state as the main need |
| several different views of the same data, built and rebuilt over time | a small team with no experience, little time and no need for the benefits |
| a need to subscribe to what happened rather than poll | data with strong, immediate read-after-write needs everywhere |
Cheaper alternatives for part of the benefit: an audit table written in the same transaction as the change, temporal tables (system-versioned tables) in some databases, or change data capture to stream row changes to other systems. Use event sourcing for an aggregate or a bounded context that really needs it, not for a whole system by default. If you do, use a proven store (EventStoreDB, or an append-only table in a relational database with the constraints shown above, or a log system such as Kafka for the distribution side) and a framework that handles snapshots, projections and upcasting, rather than inventing them.
8. Common mistakes
- Event sourcing a whole application when one part needs it.
- Events named after CRUD operations or carrying the full new state, which loses the business meaning.
- Huge aggregates and unbounded streams, which make every command slow and contend on one stream.
- Querying the write side for lists and reports, instead of building a read model.
- Projectors that update the read model and the checkpoint in separate steps, or that are not idempotent.
- Ignoring read-your-writes and surprising users with stale screens.
- Changing the meaning of old events, or deleting and editing them.
- Exposing internal events directly as the public integration contract.
- Forgetting personal data and erasure until the first request arrives.
- Treating the snapshot as the truth.
9. Things to mention in an interview
- The core idea: store facts, derive state with a fold; the log is the source of truth.
- Streams, versions, optimistic concurrency with an expected version, and why aggregates are small.
- Commands against events, decide on the write side, and tests in the given-when-then style.
- Snapshots: why, how they are used, and that they are a cache.
- CQRS: separate read models and projectors, checkpoints and idempotence, rebuilding, and eventual consistency with the ways to manage it.
- Event design and schema evolution (upcasting), and privacy (crypto-shredding).
- Honest trade-offs: where it pays off and which cheaper alternatives exist.
Quick recap
- Event sourcing stores immutable facts and derives state by replaying them; the log is the truth, and you gain audit, temporal queries and the ability to build new views from history.
- The expected version gives optimistic concurrency: of two simultaneous withdrawals one was accepted and one got a conflict, and the balance stayed correct.
- Snapshots keep loading cheap: replaying a million events took about 2.5 seconds, reading 100 after a snapshot took under a millisecond.
- CQRS adds separate read models built by projectors with checkpoints; they are rebuilt from the log (1,000 events in 63 ms in the test) and are eventually consistent, so design for read-your-writes.
- Events are past-tense business facts; they evolve through upcasting, and personal data needs a plan such as crypto-shredding.
- Use it where history and rich business rules matter, not as a default.
Appendix: the experiment code
One Python 3 script using only the standard library (SQLite in memory). It builds the event store with the unique constraint on (stream, version), appends with an expected version, runs two threads that withdraw from the same account, replays streams of 100, 10,000 and 1,000,000 events with and without a snapshot, runs a projector with a checkpoint and rebuilds it from the log, and shows upcasting. Replay times are real measurements on a small virtual machine and vary from run to run (the figures in the guide are rounded); the staleness table is a calculation from the polling interval and a 5 ms batch time, not a measurement of a real system. SQLite runs in a single process here, so the lock around append stands in for the database transaction a real store would use; the (stream, version) constraint is what guarantees correctness in a real database.
An event store on SQLite: replay, snapshots, concurrency, a projector and upcasting
import sqlite3, json, time, random, threading
# ---------- an append-only event store on SQLite, with optimistic concurrency per stream
db = sqlite3.connect(":memory:", check_same_thread=False, isolation_level=None)
db.execute("pragma journal_mode=off"); db.execute("pragma synchronous=off")
db.execute("""create table events(global_pos integer primary key autoincrement, stream text not null, version int not null,
type text not null, data text not null, schema int not null default 1, unique(stream, version))""")
db.execute("create table snapshots(stream text primary key, version int, state text)")
lock = threading.Lock()
class Conflict(Exception): pass
def append(stream, expected_version, events):
"""Appends events only if the stream is still at expected_version (optimistic concurrency)."""
with lock:
try:
db.execute("begin")
v = db.execute("select coalesce(max(version), 0) from events where stream=?", (stream,)).fetchone()[0]
if v != expected_version: raise Conflict(f"stream {stream} is at version {v}, expected {expected_version}")
for i, (t, d) in enumerate(events): db.execute("insert into events(stream, version, type, data) values (?,?,?,?)", (stream, v + i + 1, t, json.dumps(d)))
db.execute("commit"); return v + len(events)
except Exception: db.execute("rollback"); raise
def apply(state, t, d):
if t == "Opened": return {"balance": 0, "open": True}
if t == "Deposited": return {**state, "balance": state["balance"] + d["amount"]}
if t == "Withdrew": return {**state, "balance": state["balance"] - d["amount"]}
return state
def load(stream, snapshot=True):
state, v = {}, 0
if snapshot:
row = db.execute("select version, state from snapshots where stream=?", (stream,)).fetchone()
if row: v, state = row[0], json.loads(row[1])
n = 0
for t, d, ver in db.execute("select type, data, version from events where stream=? and version>? order by version", (stream, v)):
state = apply(state, t, json.loads(d)); v = ver; n += 1
return state, v, n
# ---------- 1. cost of rebuilding state, with and without snapshots
rnd = random.Random(1)
def fill(stream, n):
rows = [(stream, 1, "Opened", "{}")] + [(stream, i + 2, rnd.choice(["Deposited", "Withdrew"]), json.dumps({"amount": rnd.randint(1, 100)})) for i in range(n - 1)]
db.execute("begin"); db.executemany("insert into events(stream, version, type, data) values (?,?,?,?)", rows); db.execute("commit")
print("rebuilding one account by replaying its events (Python fold, SQLite in memory):")
for n in (100, 10_000, 1_000_000):
s = f"acct-{n}"; fill(s, n)
t = time.perf_counter(); st, v, replayed = load(s, snapshot=False); full = time.perf_counter() - t
# build a real snapshot 100 events before the end
st2 = {}
for t_, d_, ver in db.execute("select type, data, version from events where stream=? and version<=? order by version", (s, v - 100)): st2 = apply(st2, t_, json.loads(d_))
db.execute("insert or replace into snapshots values (?,?,?)", (s, v - 100, json.dumps(st2)))
t = time.perf_counter(); st3, v3, replayed3 = load(s, snapshot=True); snap = time.perf_counter() - t
assert st3 == st
print(f" {n:>9,} events: full replay {full*1000:>9.1f} ms ({replayed:,} events read), from a snapshot {snap*1000:>6.2f} ms ({replayed3} events read)")
# ---------- 2. optimistic concurrency: two writers, one decision each
append("acct-A", 0, [("Opened", {}), ("Deposited", {"amount": 100})])
out = []
def withdraw(name, amount):
state, v, _ = load("acct-A", snapshot=False)
time.sleep(0.01) # both read the state before either one writes
if state["balance"] < amount: out.append((name, "rejected: insufficient funds")); return
try: append("acct-A", v, [("Withdrew", {"amount": amount})]); out.append((name, "accepted"))
except Conflict as e: out.append((name, f"conflict: {e}"))
ths = [threading.Thread(target=withdraw, args=(n, 80)) for n in ("writer 1", "writer 2")]
[t.start() for t in ths]; [t.join() for t in ths]
print("\ntwo clients withdraw 80 from a balance of 100 at the same moment:"); [print(" ", o) for o in sorted(out)]
print(" final balance:", load("acct-A", False)[0]["balance"])
# ---------- 3. a projection (read model) with a checkpoint, run in batches: how stale is it?
db.execute("create table orders_by_customer(customer text primary key, orders int, total int)")
db.execute("create table checkpoint(name text primary key, pos int)"); db.execute("insert into checkpoint values ('orders', 0)")
def project(batch=100):
pos = db.execute("select pos from checkpoint where name='orders'").fetchone()[0]
rows = db.execute("select global_pos, type, data from events where global_pos>? and type='OrderPlaced' order by global_pos limit ?", (pos, batch)).fetchall()
last = pos
db.execute("begin")
for gp, t, d in rows:
d = json.loads(d)
db.execute("insert into orders_by_customer values (?,1,?) on conflict(customer) do update set orders=orders+1, total=total+excluded.total", (d["customer"], d["total"])); last = gp
db.execute("update checkpoint set pos=? where name='orders'", (last,)); db.execute("commit") # the read model and the checkpoint move together
return len(rows)
for i in range(1000): append(f"order-{i}", 0, [("OrderPlaced", {"customer": f"c{i % 50}", "total": 10 + i % 7})])
while project(): pass
print("\nread model built from", db.execute("select count(*) from events where type='OrderPlaced'").fetchone()[0], "OrderPlaced events:", db.execute("select count(*), sum(orders), sum(total) from orders_by_customer").fetchone(), "(customers, orders, total)")
# the projection can be dropped and rebuilt from the log at any time, with the same result
before = db.execute("select * from orders_by_customer order by customer").fetchall()
db.execute("delete from orders_by_customer"); db.execute("update checkpoint set pos=0")
t = time.perf_counter(); n = 0
while True:
k = project(500); n += k
if not k: break
print(f"rebuilt from scratch in {1000*(time.perf_counter()-t):.0f} ms; identical to the original: {db.execute('select * from orders_by_customer order by customer').fetchall() == before}")
# replaying a batch twice must not change the result: the checkpoint and the data change in one transaction
pos = db.execute("select pos from checkpoint where name='orders'").fetchone()[0]
print("running the projector again with nothing new applies", project(), "events")
# staleness of the read model if the projector polls every `interval` ms and a write is committed at a random moment
rnd2 = random.Random(5)
print("\nread model staleness (time from commit to visible), projector polling every interval, 5 ms to process a batch:")
for interval in (10, 100, 1000):
lags = sorted(rnd2.uniform(0, interval) + 5 for _ in range(100000))
print(f" poll every {interval:>4} ms: median {lags[50000]:.0f} ms, 99th percentile {lags[99000]:.0f} ms, worst {lags[-1]:.0f} ms")
# ---------- 4. schema evolution: events are forever, so old shapes must still be readable
db.execute("insert into events(stream, version, type, data, schema) values ('acct-B', 1, 'Opened', ?, 1)", (json.dumps({"owner": "Ada Lovelace"}),))
db.execute("insert into events(stream, version, type, data, schema) values ('acct-B', 2, 'Opened', ?, 2)", (json.dumps({"owner": {"first": "Grace", "last": "Hopper"}}),))
def upcast(t, d, schema):
if t == "Opened" and schema == 1: first, _, last = d["owner"].partition(" "); return {"owner": {"first": first, "last": last}}
return d
print("\nupcasting version 1 events to the current shape when they are read:")
for t, d, s in db.execute("select type, data, schema from events where stream='acct-B' order by version"): print(" schema", s, "->", upcast(t, json.loads(d), s))
# ---------- 5. the temporal query that a state table cannot answer
state = {};
print("\nbalance of acct-A after each event (a state table keeps only the last row):")
for t, d, v in db.execute("select type, data, version from events where stream='acct-A' order by version"):
state = apply(state, t, json.loads(d)); print(f" version {v}: {t:<10} balance {state['balance']}")
Operating an event-sourced system: replay, poison events, crashes, rebuilds and hash chains
Python 3, standard library only, SQLite in a temporary file. Timings depend on the machine.
"""Operating an event-sourced system: replay after code changes, poison events, projector crashes, rebuilding a read model while it is being read,
and what a hash chain can and cannot prove. SQLite and the standard library only; numbers are from one small machine."""
import hashlib, json, os, random, sqlite3, threading, time
DB = "/var/tmp/es_ops.db"
def fresh():
for ext in ("", "-wal", "-shm"):
try: os.remove(DB + ext)
except FileNotFoundError: pass
c = sqlite3.connect(DB, isolation_level=None); c.execute("pragma journal_mode=wal"); c.execute("pragma synchronous=off")
c.execute("create table events(pos integer primary key autoincrement, stream text, type text, data text)")
return c
def add(c, stream, type, **data): c.execute("insert into events(stream,type,data) values (?,?,?)", (stream, type, json.dumps(data)))
def workload(c, n_streams=200, n=20_000, seed=1):
r = random.Random(seed); bal = {f"acct-{i}": 0 for i in range(n_streams)}
c.execute("begin")
for _ in range(n):
s = r.choice(list(bal))
if bal[s] < 50 or r.random() < .6: a = r.randrange(10, 200); add(c, s, "Deposited", amount=a); bal[s] += a
else: a = r.randrange(1, min(bal[s], 150) + 1); add(c, s, "Withdrawn", amount=a); bal[s] -= a
c.execute("commit"); return bal
# ---------------------------------------------------------------------------------------------------------------- A. replay after a code change
def part_a():
print("A. Replay after the business rules change")
events = [("Deposited", 1000), ("Withdrawn", 200), ("Withdrawn", 300), ("Deposited", 50), ("Withdrawn", 100)]
fee_v1 = lambda amount: round(amount * 0.01) # the rule when the events were written: 1 percent
fee_v2 = lambda amount: 5 # the rule after a change in the business: a flat 5
def fold_fee_in_apply(fee): # BAD: the fee is computed inside `apply`, from today's code
b = 0
for t, a in events: b += a if t == "Deposited" else -(a + fee(a))
return b
live = fold_fee_in_apply(fee_v1); replayed = fold_fee_in_apply(fee_v2)
print(f" fee computed in apply: balance when the events happened {live}, balance after replaying with the new rule {replayed} -> history silently changed")
stored = [] # GOOD: the decision (including the fee) is recorded in the event
for t, a in events: stored.append((t, a, 0 if t == "Deposited" else fee_v1(a)))
def fold_recorded(stored): return sum(a if t == "Deposited" else -(a + f) for t, a, f in stored)
print(f" fee recorded in the event: balance {fold_recorded(stored)} now, and after the new rule is deployed {fold_recorded(stored)} (the new rule only applies to new commands)")
# ---------------------------------------------------------------------------------------------------------------- B. poison events
def project(c, events, strategy, max_retries=3):
"""A projector that keeps `balances`. `events` come in order. Returns (balances, dead_letters, applied_count, halted_at)."""
bal = {}; dead = []; quarantined = set(); applied = 0
def apply(stream, t, d):
a = d["amount"]
if not isinstance(a, int): raise TypeError(f"bad amount {a!r}")
bal[stream] = bal.get(stream, 0) + (a if t == "Deposited" else -a)
for pos, stream, t, data in events:
d = json.loads(data)
if strategy == "dead-letter-per-stream" and stream in quarantined: dead.append((pos, stream, t, data)); continue # keep the order of that stream
for attempt in range(max_retries if strategy != "skip" else 1):
try: apply(stream, t, d); applied += 1; break
except Exception:
if strategy == "halt": return bal, dead, applied, pos
else:
if strategy == "dead-letter-per-stream": dead.append((pos, stream, t, data)); quarantined.add(stream)
return bal, dead, applied, None
def part_b():
print("B. A poison event in the middle of the log")
c = fresh(); truth = workload(c, n_streams=50, n=5_000); poison_pos = 2500
row = c.execute("select stream, type, data from events where pos=?", (poison_pos,)).fetchone()
true_amount = json.loads(row[2])["amount"]
c.execute("update events set data=? where pos=?", (json.dumps({"amount": f"{true_amount} EUR"}), poison_pos)) # a bad producer wrote text instead of a number
events = c.execute("select pos, stream, type, data from events order by pos").fetchall(); total = len(events)
for strategy in ("halt", "skip", "dead-letter-per-stream"):
bal, dead, applied, halted = project(c, events, strategy)
wrong = sum(1 for s in truth if bal.get(s, 0) != truth[s])
extra = f", halted at position {halted}: {total - applied} events ({100*(total-applied)/total:.0f}%) not visible" if halted else ""
print(f" {strategy:<22} applied {applied}/{total}, accounts with a wrong balance {wrong}/{len(truth)}, parked {len(dead)}{extra}")
# the fix: an upcaster that understands the bad format, then re-drive the parked events in order
bal, dead, applied, _ = project(c, events, "dead-letter-per-stream")
fixed = []
for pos, stream, t, data in dead:
d = json.loads(data)
if isinstance(d["amount"], str): d["amount"] = int(d["amount"].split()[0])
fixed.append((pos, stream, t, json.dumps(d)))
for pos, stream, t, data in fixed:
d = json.loads(data); bal[stream] = bal.get(stream, 0) + (d["amount"] if t == "Deposited" else -d["amount"])
print(f" after the fix and re-driving {len(fixed)} parked events: accounts with a wrong balance {sum(1 for s in truth if bal.get(s, 0) != truth[s])}/{len(truth)}")
# ---------------------------------------------------------------------------------------------------------------- C. crash between the read-model update and the checkpoint
def part_c(trials=300):
print("C. The projector crashes at a random moment")
c = fresh(); truth = workload(c, n_streams=20, n=2_000, seed=3)
events = c.execute("select pos, stream, type, data from events order by pos").fetchall()
def run(design, rnd):
m = sqlite3.connect(":memory:", isolation_level=None)
m.execute("create table balances(stream text primary key, balance int)"); m.execute("create table checkpoint(pos int)"); m.execute("insert into checkpoint values (0)")
crashes = 0
while True:
pos0 = m.execute("select pos from checkpoint").fetchone()[0]
batch = [e for e in events if e[0] > pos0][:100]
if not batch: break
crash_at = rnd.randrange(len(batch) + 2) if rnd.random() < .04 else None
try:
if design == "separate": # update the read model, commit, THEN write the checkpoint
for i, (pos, s, t, d) in enumerate(batch):
a = json.loads(d)["amount"]; m.execute("insert into balances values (?,0) on conflict(stream) do nothing", (s,))
m.execute("update balances set balance = balance + ? where stream=?", (a if t == "Deposited" else -a, s))
if crash_at is not None: raise RuntimeError("crash after the read model, before the checkpoint")
m.execute("update checkpoint set pos=?", (batch[-1][0],))
else: # one transaction: read model and checkpoint together
m.execute("begin")
for i, (pos, s, t, d) in enumerate(batch):
a = json.loads(d)["amount"]; m.execute("insert into balances values (?,0) on conflict(stream) do nothing", (s,))
m.execute("update balances set balance = balance + ? where stream=?", (a if t == "Deposited" else -a, s))
if crash_at is not None: raise RuntimeError("crash before commit")
m.execute("update checkpoint set pos=?", (batch[-1][0],)); m.execute("commit")
except RuntimeError:
crashes += 1
if design == "atomic": m.execute("rollback")
return dict(m.execute("select stream, balance from balances")), crashes
for design in ("separate", "atomic"):
wrong = 0; crashed = 0
for k in range(trials):
got, cr = run(design, random.Random(k)); crashed += cr > 0; wrong += got != truth
print(f" {design:<8}: {crashed} of {trials} runs had a crash, {wrong} runs ended with a wrong read model")
# ---------------------------------------------------------------------------------------------------------------- D. rebuilding a read model that is being read
def rebuild_batches(c, table, start=0, batch=5000):
pos = start
while True:
rows = c.execute("select pos, stream, type, data from events where pos>? order by pos limit ?", (pos, batch)).fetchall()
if not rows: return pos
c.execute("begin")
for p, s, t, d in rows:
a = json.loads(d)["amount"]; c.execute(f"insert into {table} values (?,0) on conflict(stream) do nothing", (s,))
c.execute(f"update {table} set balance = balance + ? where stream=?", (a if t == "Deposited" else -a, s))
c.execute("commit"); pos = rows[-1][0]
def part_d():
print("D. Rebuilding a read model while the application keeps reading it")
for mode in ("in place", "side by side, then swap"):
c = fresh(); workload(c, n_streams=200, n=150_000, seed=5)
c.execute("create table balances(stream text primary key, balance int)"); last = rebuild_batches(c, "balances")
expected = c.execute("select sum(balance) from balances").fetchone()[0]
stop = threading.Event(); stats = {"reads": 0, "wrong": 0, "worst": 0.0}
def reader():
r = sqlite3.connect(DB, isolation_level=None, timeout=30)
while not stop.is_set():
t = time.perf_counter()
try: total = r.execute("select coalesce(sum(balance),0) from balances").fetchone()[0]
except sqlite3.OperationalError: continue
stats["worst"] = max(stats["worst"], time.perf_counter() - t); stats["reads"] += 1; stats["wrong"] += total != expected
time.sleep(0.002)
th = threading.Thread(target=reader); th.start(); t0 = time.perf_counter()
if mode == "in place":
c.execute("delete from balances"); rebuild_batches(c, "balances")
else:
c.execute("create table balances_v2(stream text primary key, balance int)"); rebuild_batches(c, "balances_v2")
c.execute("begin immediate"); c.execute("alter table balances rename to balances_old"); c.execute("alter table balances_v2 rename to balances"); c.execute("commit")
took = time.perf_counter() - t0; stop.set(); th.join()
print(f" {mode:<24}: rebuild took {took:4.1f} s; {stats['reads']} reads, {stats['wrong']} of them ({100*stats['wrong']/max(1,stats['reads']):.0f}%) returned a wrong total; slowest read {stats['worst']*1000:.0f} ms")
# ---------------------------------------------------------------------------------------------------------------- E. a hash chain: what it proves
def part_e(n=100_000):
print("E. Tamper evidence with a hash chain")
h = "0" * 64; chain = []
rows = [(i, f"acct-{i % 50}", "Deposited" if i % 3 else "Withdrawn", json.dumps({"amount": 10 + i % 90})) for i in range(1, n + 1)]
t = time.perf_counter()
for pos, s, ty, d in rows: h = hashlib.sha256((h + f"{pos}|{s}|{ty}|{d}").encode()).hexdigest(); chain.append(h)
write = time.perf_counter() - t; anchored_head = chain[-1] # the head hash is published somewhere the database admin cannot change
def verify(rows, anchor=None):
h = "0" * 64
for k, (pos, s, ty, d) in enumerate(rows):
h = hashlib.sha256((h + f"{pos}|{s}|{ty}|{d}").encode()).hexdigest()
if h != chain[k]: return f"first broken link at position {pos}"
return "chain consistent" + ("" if anchor is None else ("; head matches the anchor" if h == anchor else "; HEAD DIFFERS FROM THE ANCHOR"))
t = time.perf_counter(); r0 = verify(rows, anchored_head); vt = time.perf_counter() - t
print(f" {n:,} events: chaining took {write*1000:.0f} ms, verifying {vt*1000:.0f} ms ({r0})")
tampered = list(rows); tampered[49_999] = (50_000, rows[49_999][1], rows[49_999][2], json.dumps({"amount": 99999}))
print(f" a stored amount changed: plain log -> nothing notices; hash chain -> {verify(tampered, anchored_head)}")
h = "0" * 64; chain2 = [] # the attacker has write access to the table AND recomputes every later hash
for pos, s, ty, d in tampered: h = hashlib.sha256((h + f"{pos}|{s}|{ty}|{d}").encode()).hexdigest(); chain2.append(h)
chain_backup, chain[:] = chain[:], chain2
print(f" same edit with the chain recomputed: chain alone -> {verify(tampered).split(';')[0]}; with the head anchored outside -> {verify(tampered, anchored_head).split('; ')[-1]}")
chain[:] = chain_backup
if __name__ == "__main__":
part_a(); part_b(); part_c(); part_d(); part_e()
Sources
- Martin Fowler, Event Sourcing, CQRS and What do you mean by "Event-Driven"?.
- Greg Young, CQRS Documents and his talks on event sourcing (the origin of the term CQRS), and Vaughn Vernon, Implementing Domain-Driven Design (Addison-Wesley, 2013) for aggregates.
- Microsoft, Event Sourcing pattern and CQRS pattern in the Azure Architecture Center.
- Martin Kleppmann, Designing Data-Intensive Applications (O'Reilly), chapter 11, "Stream Processing", on event logs and derived data, and his talk Turning the database inside-out.
- Greg Young, Versioning in an Event Sourced System (2017), on upcasting and event versioning.
- The EventStoreDB documentation on streams, optimistic concurrency and projections.
- Chris Richardson, microservices.io: Event sourcing and Transactional outbox.
- Greg Young, Versioning in an Event Sourced System and the EventStoreDB documentation on projections, subscriptions and stream metadata.
- Martin Fowler, Event Sourcing and CQRS; Microsoft, Event Sourcing pattern.
- Bundesministerium der Finanzen, GoBD (Grundsätze zur ordnungsmäßigen Führung und Aufbewahrung von Büchern, Aufzeichnungen und Unterlagen in elektronischer Form), as an example of a record-keeping regulation; check the rules that apply to you.
Practise this topic: Databases