I need to audit log all events that are happening in our application. These audit logs are stored in a table. One column in the table in of BLOB type.
So, if I insert audit log entries into the table real-time, that would affect the performance of the original event. What approach can I take to make this insertion operation asynchronous?
Some databases have built-in audit functionality, which usually performs quite well, I'd suggest to check these possibilities first.
Inserting the audit log records in a separate thread seems like a viable solution to me. You should be just aware that you're losing atomicity - there is a small but unavoidable risk that your main transaction will succeed while the auditing transaction will fail. If this is unacceptable to you, your options are limited - either use built in auditing facility of your database, or insert the audit logs as part of your main transaction. (My opinion is that you indeed do not want to lose atomicity - an audit trail that is not guaranteed to cover all operations is of very limited use.)
Another question is why do you want to insert a BLOB as part of the audit log. If the blob contains encoded auditing information, you won't be able to query the audit log outside your application, which looks like a substantial limitation of the audit trail.
Joined: May 14, 2009
Intention is to store the snapshot of entities state before and after the event. If the event acts on multiple entities, this detail could be quite big.
Gajen Logandan wrote:Intention is to store the snapshot of entities state before and after the event. If the event acts on multiple entities, this detail could be quite big.
Again, some databases might have built-in support for this. For example, Oracle has a Flashback data archive (sometimes referred to as Total recall), which allows you to read data as they looked like at an arbitrary point in time in the past. If you're on Oracle, this would definitely be something to look closer at.