PostgreSQL Is Not an Event Store

The table takes about fifteen minutes to write. An ID, a stream name, a version number, an event type, a JSON payload, a timestamp. Add a unique constraint, add an index, and you have somewhere to put your events. It is the first thing almost every team reaches for, and for good reason: the database is already running, already backed up, already monitored, and everybody on the team can read it.

In the previous post we pinned down what an event store actually has to do and held Kafka against that list. This one turns it around and points it at the tool people reach for from the opposite direction – the general-purpose database they already trust with everything else. PostgreSQL passes more of that list than Kafka did, and that is exactly what makes it the harder case. Kafka's central failure announces itself. PostgreSQL fails without a sound, a year in, when a read model is missing a row that nobody can account for.

Giving the Table Its Due

The approach deserves a fair hearing first. A relational database hands you transactions, durability, point-in-time recovery, replication, and thirty years of operational practice around all of it – and the events table on top of it is genuinely simple. Appending an event is an INSERT. Loading an aggregate is a SELECT with a WHERE and an ORDER BY. Writing three events as one unit is a transaction, which the database has done well since before most of us started programming. None of this is naive. It is the reasonable first move.

We know, because we made it. Before EventSourcingDB there was wolkenkit, and its event store ran on relational databases – PostgreSQL, MariaDB, SQL Server. It worked. It never felt like the right shape, and it took us a long time to say why. The argument here is not that the table does not work. It is about what "works" is quietly covering for.

The Same Six-Item List

Here is the yardstick again, unchanged from the first post. Changing the list to suit the candidate would defeat the purpose of having one.

• A durable source of truth. Events are not data in transit on their way somewhere else; they are the truth itself, kept for as long as the system lives.
• Append-only, immutable storage. Not by convention or team discipline, but as a guarantee the store enforces, so that no past event can ever be rewritten or quietly dropped.
• Atomic writes across multiple events. A single decision that produces several events is persisted as one indivisible unit, so you never store half of it.
• Optimistic concurrency per stream. The ability to append events only if that stream has not changed since you last read it, which is how invariants survive concurrent writes.
• Two different read patterns. Reading the entire history in order, to build and rebuild read models, and reading a single entity's events cheaply, to reconstruct that one entity's current state.
• Subscriptions as a first-class feature. Observing new events as they arrive, with a seamless transition from replaying the past to following the present.

Six capabilities, and an event store has to deliver all of them together, not most of them with effort. Kafka came apart on the first item. PostgreSQL does not.

What PostgreSQL Gets Right

Take that first criterion. A relational database is a durable source of truth by construction, and it takes work to make it stop being one. There is no retention window quietly expiring the events you were counting on, no compaction collapsing history. What you wrote is there next decade.

Atomic writes across multiple events fall out for free: one INSERT with several rows inside one transaction, all of them landing or none, including when they belong to different streams. Reading a single entity's events cheaply is an index on stream name and version. Neither is a concession. This is a relational database doing what it was built to do.

Optimistic concurrency takes more work and is still achievable. A unique constraint on stream name and version means two writers who both believe they are appending version 7 cannot both succeed: one commits, the other gets a constraint violation, and you translate that into a domain-level conflict. Plenty of production systems run on exactly this.

The difference from a purpose-built store is not who has to remember – both make every writing path declare its expectation – but what you can declare. A unique constraint only checks the number it is handed: a writer that computes the version as MAX(version) + 1 inside the INSERT, rather than passing the one it read, appends without complaint even though the stream moved on in between. A precondition travels with the write itself and reaches conditions a constraint cannot express.

That is two of the six outright, a third once you have written the constraint yourself and every writing path uses it as intended, and half of a fourth: the half of the read patterns that loads a single entity. Two criteria are left, and neither is subtle. What is subtle is the other half – the one PostgreSQL looks like it passes.

Append-Only Is a Promise, Not a Property

The second criterion draws the line exactly where PostgreSQL falls: not by convention or team discipline, but as a guarantee the store enforces. In a relational database, UPDATE and DELETE exist on every table, always. You can make them harder to reach: revoke the privileges, connect as a role that only holds INSERT and SELECT, add a BEFORE UPDATE trigger that raises. Each of these is a fence. None is a wall.

Fences come down in unremarkable ways. A migration tool recreates the table and the trigger does not survive it. A new service copies its connection string from an older one and arrives with more privileges than intended. None of this requires bad intent. It only requires an ordinary Tuesday.

We have made part of this argument before. An audit log beside your tables is a second write, kept correct by discipline rather than by construction. Here there is no second write – only the discipline, which is the part that fails. The same holds for a hash chain over rows an UPDATE can reach: it goes invalid the moment somebody reaches them, and a chain nobody has published outside the database can simply be recomputed.

A store with no edit operation and a table that nobody is supposed to edit are different kinds of object. The first is a property of the system, the second a property of the people currently operating it – and people change jobs. EventSourcingDB has no edit operation to revoke, and each event carries the hash of its predecessor, computed by the database rather than by the writer, so a later alteration is something you detect rather than something you trust did not happen.

The Gap You Cannot See

Now the interesting one, because on paper it looks fine. Reading the entire history in order needs a global sequence, and PostgreSQL has an obvious candidate: a BIGSERIAL or identity column handing out numbers that only go up. A projection remembers the highest number it processed and asks for everything above it. It is the standard recipe.

Here is what that misses. Sequence numbers are handed out at INSERT time, not at commit time, and the sequence never takes one back. That is deliberate and right – it is what lets two writers insert at the same time without queueing behind each other for a number. But it means the order in which numbers are assigned and the order in which rows become visible are two different orders. Transaction A takes 43. Transaction B takes 44. B commits first. For a moment, the table contains a visible row 44 and an invisible row 43.

