alt Hacker NewsMy current state is have the database be the current state and use logical replication (CDC) to keep the log of changes in case you need it
Replies
The problem with this is the audit log is only at the CRUD level which is often too low. Ambiguities can arise. For example if the question is "who published the article" do you look for a create or do you look for an update with published=true? It's even worse when you consider the schema can change over time, so both can be correct but at different points in time. Event sourcing is the way if you want to capture business-level events.
If you see it from the pure SQL point of view, you are in the "blame database engines and adopt an experimental solution".
It is the point where you give up modeling the audit as part of the systems tables.
The drawbacks of this choice are often related to retrieval. It depends on the engine.
I once maintained a system that kept a fully working log replicated instance delayed by 24h, ready for retrieval queries, in addition to regular disk backups (slow costy retrieval).
I am more developer than DBA, so I can probably speak more about modeling solutions than infra-centric solutions.
It is interesting thinking about record changes as a spectrum towards application logs. At some point too much detail is expensive to store, and you must adopt an archival strategy.