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

Storing sensor data

From: Ivan Voras <ivoras(at)freebsd(dot)org>
To: pgsql-performance(at)postgresql(dot)org
Subject: Storing sensor data
Date: 2009-05-28 12:54:27
Message-ID: gvm1ht$8c3$1@ger.gmane.org (view raw or flat)
Thread:
Lists: pgsql-performance
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?

Responses

pgsql-performance by date

Next:From: Heikki LinnakangasDate: 2009-05-28 13:31:17
Subject: Re: Storing sensor data
Previous:From: Alan McKayDate: 2009-05-27 19:58:00
Subject: Re: [PERFORM] Postgres Clustering

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