We use RavenDB as our data store for transactional information, and it works well for data that is currently “in use”.
We also collect audit events as people use our application, where each event looks something like:
- Type, date, summary, etc. – pretty standard
- Related document IDs (could be one, could be 5)
As a document store, Raven does a great job of indexing these so that we can ask questions like “find all events related to document XYZ”.
Unfortunately as the data builds up over time, these audit events can account for 90% of the data stored in our database – and it’s rarely used. We’d like to get this data out of Raven, and into some kind of storage that might be better suited.
What other storage solutions exist for storing hundreds of thousands of audit-type events? We’re happy to sacrifice speed to save storage and indexing costs.
SQLite + JSON.NET
Using SQLite provides fast, robust, indexed storage, while JSON.NET maintains compatibility between model types in archive storage and Raven. Deserialize from Raven, re-serialize, store in SQLite.
You can use SQLite columns and indexes to speed up access by selecting before parsing JSON.
Henrik (Andersson) pointed out there’s a .NET wrapper for SQLite used in Akavache: https://github.com/akavache/Akavache/blob/akavache4-master/Akavache.Sqlite3/SQLite.cs
SQLite definitely doesn’t seem excessively limited for this: http://sqlite.org/limits.html
I used to work in a project which has audit capability for all SQL tables. However, the audit data is much smaller than the actual data because main data is rarely changed.
For your particular case and usage, I have seen people do one thing that may be appropriate to your situation. What they did is they moved part of the archived data (auditing data in this case) into either a new DB or a flat file. The audit system only supports auditing recent data.
For the archived audit data, you can either set up in a new DB which has the benefit of ease of use OR you can setup your own data-diff storing facility and store in flat file manner with indexed access.