Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question
1  
I would seriously consider dropping the entry_id column and using sensor_id, created_at as the primary key. We have a table with a lot more columns that has hundreds of millions of rows and a three-column key; performance is fine with proper indexing. You definitely should also consider partitioning and other indexes. –  kgrittn Jun 17 '12 at 15:11
1  
A completely different solution would use RRDTool oss.oetiker.ch/rrdtool this is a tool that only keeps recent data at the full resolution and aggregates older data. So it can generate detailed graphs for recent data but only less detailed for older data. It is often used by monitoring tools like cacti and opennms. –  Eelke Jun 17 '12 at 16:31
    
kgrittn: that's a good point. It's there as default byproduct of Rails generating it for you. I remember ActiveRecord not supporting composite PKs that well off the shelf, so I'll need to investigate how easy it would be to achieve without breaking everything. –  glitch Jun 17 '12 at 21:56
    
Eelke: would I run into a situation where I'd really need something that only a RDBMS would offer, that rrdtool doesn't have? –  glitch Jun 17 '12 at 21:58
    
One thing I have noticed though is that I have found it easier to add an artificial candidate key where the primary key is composite. This makes joins easier. So I wouldn't suggest removing the entry_id. I would just suggest using it as an artificial candidate key, and still designating the primary key as you suggest. Otherwise joins become interesting since you have to specify in the joining table which sensor made the reading. –  Chris Travers Sep 9 '12 at 8:25
add comment

2 Answers

up vote 2 down vote accepted

Here's my suggestion, which is somewhat different. I call it "log, aggregate, and snapshot."

Basically all your sensor input is coming in. We can assume or even require that it gets into the database within a specified period of time (1 day?). At the close of each interval we go back one interval into the past and snapshot our analytic info. In this way we can start with a snapshot before where we need and roll forward.

For example we might do this in a financial app (not really the same thing but poses similar challenges over time):

CREATE TABLE gl ( 
   id bigserial not null unique,
   reference text primary key
   description text,
   date_posted date not null
);
CREATE TABLE gl_lines (
   entry_id bigserial,
   account_id int not null references account(id),
   gl_id bigint not null references gl(id),
   amount numeric
);
CREATE TABLE eoy_checkpoint (
   date_ending date,
   account_id int references account(id)
   running_balance numeric not null,
   debits numeric,
   credits numeric
   primary key (date_ending, account_id)
);

Then we can maintain eoy_checkpoint when we close our books. We can also have a trigger that denies entry into gl_lines where it occurs on or before the most recent date in eoy_checkpoint. This allows us to still do aggregate reporting, but we can use the checkpoint as a point we can roll forwards from, and thus manage how much data is actually being aggregated.

I think a similar approach would be usable in your case. It handles this sort of thing very well, and allows a mixture of OLTP and more complex queries to be run without too much impact. It isn't really clear how normalized this is since there is duplication of data, but the data duplication allows you to do things like purche historical data without impacting your current running totals.

share|improve this answer
add comment

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.

share|improve this answer
    
I wasn't very well aware of partitioning, thanks for the suggestion! –  glitch Jun 17 '12 at 22:06
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.