Storing sensor data

From: Ivan Voras
Subject: Storing sensor data
Date: ,
Msg-id: gvm1ht$8c3$1@ger.gmane.org
(view: Whole thread, Raw)
Responses: Re: Storing sensor data  (Heikki Linnakangas)
Re: Storing sensor data  (Alexander Staubo)
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, )

Hi,

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.

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.

I think the second design would be easiest on the database but as the
current sensor state can potentially be queried often, it might be too
slow to read.

Any recommendations?


Attachment

pgsql-performance by date:

From: Fabrix
Date:
Subject: Scalability in postgres
From: Greg Jaman
Date:
Subject: Re: Storing sensor data