Skip site navigation (1) Skip section navigation (2)

Re: Storing sensor data

From: Greg Jaman <gjaman(at)gmail(dot)com>
To: Ivan Voras <ivoras(at)freebsd(dot)org>
Cc: Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Storing sensor data
Date: 2009-05-28 18:56:37
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance
I currently have a database doing something very similar.  I setup partition
tables with predictable names based on the the data's timestamp week number
eg:  (Data_YYYY_WI).

I have a tigger on the parent partition table to redirect data to the
correct partition( tablename:='Data_' || to_char('$NEW(ts)'::timestamptz,
'IYYY_IW') ) .  then I use dynamic sql to do the insert.  I did some
optimization by writting it in pl/TCL and using global variables to store
prepared insert statements.

Most queries for me are based  on the date and we have decent performance
with our current setup.  For last/current sensor data we just store the last
dataID in the sensor record.  I haven't thought of a better way yet.  After
batch inserts we caculate the last reading for each participating sensorID

With partition tables we struggled with the query to get the lastest data :
select * from "Data" where "sensorID"=x order by ts limit 1  -- for parition
tables.   See (

On Thu, May 28, 2009 at 7:55 AM, Ivan Voras <ivoras(at)freebsd(dot)org> wrote:

> 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.
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:

In response to


pgsql-performance by date

Next:From: Greg JamanDate: 2009-05-28 19:38:41
Subject: Re: Storing sensor data
Previous:From: FabrixDate: 2009-05-28 18:50:56
Subject: Scalability in postgres

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group