EventStoreDB and PostgreSQL

With EventStoreDB, there is no need to get rid of everything you’ve built in PostgreSQL. You can use EventStoreDB for your event-driven architecture and integrate into Postgres, keeping the outputs your business is familiar with.

In this article we outline how EventStoreDB and Postgres complement each other and how  EventStoreDB with Postgres work together with a step-by-step walkthrough.

• How EventStoreDB & Postgres complement each other
• How EventStoreDB & Postgres work together
• What is EventStoreDB?
• What is Postgres?

How EventStoreDB and Postgres complement each other

EventStoreDB and Postgres work very well together because each can leverage their best strengths, effectively complementing one another. 

We’ve outlined the 4 advantages of integrating EventStoreDB with Postgres:

1. EventStoreDB gives you the undisputed source of truth

In an event sourced application, state changes (facts) are persisted as events to EventStoreDB. These events aren’t just data at certain moments in time, but the results of decisions made in a business process. 

That change in state, along with other attributes such as the name of the event and stream it belongs to, tell the story as to what is happening and why. In other words, thanks to EventStoreDB, we have a source of truth (AKA system of record) for the business to leverage.

Learn more about EventStoreDB

2. Continue to produce the same read models in Postgres

You can continue to project data from your events to perfectly fit what already exists in your Postgres solution. 

Existing queries and materialized views can continue to work as they always have. This makes integrating EventStoreDB upstream easier than you may have realized.

When migrating an existing application to leverage EventStoreDB, you can continue producing the same read models already in use for the web, mobile, or analytics. 

Simply put, with Postgres being responsible for the read-side portion of your event sourced solution, downstream consumers can go about their day-to-day operations without knowing there was a change upstream. Downstream consumers of this data can expect the same tables, columns, indexes, queries, and materialized views to work the same.

3. Specific read models for specific use cases

As any experienced event sourcing practitioner will tell you, viewing data through the lens of events will unlock insights that may not have been previously visible. These previously unseen insights can lead to additional read models to fulfil specific purposes. 

When you integrate EventStoreDB with Postgres you have a source of truth upstream - where you have well-defined, business process-oriented streams of what has occurred - enabling you to produce read models fit for specific business purposes.

Rather than using the same expensive query or bloated read models across different locations, you can leverage EventStoreDB's projection capabilities to write specific view models for specific use cases in Postgres.

Because you have separated the reads from the writes and given yourself fine-grain control through events and streams thanks to EventStoreDB, there is nothing stopping you from having a well-defined set of read models at your disposal. Have read models for specific use cases, authorized users, and intended schemas. No more expensive query to the database to retrieve a single, bloated data model that everyone uses.

4. Non-relational options

If storing data in a document-like manner is needed, as opposed to a traditional relational structure, fear not because Postgres has first-class JSONB for storing unstructured data, effectively providing NoSQL-like behaviors and benefits. 

Another similar but less common option is the use of the hstore datatype instead of JSONB.

NOTE: hstore stores key-value pairs as a flat string and only supports basic data types such as string and null values. JSONB stores JSON data in a binary format and supports nested structures, arrays, and complex data types.

What does this all mean? You have options. Postgres as a downstream read-model database is excellent because you may have a use case to use any of these options depending on the read model’s consumers and their constraints.

Below is an EventStoreDB write model example and a Postgres read model example:

EventStoreDB write model example

Postgres read model examples

-- There may be a few tables to update Postgres as part of your read model(s)
-- depending on the amount of normalization structuring there is.

SELECT * FROM orders;
order_id |   user_id  | total_price | quantity | completed |         placed_at         
---------+------------+-------------+----------------------+---------------------------
"1234"   | "abee0f72" | 42.01       | 2        | false     | "2024-01-02T00:07:33.000Z" 


SELECT * FROM order_items;
order_item_id | order_id |   SKU    | item_price |           title
--------------+----------+----------+------------+---------------------------
"64123678"    | "1234"   | "36606"  | 25.01      | "Acme Dehydrated Boulders"
"64123679"    | "1234"   | "421337" | 25.01      | "Acme Tornado Kit"


SELECT * FROM users;
user_id    |  full_name   | first_name | last_name |       email
-----------+--------------+------------+-----------+-------------------
"abee0f72" | "John Smith" | "John"     | "Smith"   | "john@email.email"

Relational Data

-- A Postgres table with a JSONb column called "data_json".
-- This table is specifically used for an internal dashboard for orders where
-- not all the information is required. Properties are named according to the
-- requirements for the (frontend) web dashboard.

SELECT * FROM orders_dashboard_concise_documents

order_id |   data_json                                  |  date_of_order
---------+----------------------------------------------+----------------------
"1234"   |  {                                           | "2024-01-02"
		 |	   "orderNumber": "1234",                   |
		 |	   "placeddAt": "2024-01-02T00:07:33.000Z", |
		 |	   "totalPrice": 42.01,                     |
		 |	   "itemSkus": [ "36606", "421337" ],       |
		 |	   "itemsQuantity": 2                       |
		 |	 }                                          |

JONSB Data

How EventStoreDB & Postgres work together

