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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
