Event Store Design: Streams and Aggregates
On this page
Learning Objective
Design a workable event store schema on a relational database, choose aggregate and stream boundaries around invariants rather than convenience, and explain why a purpose-built event store and a plain table differ mostly in ergonomics rather than in fundamentals.
Concept Explanation
An event store is a narrower thing than it sounds. At minimum it must append an event to a named stream, enforce a conditional append at an expected version, and read a stream in order from a given position. Everything else, subscriptions, projections, snapshots, is built on those three operations. This matters practically because it means you do not need a specialized product to start: a single well-indexed table in PostgreSQL with a unique constraint on the pair of stream id and version provides all three, and a great many production event-sourced systems run exactly that way.
The core table holds one row per event with a global sequence number, a stream id, a version within that stream, an event type, a JSON payload, and metadata. The global sequence number matters as much as the per-stream version: per-stream version is what gives optimistic concurrency, while the global position is what lets a projection or an external subscriber read every event across all streams in a total order and remember where it stopped. Without a reliable global ordering, catching up a projection from a known point becomes guesswork.
There is a subtlety worth knowing about that global counter. On PostgreSQL, a bigserial column assigns numbers at insert time, but concurrent transactions commit out of order, so a reader can see event 105 committed while event 104 is still in flight from a slower transaction. If the reader records position 105 and polls again, event 104 appears in the past and is silently skipped. Production implementations deal with this by tracking transaction ids alongside the sequence, by reading only up to the oldest currently-in-flight transaction, or by serializing appends. Purpose-built stores such as EventStoreDB solve it internally, which is one of the more substantive reasons to use one.
Choosing the aggregate boundary is the highest-leverage decision in the whole design, and the rule is that the boundary is drawn around an invariant. If the business requires that an order's line items must never exceed an approved credit limit, then line items belong inside the order aggregate, because that rule must be enforced atomically at the moment of the decision. If a rule involves an order and a separate customer record, either the rule is not actually a hard invariant and can be enforced eventually, or the boundary is wrong. Aggregates that are too large create contention, because every command against any part of them serializes on one stream version, and they force loading far more history than a given decision needs. Aggregates that are too small push real invariants across boundaries and turn them into sagas, with all the compensation complexity that implies.
Two operational realities round out the design. First, the store must be genuinely append-only: no UPDATE, no DELETE, enforced at the database level with permissions or triggers rather than by convention, because the value of the model collapses the moment someone patches history in place. Second, immutability collides with privacy regulation, since GDPR's right to erasure sits awkwardly with a store that never deletes. The standard answer is crypto-shredding: personal data is stored encrypted with a per-subject key held outside the event store, and erasure is performed by destroying that key, which renders the payload permanently unreadable while leaving the event sequence and all derived non-personal aggregates structurally intact.
| Design decision | Get it wrong by | Consequence |
|---|---|---|
| Aggregate boundary | Drawing it around a database table or a UI screen | Either contention on an oversized stream or real invariants leaking into sagas |
| Stream naming | Using an opaque id with no type prefix | Impossible to filter or route by aggregate type without reading every payload |
| Global ordering | Trusting a bigserial position under concurrent commits | Projections silently skip events committed out of order |
| Append-only enforcement | Relying on convention rather than permissions | One well-meaning data fix rewrites history and invalidates every projection |
| Personal data in payloads | Storing it in plaintext in an immutable log | No mechanism to honor an erasure request without breaking immutability |
Worked Example: A Relational Event Store Schema With Conditional Append
The unique constraint on the stream and version pair is doing the real work here: it is what makes the conditional append atomic, with no application-level locking and no read-modify-write race.
CREATE TABLE events (
global_position BIGSERIAL PRIMARY KEY, -- total order for projections
stream_id TEXT NOT NULL, -- e.g. 'order-9f12'
stream_type TEXT NOT NULL, -- e.g. 'order'
version INT NOT NULL, -- position within this stream
event_id UUID NOT NULL UNIQUE,
event_type TEXT NOT NULL,
event_version INT NOT NULL DEFAULT 1,
data JSONB NOT NULL,
metadata JSONB NOT NULL, -- correlationId, causationId, actor
occurred_at TIMESTAMPTZ NOT NULL DEFAULT now(),
CONSTRAINT one_version_per_stream UNIQUE (stream_id, version)
);
CREATE INDEX events_stream_idx ON events (stream_id, version);
CREATE INDEX events_type_idx ON events (stream_type, global_position);
-- Append-only enforced by the database, not by convention:
REVOKE UPDATE, DELETE ON events FROM application_role;
-- Conditional append. If another writer already used version expectedVersion+1,
-- the unique constraint rejects this insert and the caller retries.
async function append(streamId, streamType, events, expectedVersion) {
const tx = await db.begin();
try {
let v = expectedVersion;
for (const e of events) {
v += 1;
await tx.query(
`INSERT INTO events (stream_id, stream_type, version, event_id,
event_type, data, metadata)
VALUES ($1, $2, $3, $4, $5, $6, $7)`,
[streamId, streamType, v, e.eventId, e.type, e.data, e.metadata],
);
}
await tx.commit();
return v;
} catch (err) {
await tx.rollback();
if (err.constraint === 'one_version_per_stream') {
throw new ConcurrencyError(streamId, expectedVersion); // caller reloads and retries
}
throw err;
}
}Real-World Examples
- EventStoreDB, created by Greg Young, who also coined the term CQRS, is a purpose-built open-source database whose primary abstraction is exactly the one described in this lesson: named streams, per-stream expected-version appends, a global ordering, and catch-up subscriptions. Its existence as a distinct product is evidence that the three primitives, conditional append, ordered read, and reliable global position, are genuinely awkward to get right on a general-purpose database, particularly the out-of-order commit problem in the global sequence.
- Axon Framework, an open-source Java framework used in production by financial and logistics companies in Europe, implements event-sourced aggregates over both relational databases and its own Axon Server, and its documentation deals extensively with the aggregate boundary question this lesson raises. Its widely discussed guidance, that aggregates should be kept small and that a rule spanning two aggregates is a saga rather than an invariant, reflects the practical experience that oversized aggregates become contention bottlenecks once concurrent commands arrive at any real rate.
Review Questions & Answers
An application-level check would have to read the current version, compare it, and then insert, and two handlers can interleave between the read and the insert so that both see version 7 and both believe they are free to write version 8. The unique constraint moves the check into the same atomic operation as the write: whichever transaction commits first takes version 8, and the second gets a constraint violation it can translate into a retry. There is no window between checking and writing, because checking and writing are the same statement.
The sequence number is allocated when the row is inserted, but visibility to readers depends on when the transaction commits, and those two orders differ. A transaction that grabbed position 104 may commit after one that grabbed 105, so a projection that reads up to 105 and stores that as its checkpoint will never see 104 when it appears afterwards. Implementations handle this by tracking in-flight transaction ids and only advancing the checkpoint past the oldest uncommitted transaction, by using a commit-time-assigned ordering, or by serializing appends through a single writer. Purpose-built event stores handle it internally, which is one of their more substantive advantages.
Personal data is not written into the event payload in plaintext. It is encrypted with a key unique to that data subject, and the keys are held in a separate, mutable key store outside the event log. Honoring an erasure request means deleting that subject's key, after which every event payload encrypted with it is permanently unreadable by anyone, including the system itself. The event sequence, the stream versions, and every derived aggregate that does not depend on the plaintext remain intact, so the log's structural integrity and the replayability of non-personal projections survive the erasure.
An aggregate that is too large serializes every command touching any part of it on a single stream version, so the symptom is a rising rate of concurrency conflicts and retries under load, along with command handlers loading far more event history than the decision actually requires. An aggregate that is too small pushes a genuine business invariant across a boundary it cannot be enforced across, so the symptom is a proliferation of sagas and compensating actions to enforce rules that could have been a single transaction, plus recurring bugs where the rule is temporarily violated during the window before compensation runs.