[LRUG] Using statistical tables with Rails

[Neil Middleton]

I'm building an app that needs to store a fair amount of events that the users carry out. (Think LOTS as in millions per month).

I need to report on the these events (total of type x in the last month, etc) and need something resilient and fast.

I've toyed with Redis etc to store aggregates of the data, but this could just mean that I'm building up a massive store of single figure aggregates that aren't rebuildable.

Whilst this isn't a bad solution, I'm looking at storing the raw event data in tables that I can then query on a needs basis, and potentially generate aggregate counters on a periodic basis. This would thus give me the ability to add counters over time, and also carry out ad-hoc inspections on what is going on, something which aggregates don't allow.

Question is, how is best to do this? I obviously don't want to have to create a model for each table (which is what Rails would prefer), so do I just create the tables and interact with raw SQL on a needs basis, or is there some other choice for dealing with this sort of data?

It would be interesting to know what thoughts you guys have.

Cheers

Neil