Re: Storing sensor data

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.

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).

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.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com