At a high level, an application change is saved as an event in EventStoreDB. The event is then published to a custom projector service, which subscribes to new events and updates the Postgres database.

Here is the process description in detail:

• The requestor (a user) sends an API request to update the application (e.g. withdraw from an account).
• The API receives the request and makes sure the change satisfies all business constraints (e.g. balance below credit limit).
• The API generates an event and appends it to EventStoreDB’s event log (e.g. withdrew from account event).
• EventStoreDB publishes the event using subscriptions.
• The projector, responsible for updating Postgres, receives the event from the persistent subscription.
• The projector retrieves the data required, updates it, and sends it back to Postgres (e.g. account summary document updated).
• Postgres updates the document.
• End user(s) queries the results from Postgres.

What is EventStoreDB?

EventStoreDB is a state transition database that stores historical events of facts that occurred in an application as an event log. So it not only provides storage, but also a concurrency model, streaming, projections, query, and subscriptions on top of this state-transition data model.

Unlike traditional databases, which usually only store the current state and overwrite past data, EventStoreDB EventStoreDB retains the history state changes and their business context.

A state transition database consists of three major components:

1. Events are state transitions that represent an important business decision in your application (e.g. OrderPlaced, OrderPaid, OrderDelivered). Because they capture business actions taken, they are more granular and fundamental than state changes that are based on database operations. (e.g. OrderInserted, OrderUpdated, OrderDeleted)
2. Streams are what events are logically organized into. A well-defined stream is effectively an ordered list of events relating to an entity or business process. (e.g. a stream named Order-1234 would have the events OrderPlaced, OrderPaid, OrderDelivered)
3. The Log is an append-only immutable log that captures all events that occur in your application. Each appended event is assigned a sequential number. This is a global sequence number in the log which is incremented whenever a new event is appended, which allows the log to record the exact order in which the events arrive at the database. This enables capabilities such as being able to query (projection) with start and ending points without having to replay the entire log.

Strengths of EventStoreDB

1. Expose the Reason(s) Behind each Decision

Millions of decisions are made in an organization each year. EventStoreDB retains these decisions as events in named sequences/streams, which can expose key reasons of success and failures.

2. Time Travel to the Past, Present, and Future

Storing the history of events allows EventStoreDB to reconstruct the past and present. Additionally, with a sufficient number of events, the future can be forecasted using machine learning.

3. Great Support for Eventual Consistency

4. Decouple Complex and Monolithic Systems

5. Guide Developers to Better System Design

Where EventStoreDB shines

EventStoreDB shines as the source of truth of core business data. Where the business decisions made by the constraints outlined in the system of record have their results recorded as events. That is to say, the different transitions your data goes through over time are persisted.

What is PostgreSQL?

PostgreSQL, also known as simply Postgres, is an object-relational database management system (ORDBMS) that dates back to 1986. In fact, the PostgreSQL Global Development Group states it is “the world’s most advanced open source relational database.” Its widespread usage and ever-expanding feature set prove their claim.

A feature that has made Postgres stand out from other relational databases is something that pairs well with EventStoreDB, but we’ll get further into that later. Unlike many other relational databases that use locks for concurrency control, Postgres maintains data consistency with a technique called Multi-Version Concurrency Control (MVCC). Simply put, it can handle many writes and reads all at the same time.

Postgres is a robust solution for a number of reasons, but below are a few relevant points that will help demonstrate why it pairs well with EventStoreDB:

Strengths of Postgres

Postgres is a robust solution for a number of reasons, but below are a few relevant points that will help demonstrate why it pairs well with EventStoreDB:

1. SQL

Not to be taken for granted. There is no need to learn a custom query language when you have the most popular querying language.

2. Many data types supported

Beyond primitives like numeric, string, boolean, and datetime types, there are network addresses, geometric types, JSONB (see below), monetary types, key-value support, multidimensional arrays, and more.

3. Create complex types

Defining your own types can help align your database to more closely represent your applications.

4. Native JSON support

Store and operate on JSON objects can effectively make Postgres handle non-relational data like a NoSQL database. The JSON can be indexed and queried in a variety of ways. It’s referred to as JSONB commonly, as it’s JSON in a binary format.

5. Maturity

Nearly four decades of active development. Postgres can be run on all major operating systems and has a multitude of offerings with various cloud providers.

6. Extensibility

A wide variety of extensions exist, giving Postgres specialized features for specific use cases. One is PostGIS, which expands the capabilities of Postgres’s geospatial capabilities. Another is Citus, which transforms Postgres into a distributed database.

Despite its rich feature set and extensibility, many developers choose Postgres because it simply is a superb relational database first and foremost.

Where Postgres shines

Postgres is a battle-tested juggernaut that keeps up with the growing needs of its community and users.

Despite its rich feature set and extensibility, many developers choose it because it simply is a superb relational database first and foremost.

With many developers structuring their data in a relational style, for now at least, Postgres’s popularity becomes obvious.

Summary

EventStoreDB and Postgres naturally work well together because of their individual strengths. EventStoreDB serves as the source of truth for your system of record and Postgres can provide read models with the relational capabilities you have grown accustomed to.

It is no wonder this duo is a powerful and popular choice amongst event sourcing practitioners.