Now let the projection poll during that moment. It asks for everything above 42, receives 44, processes it, and records 44 as its position. A moment later A commits and row 43 becomes visible – to a reader that will never ask for it again. The event is not lost, not corrupted, not delayed. It is simply skipped, permanently, by a consumer with no way of knowing it happened. Nothing raises, nothing logs, and the read model is just wrong – and stays wrong until somebody notices a number that is off.

One writer at a time and this never happens, which is why it stays hidden so long. Nobody marks the day the second one arrives – usually the day somebody raises the connection pool size or adds a worker.

Every workaround costs something real. You cannot simply wait for the missing number to appear: rolled-back transactions burn their sequence values for good, so a gap might mean "not committed yet" or "never will be". A few seconds' lag before processing buys probability, not correctness. Comparing each row's xmin against pg_snapshot_xmin(pg_current_snapshot()) is correct only once the cursor stops being a sequence number and becomes a transaction ID – and then every projection is coupled to transaction-visibility internals, and one long-running write transaction stalls them all. An advisory lock works if it is held from before the INSERT until the commit, and gives up concurrent writes.

By contrast, EventSourcingDB assigns every event a unique, monotonically increasing ID, globally across all subjects and without gaps, and an event becomes visible only once its transaction has committed. A cursor means what a cursor appears to mean.

Nobody Subscribes to a Table

The last criterion asks for subscriptions as a first-class feature. PostgreSQL's answer is LISTEN and NOTIFY, and it is cleverer than it gets credit for: a notification is delivered when the transaction commits, so you are never told about an event that then rolls back.

But a notification is a nudge, not a delivery. It is not durable – a listener disconnected when it fires never learns that anything happened, and nothing replays it. The payload has to stay under 8,000 bytes, so it can carry the news that an event exists but not, reliably, the event. Put a position in there instead, and it still helps nobody who was not connected to receive it.

Which means you poll – precisely the cursor whose gap we just took apart. The two failures reinforce each other: the ordering gap only bites once something is reading by cursor, and that reader is the part you have to build yourself.

Notice too what "seamless" was asking for. NOTIFY delivers live events but never replays, so you write a catch-up path, a live path, and a handover between them – and the handover is where the off-by-one lives, at exactly the moment events arrive faster than the catch-up finishes.

The alternative is the one that actually works, and it deserves a price rather than a wave. A logical replication slot hands you changes in commit order, and it is a PostgreSQL feature, not a bolt-on. But a slot exists per consumer and somebody creates and monitors each one, and an unread slot pins write-ahead log on disk until the volume fills. That is not a query; it is a service you operate.

The other route is a transactional outbox – and we have argued that you don't need an outbox when storing an event and publishing it are one atomic act. Reaching for one here is the Kafka symptom from the other direction.

What You End Up Building

Step back and look at what is now on the bill. Revoked privileges and a trigger to approximate append-only, plus a review habit so the next migration does not undo them. A read path that respects commit order rather than sequence order – or, if you settle for a lag window, the monitoring for when it was too narrow. A poller, or a slot and its upkeep. And the tests that prove any of it, which means reproducing an out-of-order commit on purpose. Idempotent projections and a stored cursor you would have needed either way; the rest is the price of the table.

Then the part that never makes it into the estimate. Somebody has to own all of it: write down why the trigger is there, explain it to whoever arrives next, take the call when a projection drifts. This is not a library you install; it is a component your team maintains forever, beside the product you were hired to build.

Piece by piece, you build an event store inside your relational database – the very thing you set out to avoid needing. The Kafka case at least forces the conversation: a retention window eventually deletes something that mattered, and somebody has to explain why. PostgreSQL never forces it. It holds your events faithfully for a decade and lets you find the missing pieces one incident at a time.

Where the Table's Job Ends

The fifteen minutes at the top of this post were real. The columns are right, and nobody would correct that table on a whiteboard. What is wrong is the assumption that you were finished. The table was not an event store. It was the first fifteen minutes of one.

There is a tension here with something we have written before. We have said that it was never about the database – that the most valuable outcome of Event Sourcing is the shared language a team builds while modeling. We still mean it: a team that models well on a plain table beats one that models badly on a purpose-built store. But infrastructure still has to hold, and "start with what you already run" is sound advice until what you already run holds the only copy of the truth.

And there is a place where a relational database is not a compromise at all. Looking up current state by a known key is its home ground: a table shaped for one query, indexed for that query, answering in single-digit milliseconds, rebuilt whenever the shape changes. An event-sourced system usually has several such read models – and several other shapes besides. The relational database keeps its place in the architecture. It just is not this place.

Which leaves one sentence. An event store is not a general-purpose database with a table that happens to contain events. It is a store whose guarantees are the event model: append-only because it cannot be otherwise, globally ordered because the order is fixed at commit rather than guessed at afterward, subscribable because handing out events as they arrive is what a log of truth is for. Those are not features layered on top of a database. They are what is left when you take the general purpose away.

This is the second entry in the series, and the two candidates fail from opposite ends. Kafka keeps events beautifully in motion and poorly at rest. PostgreSQL keeps them beautifully at rest and has no idea they are events. Next, the same list goes to the NoSQL databases, where a schema flexible enough to hold anything makes events look like a natural fit.

If you would rather feel the difference than take our word for it, that is an experiment, not a migration. Every event carries a globally ordered, gap-free ID, and one connection delivers the full history followed by the live events, so a subscriber never guesses whether it skipped something. Rebuilding a read model is pointing it at ID 0 and letting it run. It is one binary or one container, nothing to stand up beside it, so installing it and writing your first events take about as long as the table did.