happy to answer any questions
happy to answer any questions
I've built audit systems in the past which track just the new values, with the idea being that you can replay the audit log for a particular record from the beginning to reconstruct its state. I'm not convinced that's a better approach than yours here though, just interested in your thinking.
That's def a valid concern if you're auditing a high frequency insert/update table, or have your entire database under audit. If you do have those needs a system that logs outside of postgres (like pgaudit) would be a better fit.
In my experience most startups and mid-size enterprises sprinkle in auditing around the sensitive parts of the DB, like `account` or `access_control` tables where writes load isn't much of a concern.
> I've built audit systems in the past which track just the new values
Yeah, that solution works great! The one place it breaks down is if you apply auditing to an existing table, and need to be able to recover the records from when auditing was enabled (initial state is not snapshotted)
We toyed with another approach to avoiding having to track old_record by first checking to see if the `record_id` exists in the audit table, and then inserting a row with the OLD values with a `SNAPSHOT` `operation` if it does not.
Even though that query/check was operating on an index, the performance overhead was higher than we were comfortable with.
Storage is pretty cheap these days so we opted to optimize for write throughput + reduced memory usage rather than any considerations wrt disk.
> Even though that query/check was operating on an index, the performance overhead was higher than we were comfortable with.
Was this done inside the trigger function? Couldn't you just insert the SNAPSHOT values first before applying the trigger? Seems wasteful to me to track both NEW and OLD for all subsequent operations just to ensure the initial value is covered.
[0] http://8kb.co.uk/blog/2015/01/19/copying-pavel-stehules-simp...
For sure! There is a conversion penalty that is paid incrementally (at write time). For paying that penalty, jsonb gets you reduced storage size and dramatically faster column value extraction with `->` or `->>` if you end up having to filter the audit table by something other than the `record_id`
It is a tradeoff though and depending on the specifics of your use-case JSON could be a better choice.
How well does this work when you audit multiple tables with foreign key relationships between them?
If the user references a customer, and you want to find out what customer the user belonged to at time X (but the customer might have been deleted in the mean time), you have to track through the history of multiple tables to find answers. More if you have n2m relationships...
It sounds doable, but not trivial to get right for more complicated models.
---
Update: to clarify, I want to be able to present a somewhat homan-readable history of an object without doing much table-specific logic.
If an author can publish many books, you typically have an n2m mapping table, like book_authors. If you want to add newly published books to the "author" history view, you have to determine than you have to include parts of the history of the book_authors table. I don't think you can do that through a join though? Trying to think my way through this...
Maybe this can be dealt with easier if you can assume each table has a non-composite primary key, and include that primary key in the history table, and then you can do joins more easily on the history?
For that scenario replicating audit data to a OLAP system may be a better fit
But how could you go about not storing the diff but still being able to select it?
If you have the HSTORE extension, you can also use it to get a diff of the old and new: `HSTORE(OLD) - HSTORE(NEW)` and vice versa, very nice to easily have the deltas.
Long story short: it is avoidable if you're okay with paying the penalty of querying the audit table by `record_id` before every write. We decided to use more disk and keep the higher write throughput