Re: Storing sensor data

From: Kenneth Marshall
Subject: Re: Storing sensor data
Date: ,
Msg-id: 20090528150102.GF18879@it.is.rice.edu
(view: Whole thread, Raw)
In response to: Re: Storing sensor data  (Ivan Voras)
Responses: Re: Storing sensor data  (Ivan Voras)
List: pgsql-performance

Tree view

Storing sensor data  (Ivan Voras, )
 Re: Storing sensor data  (Heikki Linnakangas, )
  Re: Storing sensor data  (Nikolas Everett, )
   Re: Storing sensor data  (Ivan Voras, )
  Re: Storing sensor data  (Ivan Voras, )
   Re: Storing sensor data  (Kenneth Marshall, )
    Re: Storing sensor data  (Ivan Voras, )
     Re: Storing sensor data  (Kenneth Marshall, )
   Re: Storing sensor data  (Greg Jaman, )
    Re: Storing sensor data  (Greg Jaman, )
 Re: Storing sensor data  (Alexander Staubo, )
  Re: Storing sensor data  (Ivan Voras, )
   Re: Storing sensor data  (Alexander Staubo, )
    Re: Storing sensor data  (Grzegorz Jaśkiewicz, )

On Thu, May 28, 2009 at 04:55:34PM +0200, Ivan Voras wrote:
> 2009/5/28 Heikki Linnakangas <>:
> > 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


pgsql-performance by date:

From: Scott Mead
Date:
Subject: Re: Scalability in postgres
From: Scott Marlowe
Date:
Subject: Re: Scalability in postgres