Choosing the Right Event Store: Trade-offs in Persistence Technologies

Hello! Welcome to the final lesson in our module on CQRS and Resilient Persistence.

In our last session, we built a robust optimistic concurrency control mechanism for an event store using a relational database. We saw how a simple UNIQUE constraint on (aggregate_id, version) combined with an application-level check provides a powerful guarantee of data consistency.

This lesson builds directly on that foundation to address the learning outcome: Compare the trade-offs between different persistence technologies for implementing an event store (e.g., relational DB, document DB, specialized event store).

So far, we've treated a relational database as our default choice. Today, we'll challenge that assumption. We will analyze the strengths and weaknesses of three major categories of databases for the specific job of being an event store. Your background in economics and finance will be useful here, as this is fundamentally a study of trade-offs—balancing consistency, performance, scalability, and operational cost to find the optimal solution for a given problem.

1. The Baseline: Relational Databases (e.g., PostgreSQL)

We've already done the groundwork for this approach in the previous lesson. A relational database (RDB) is often the default starting point for many applications, and it can be a surprisingly capable event store.

Let's revisit the article we used previously to summarize the pros and cons.

Based on that reading and our previous work, let's distill the key trade-offs:

Strengths:

• Strong Consistency (ACID): This is the paramount advantage. As the article you just read mentions, you have transactions, isolation levels, and all the engineering that makes relational databases solid. We can atomically append new events and update a synchronous read model (like a usernames table for uniqueness checks) in the same transaction. This is very difficult to achieve with other database types.
• Familiarity and Maturity: Your team and organization likely already have deep expertise in managing, operating, and programming against relational databases. The ecosystem of tools is vast.
• Data Integrity: Features like UNIQUE constraints are the bedrock of the optimistic concurrency control we implemented. The rigid schema can also enforce the structure of your event metadata.

Weaknesses:

• Impedance Mismatch: As the article notes, we're using a general-purpose database for a specialized task. An RDB is designed to model the current state of normalized data, not an append-only log of immutable facts. This can feel unnatural.
• Performance for Stream Reads: Reconstituting an aggregate with a very long history (e.g., a bank account with thousands of transactions) requires reading and deserializing thousands of rows. This can become a performance bottleneck compared to other approaches.
• Projection Complexity: While synchronous projections are an RDB strength, asynchronous projections are more complex. You need a mechanism to publish events to consumers, such as a transactional outbox pattern, logical replication (like Debezium), or a polling process, which adds another layer of infrastructure.

2. The CQRS Advantage: Separating Write and Read Persistence

Before we explore other database types, it's crucial to reinforce a key principle of CQRS. The choice of database for your event store (the write side) is independent of the choice for your read models (the query side).

This separation allows you to pick the best tool for each job.

As the video highlights, you can have a write side that is a "really fast simple key-value store" (or our relational log) and a read side that is a relational database, a document database, a graph database, or a full-text search engine like Elasticsearch. The events from the write model are used to build these projections. This flexibility is one of the most powerful aspects of the pattern.

3. The Specialist: Purpose-Built Event Stores (e.g., EventStoreDB)

If a relational database is a general-purpose tool, a specialized event store is the bespoke, custom-made instrument. These are databases designed from the ground up with one job in mind: storing and retrieving streams of events.

Strengths:

• Optimized Performance: They are architected for extremely high-throughput appends and fast reads of entire streams. The storage engine and indexing are tailored for this specific access pattern.
• Rich Feature Set: They come with built-in features that you would otherwise have to build yourself. A critical one is subscriptions. You can create persistent subscriptions that push events to your projectors in real-time, with guarantees about ordering and delivery. This elegantly solves the "how do I publish my events?" problem we noted with relational databases.
• Developer Experience: The API is designed for event sourcing. Methods like appendToStream, readStream, and subscribeToStream are first-class citizens, making the code clean and intention-revealing.
• Advanced Capabilities: Many offer built-in support for snapshots, stream-level access control, and sophisticated querying of the event log itself (e.g., by event type or correlation ID).

Weaknesses:

• Operational Complexity: It's a new piece of infrastructure. Your team must learn how to deploy, monitor, back up, and manage it. This adds to the cognitive load and operational cost.
• Niche Technology: The community is smaller, and finding expert knowledge can be harder compared to mainstream databases like PostgreSQL.
• Ecosystem Integration: While integrations exist, they may not be as mature or widespread as those for more common databases.

4. The Flexible Middle Ground: Document Databases (e.g., MongoDB)

