Re: Storing sensor data

2009/5/28 Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com>:
> Ivan Voras wrote:
>>
>> I need to store data about sensor readings. There is a known (but
>> configurable) number of sensors which can send update data at any time.
>> The "current" state needs to be kept but also all historical records.
>> I'm trying to decide between these two designs:
>>
>> 1) create a table for "current" data, one record for each sensor, update
>> this table when a sensor reading arrives, create a trigger that would
>> transfer old record data to a history table (of basically the same
>> structure)
>> 2) write only to the history table, use relatively complex queries or
>> outside-the-database magic to determine what the "current" values of the
>> sensors are.
>
> 3) write only to the history table, but have an INSERT trigger to update the
> table with "current" data. This has the same performance characteristics as
> 1, but let's you design your application like 2.

Excellent idea!

> I think I'd choose this approach (or 2), since it can handle out-of-order or
> delayed arrival of sensor readings gracefully (assuming they are timestamped
> at source).

It seems like your approach is currently the winner.

> If you go with 2, I'd recommend to still create a view to encapsulate the
> complex query for the current values, to make the application development
> simpler. And if it gets slow, you can easily swap the view with a table,
> updated with triggers or periodically, without changing the application.
>
>> The volume of sensor data is potentially huge, on the order of 500,000
>> updates per hour. Sensor data is few numeric(15,5) numbers.
>
> Whichever design you choose, you should also consider partitioning the data.

I'll look into it, but we'll first see if we can get away with
limiting the time the data needs to be available.