Perf of query of large table by date? 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.

Thanks!
We do 500 to 3000 inserts per second on a table, but in partitions of 1 month, and are monitoring the last 2 hours of activity. An index on the timestamp does the trick, most queries run less than a few milliseconds on our hardware.

But, we do have fast storage, many GB's of RAM and descent processors. On a single 7200rpm disk, this would be impossible.

Use EXPLAIN ANALYZE to see how your queries are executed and try to find the bottleneck. Storage is the slowest part of your system, and databases love lots of RAM.