I’ll be the mug: what’s an “event sourcing” database engine? Is it like a time-series database engine? Is it like a message bus with persistence? The demo shows it storing a key-value pair, which is hard to relate to “events”.
Event sourcing (the architectural pattern) means storing a ‘current state’ record when something changes.
This later lets you answer ad-hoc questions like ‘How many people delete their account and later recreated it?’ with a straightforward query (typically SQL), which encourages people to do more digging than they would otherwise.
Do you know if anyone has written up how to represent events in a traditional relational database like PostgreSQL? In particular, when there are N events and each event might have different data associated with it. The “obvious” answer I can think of is to create a new table for each event type, but reading all events from some time T then requires a join for every event type and the number of columns returned by said query is the union of all distinct fields in all event types. This has led me down paths like, “just use one table and put the event data in a JSONB column,” but such things feel unsatisfying.
Phrased differently: what’s the canonical way to represent a sum type in a relational database with no explicit support for sum types?
I don’t think there is a canonical approach to handling typed unions in Postgres because SQL does not have any notion of typed unions. However, I did some research on it for the purpose of representing event data, and came up with an approach with a view with columns that contain rows of the tables that participate in the union.
Only one such column is ever non-null. This approach preserves all type information.
There is support for referencing the variant type as a table and propagating primary key changes back and forth between the table representing the variant as a whole and the individual variant tables.
There’s a few workable ways. I’ve seen some variant of the JSONB approach a few times, usually with ‘common’ fields given their own columns - eg an user_id, an event_type enum, a subject_id and a timestamp.
I’ll be the mug: what’s an “event sourcing” database engine? Is it like a time-series database engine? Is it like a message bus with persistence? The demo shows it storing a key-value pair, which is hard to relate to “events”.
Event sourcing (the architectural pattern) means storing a ‘current state’ record when something changes.
This later lets you answer ad-hoc questions like ‘How many people delete their account and later recreated it?’ with a straightforward query (typically SQL), which encourages people to do more digging than they would otherwise.
Do you know if anyone has written up how to represent events in a traditional relational database like PostgreSQL? In particular, when there are N events and each event might have different data associated with it. The “obvious” answer I can think of is to create a new table for each event type, but reading all events from some time
Tthen requires a join for every event type and the number of columns returned by said query is the union of all distinct fields in all event types. This has led me down paths like, “just use one table and put the event data in aJSONBcolumn,” but such things feel unsatisfying.Phrased differently: what’s the canonical way to represent a sum type in a relational database with no explicit support for sum types?
I don’t think there is a canonical approach to handling typed unions in Postgres because SQL does not have any notion of typed unions. However, I did some research on it for the purpose of representing event data, and came up with an approach with a view with columns that contain rows of the tables that participate in the union.
https://github.com/solidsnack/pg-sql-variants
https://thelyfsoshort.io/variant-types-in-postgresql-c63725f8678b
Only one such column is ever non-null. This approach preserves all type information.
There is support for referencing the variant type as a table and propagating primary key changes back and forth between the table representing the variant as a whole and the individual variant tables.
There’s a few workable ways. I’ve seen some variant of the
JSONBapproach a few times, usually with ‘common’ fields given their own columns - eg anuser_id, anevent_typeenum, asubject_idand a timestamp.