1

I'm thinking about using an Event Sourcing / Event Store pattern in my RDBMS designs.

What I mean by Event Store is a pattern where each data mutation (INSERT / UPDATE / DELETE) is persisted as an INSERT into a suitable table of events. At the same time, hopefully in the same transaction, some piece of logic updates a state table with the result of applying the mutation.

This is similar, but conceptually the opposite of what is traditionally done: first executing the mutation (INSERT / UPDATE / DELETE) on the state table; and then executing an INSERT into a log or audit table.

The advantage of this inverted or Event Store approach, as I understand, is that by construction it allows you to query any past state of the system, at any point in time; to undo most types of changes that might have been wrongly applied, for instance by an application bug, without affecting later events performed by users; and so on.

I've read a few articles about this pattern, but all of them seem to use an opaque JSON blob as the event data. This IMHO violates all that is good about relational databases.

I'm more inclined to model events as dedicated tables, using common sense to group similar events into the same table, and using something like DB triggers to perform the mutations on the state table.

Here is an example of what I'm thinking.

Let's take a "shopping cart" example, where the possible events are:

  1. The user adds a certain quantity of a product to their shopping cart. (ADD)
  2. The user updates the quantity of one of the products in their shopping cart. (UPDATE)
  3. The user removes all quantity of one of the products in their shopping cart. (REMOVE)
  4. The user's shopping cart is emptied, for instance after successfully creating an order. (EMPTY)

In this trivial example, all 4 events may be successfully modeled with a single cart_event table (in most real-world cases, I suppose they would not):

Column Type Null Notes
event_id int N Global sequential id, common to all event tables
timestamp timestamp N Event timestamp
user_id ? N FK to users
event_type enum N ADD, UPDATE, REMOVE, EMPTY
product_id ? Y FK to products, required iff. ADD, UPDATE, REMOVE
quantity int Y Quantity to add or update, required iff. ADD, UPDATE

I'm thinking that the event_id should come from a global sequence, so that all events (in all event tables) can be read in a unique sequential order.

A view may be written with all event types in UNION ALL from all event tables, to browse them in that order. Alternatively, table inheritance may be used, from a global event table, if the RDBMS supports it (they usually don't, or at least they don't support polymorphic queries.)

In any case, the application code would only be allowed to perform SELECT and INSERT into event tables.

Then, AFTER INSERT triggers for all event tables would "apply" the event to a "state" table, which would take the place of the traditional mutable table in SELECT queries. Application code would never be allowed to modify this table, only using it for querying the current state of the system. (The DBA would be allowed to perform mutations on event tables to fix bugs, and then rebuild the state tables.)

Alternatively, a VIEW may be used instead of triggers to create the state table virtually, if the RDBMS is powerful enough to provide decent performance when querying (I doubt it.)

Is this approach well-known and/or supported by major RDBMS?

Are there recognized guidelines on how to apply this design pattern (Event Sourcing) inside the persistence layer, without falling into the schemaless "JSON soup" way of doing things?

7
  • Take a look at FossilSCM, particularly how they implement it in terms of Artefacts, and how they position the rest of the RDBMS.
    – Kain0_0
    Aug 19, 2021 at 10:32
  • If you have a link so someone can look at the focused concept it would be very helpful. Aug 19, 2021 at 12:01
  • Aside: some databases already have features to allow you to query past states (e.g. Oracle Flashback feature set) without you having to maintain all that yourself.
    – Mat
    Aug 19, 2021 at 13:10
  • 1
    In the end this is just a question of tradeoffs. With the model you have in mind, you basically build a complete shadow-database of event sources, which sounds like hell to maintain. With "json soup" you get the mechanism in place once and it remains stable over most changes in the software and the database structure.
    – mtj
    Aug 20, 2021 at 4:25
  • 1
    Are there recognized guidelines on how to apply this design pattern (Event Sourcing) inside the persistence layer? nop. Note that not even events have a cannonical data structure.
    – Laiv
    Aug 20, 2021 at 8:05

1 Answer 1

0

Is this approach well-known and/or supported by major RDBMS?

As you've noticed, this is rather untypical.

I've read a few articles about this pattern, but all of them seem to use an opaque JSON blob as the event data. This IMHO violates all that is good about relational databases.

Kind of, but an event store really only needs two operations:

  • Append an event for a particular entity
  • Get all events of a particular entity in order

An RDBMS is not ideal for this use case, because you have to query different tables (one for each event type, essentially) and then put them in the correct order on the client-side.

What I mean by Event Store is a pattern where each data mutation (INSERT / UPDATE / DELETE) is persisted as an INSERT into a suitable table of events. At the same time, hopefully in the same transaction, some piece of logic updates a state table with the result of applying the mutation.

The second sentence goes a bit beyond the basic idea of event sourcing. Conceptually, you never store any state - you load the ordered sequence of past events and use them to recreate the state every time. The events are not just logs, they are the source of truth for your application.

This can become a performance problem, if your entities accumulate very long event sequences - which is not the case in all applications. Storing "snapshots" of the state in periodic intervalls (or even every time) is one way to remedy this problem.

Relational databases are good at storing state, but even here they may not be necessary: typically, you have separate, independent snapshots of each entity without a lot of cross-referencing.

In some cases, it is useful to have a separate representation of your data for reading*. Here, you might want to query complex relationships between multiple different entities. In this situation, storing your state in an RDBMS could be advantagous, although there are also other possibilities - it just depends on the specific use case.

* see: CQRS

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.