Comment by alganet

Comment by alganet a day ago

*_at and *_by fields in SQL are just denormalization + pruning patterns consolidated, right?

Do the long walk:

Make the schema fully auditable (one record per edit) and the tables normalized (it will feel weird). Then suffer with it, discover that normalization leads to performance decrease.

Then discover that pruned auditing records is a good middle ground. Just the last edit and by whom is often enough (ominous foreshadowing).

Fail miserably by discovering that a single missing auditing record can cost a lot.

Blame database engines for making you choose. Adopt an experimental database with full auditing history. Maybe do incremental backups. Maybe both, since you have grown paranoid by now.

Discover that it is not enough again. Find that no silver bullet exists for auditing.

Now you can make a conscious choice about it. Then you won't need acronyms to remember stuff!

lud_lite a day ago

Another option is audit info could go to another table or datastore entirely.

If you never use it, that data can be dumped to s3 glacier periodically (e.g. after 90 days).

By losing the foreign key you gain flexibility in what you audit. Maybe audit the operation and not the 20 writes it causes.

Reply View 2 replies

yes_man a day ago

Fair enough, but now your application is relying on 100% uptime of AWS and S3 and no network failures in between. And what happens if your transaction goes through, but the request to AWS doesn’t? What happens if another operation mutates the target meanwhile before you can retry with current state? Your app is also slowing down since it needs to send the events to S3 and guarantee they got there. Now you are reinventing two-stage commits. Unless you aren’t actually making an audit log and don’t care if events are guaranteed to be logged?
So like OP said, no silver bullets exist for auditing.

Reply View | 1 reply
- lud_lite 19 hours ago
  
  Correct. This is a system design problem. You want this to be transactional and work at scale? That might be hard to achieve. Maybe if the data can be partioned then each node handles its own auditing in a table ad part of the transaction. There are many possibilities. Allowing inconsistently might be OK too depending on what is required.
  
  Reply View | 0 replies

klysm a day ago

My 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

Reply View 5 replies

datadrivenangel a day ago

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.

Reply View | 1 reply
- klysm a day ago
  
  Really depends on the app. If you have a low throughput line-of-business kind of application you can probably get away with storing everything.
  
  Reply View | 0 replies
alganet a day ago

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.

Reply View | 1 reply
- klysm a day ago
  
  Yeah 100% giving up on pure SQL to solve the problem, mainly from the perspective that doing full versioning etc. in SQL is really damn hard.
  
  Reply View | 0 replies
globular-toast a day ago

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.

Reply View | 0 replies

awesome_dude a day ago

But wait, there's Event Driven Architectures and Event Sourcing, meaning that the events are your log of edits!

Reply View 2 replies

alganet a day ago

Doesn't that also falls on the "blame the database engines and go for an experimental solution"?
I'm not saying databases are blameless. It's just that experiencing the issues they have by yourself is rewarding!
There is also a walk before the long walk of databases. Store things in text files and use basic tools (cat, sed, sh...).
The event driven stuff (like Kafka) reminds me of that. I am not very familiar with it though, just played a little bit with it once or twice.

Reply View | 1 reply
- awesome_dude a day ago
  
  Kind of, the WAL in postgres is effectively an event log, and many people keep replicas of it for backup reasons, which is auditable, kind of meaning that an EDA/Event source is just a shinier version of that?
  
  Reply View | 0 replies