Document databases offer a different modeling paradigm that can seem attractive for event sourcing. Instead of storing one row per event, you could store one document per aggregate, with the events as an array inside that document.

Let's analyze this approach using MongoDB as our example.

Let's analyze MongoDB's fitness as an event store, not just a read model.

Strengths:

• Fast Aggregate Reads: Reconstituting an aggregate's state is extremely fast, as all its events can be fetched in a single document read.
• Schema Flexibility: The "schemaless" nature of documents is a natural fit for events, which can have varied structures. This simplifies schema evolution.
• Atomic Document Updates: Appending a new event to an array within a document is an atomic operation in MongoDB. This allows for a simple implementation of optimistic concurrency control by checking the aggregate's version number before performing the atomic update.

Weaknesses:

• Unbounded Document Growth: This is the critical flaw. For an aggregate with a long history (e.g., a user account, a financial ledger), the event array will grow continuously. MongoDB has a document size limit (currently 16MB). Hitting this limit would break your system. This makes the "one document per aggregate" model non-viable for many real-world use cases.
• Alternative Model Issues: You could instead store one document per event. This solves the size limit problem but loses the single-read advantage. It essentially turns MongoDB into a key-value store, and you might find a relational database offers stronger consistency guarantees for a similar model.
• Limited Transactions: While MongoDB has improved its transaction capabilities, it cannot match the simplicity and power of atomically updating an event table and a separate projection table within a single RDB transaction.

5. The Grand Comparison

Let's synthesize these findings into a comparative table. This table summarizes the trade-offs you must consider as a system architect.

Feature	Relational Database (PostgreSQL)	Document Database (MongoDB)	Specialized Event Store (EventStoreDB)
Consistency Model	Excellent. Full ACID transactions. Can atomically update events and synchronous projections.	Good (within a document). Atomic updates on a single document. Multi-document transactions are complex.	Excellent. Designed for atomic appends. Strong consistency guarantees for single stream writes.
Write Performance	Good. Solid performance, but with the overhead of a general-purpose transaction engine.	Excellent. Very fast writes, especially with the "one document per event" model.	Excellent. Purpose-built for high-throughput, low-latency appends.
Read Performance (Stream)	Fair to Poor. Can be slow for aggregates with long event histories (many rows to read).	Excellent (if viable). Reading a single document is very fast, but the model isn't always viable.	Excellent. Optimized for reading event streams.
Implementing Projections	Complex. Requires a separate mechanism (polling, CDC) for asynchronous projections.	Complex. Similar to RDBs, requires a mechanism like change streams to publish events.	Excellent. Built-in, real-time subscription model is a core feature.
Scalability	Good. Well-understood vertical and horizontal scaling patterns (e.g., sharding).	Excellent. Horizontal scaling via sharding is a core design principle.	Excellent. Designed for horizontal scaling.
Operational Complexity	Low. Mature technology, widely available expertise.	Medium. More common than specialized stores, but still requires specific expertise.	High. A niche technology requiring new skills for operations and management.
Best For...	Systems needing strong consistency for synchronous projections; teams with deep RDB skills; projects starting small.	Systems with short-lived aggregates where the "one document per aggregate" model is safe; rapid prototyping.	High-performance, large-scale systems where event sourcing is a core pattern; teams willing to invest in specialized tech.

Conclusion

We have now thoroughly analyzed the landscape of persistence options for an event store. The choice is not about finding the "best" database, but about making an informed architectural decision that aligns with your project's specific constraints and goals.

Key Takeaways:

• Relational Databases offer unparalleled transactional consistency, making them a safe and powerful choice, especially when synchronous read models are needed. Their main drawback is the "impedance mismatch" and the complexity of building asynchronous projections.
• Document Databases are attractive due to their flexible schema and developer-friendly model, but the "one document per aggregate" pattern is risky due to potential unbounded growth.
• Specialized Event Stores are the most elegant solution from a pure event sourcing perspective, offering optimized performance and built-in features like subscriptions. This elegance comes at the cost of higher operational complexity and adopting a niche technology.
• The CQRS pattern is your "get out of jail free" card: your choice for the write-side event store does not lock you into a technology for your read-side query models.

Preview of the Next Module:

Throughout this module, we've focused on the manual implementation of these patterns to understand the mechanics. However, you don't always have to build everything from scratch. In our next lesson, we will begin a new module, "Applying Event Sourcing in Python," by comparing the Python eventsourcing library with the manual approach we've discussed. This will show you how frameworks can abstract away persistence details and accelerate development.