What would be a good solution to storing change history of objects?

Question

Need to keep track of changes done to objects in database.

Trivial implementation would be to have mirror table that gets records inserted into it by triggers, either inside database or within application, but that affects performance and over time mirror database gets huge and essentially doubles maintenance time when original table has to be altered(mirror table needs to reflect that change).

Since my biggest requirement here is to have minimal effect on database and application performance my current preference is to dump changes into syslog-ng over udp and store them in plain text files.

After all changelog is not something that will be accessed frequently so it's even ok to have it archived over time. But obviously with such setup actual accessing that data is quite tricky.

So i guess my question is - is there already a system that at least partially suites my needs? Perfect fit would be UDP-accessed schema-less append-only database system with possibility to automatically archive data(or at least minimal amount of configuration needed for doing that) or very slow degradation of insert performance. MongoDB? CouchDB? YourDB?

Answer 1

Well, there are lots of ways to approach this. I am most familiar with MongoDB and so would lean in that direction. In general I think it will meet your needs for performance, and using a replica set, with reads coming off the slaves would likely be the approach to take. However, the versioning is not built in. You can see one approach to versioning with Mongoid::Versioning here:

Mongoid::Versioning - how to check previous versions?

The other solutions you mentioned may have better native support, but I can't speak to that. Hopefully this at least gives you some pointers on the MongoDB side of things.

Answer 2

Have a look at mongoid history

It tracks the history of of changes like what, when, by whom along with version. Its also provided with configuration options