3 ms·

This is right. I'm a big fan of this sort of embedded audit metadata wherever it makes sense. I do wonder when doing stuff like this though, if this really sho

This is right. I'm a big fan of this sort of embedded audit metadata wherever it makes sense. I do wonder when doing stuff like this though, if this really shouldn't be something that the database gives you for free. I read a few years ago about 'fact based' event stream style databases which store your data as a stream of time ordered ops that can later serve as an audit log, but can be used for even more powerful things such as backups at any point in time, debugging at any point in time, etc. For practical reasons (i.e. just picking a standard postgres setup to get stuff done) I've never dug into any of these systems or played around with them. Anyone know what the latest and greatest is here? Is there anything I can install on top of postgres to give me this functionality today?

terhechte 6y ago
I think Datomic[1] works like that Here's the relevant quote: "Understand how and when changes were made. Datomic stores all history, and lets you query against any point in time. Learn More" [1] https://www.datomic.com/ https://www.datomic.com/
oftenwrong 6y ago
This is often called "event sourcing". The other commenter mentions Datomic, which probably is the 'latest and greatest' form of event sourcing (I've never used it). https://vvvvalvalval.github.io/posts/2018-11-12-datomic-event-sourcing-without-the-hassle.html https://vvvvalvalval.github.io/posts/2018-11-12-datomic-even... If you want something built on postgres, I don't have any specific recommendations, but you can build a simple event sourcing system yourself. I worked somewhere that used event sourcing on postgres, and the core log was basically just a table with aggregate IDs, event sequence numbers, and a JSONB column for the event payload. I recommend starting with just one part of your application if you're going to adopt event sourcing. You will quickly find that there are a lot of new considerations and pitfalls that you don't have with traditional RDBMS usage. Overall, event sourcing is hard to get right, so you should consider the trade-offs carefully. There are easier ways to get audit logs, for example: https://www.pgaudit.org/ https://www.pgaudit.org/ I will also recommend this as a way to start to understand the tricky aspects of event sourcing: https://leanpub.com/esversioning https://leanpub.com/esversioning
skybrian 6y ago
I’ve been following the progress of Dolt [1] which is a SQL database that works like git. This would give you modification history in a similar way to git. That’s different from recording when events happened, though (and changing your mind about when they happened), so you’ll still need timestamps for that. [1] https://github.com/dolthub/dolt https://github.com/dolthub/dolt

terhechte 6y ago

I think Datomic[1] works like that Here's the relevant quote: "Understand how and when changes were made. Datomic stores all history, and lets you query against any point in time. Learn More" [1] https://www.datomic.com/ https://www.datomic.com/

oftenwrong 6y ago

This is often called "event sourcing". The other commenter mentions Datomic, which probably is the 'latest and greatest' form of event sourcing (I've never used it). https://vvvvalvalval.github.io/posts/2018-11-12-datomic-event-sourcing-without-the-hassle.html https://vvvvalvalval.github.io/posts/2018-11-12-datomic-even... If you want something built on postgres, I don't have any specific recommendations, but you can build a simple event sourcing system yourself. I worked somewhere that used event sourcing on postgres, and the core log was basically just a table with aggregate IDs, event sequence numbers, and a JSONB column for the event payload. I recommend starting with just one part of your application if you're going to adopt event sourcing. You will quickly find that there are a lot of new considerations and pitfalls that you don't have with traditional RDBMS usage. Overall, event sourcing is hard to get right, so you should consider the trade-offs carefully. There are easier ways to get audit logs, for example: https://www.pgaudit.org/ https://www.pgaudit.org/ I will also recommend this as a way to start to understand the tricky aspects of event sourcing: https://leanpub.com/esversioning https://leanpub.com/esversioning

skybrian 6y ago

I’ve been following the progress of Dolt [1] which is a SQL database that works like git. This would give you modification history in a similar way to git. That’s different from recording when events happened, though (and changing your mind about when they happened), so you’ll still need timestamps for that. [1] https://github.com/dolthub/dolt https://github.com/dolthub/dolt