I have a table with some sensor data in it which grows every minute with a new measurement for that specific sensor. Something like:
entry_id (pk), sensor_id (fk), measurement_value, created_at
I need to perform some analytics on this data, at hour intervals. Basically the query will look something like:
SELECT measurement_value FROM above_table WHERE sensor_id = x AND created_at BETWEEN time_a AND (time_a + 1 hour)
1) First of all, I'm wondering if this design is reasonable long term. Each sensor will rack up 500k readings x year. If I have enough sensors, the table will get real sizeable real fast. I'd like analytics to be pretty snappy and not take longer than a second, so perhaps I'll need to be pre-computing these results every few insertions somehow. Alternatively I perhaps be looking for NoSQL solutions, or would that not really address the core issue?
2) What's the best performance I can get for querying that table? I'm thinking that an index on (sensor_id, created_at) would be hard to beat.