On Thu, May 28, 2009 at 04:55:34PM +0200, Ivan Voras wrote: > 2009/5/28 Heikki Linnakangas <heikki.linnakangas@xxxxxxxxxxxxxxxx>: > > 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. > Mr. Voras, One big benefit of partitioning is that you can prune old data with minimal impact to the running system. Doing a large bulk delete would be extremely I/O impacting without partion support. We use this for a DB log system and it allows us to simply truncate a day table instead of a delete -- much, much faster. Regards, Ken -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
