Re: Using Postgres to store high volume streams of sensor readings

On Fri, Nov 21, 2008 at 8:41 PM, Greg Smith <gsmith(at)gregsmith(dot)com> wrote:
> On Fri, 21 Nov 2008, Sam Mason wrote:
>
>> It's not quite what you're asking for; but have you checked out any
>> of the databases that have resulted from the StreamSQL research?
>
> A streaming database approach is in fact ideally suited to handling this
> particular problem. Looking at the original request here:
>
>> * for a given client (and sensor), and time interval, I need the min, max,
>> and avg of the values;
>> * for a given time interval (and sensor), I need min, max, and avg of the
>> values;
>
> The most efficient way possible to compute these queries is to buffer the
> full interval worth of data in memory as the values are being inserted,
> compute these aggregates once the time window for the interval has ended,
> then write a summarized version of the data. Doing that sort of thing and
> then operating on the aggregated data, rather than maintaining a bulky index
> covering every single row, is exactly the sort of thing a good streaming
> database would handle for you.
>
> I can't comment on the current state of Borealis. But as the original focus
> of the streaming database research that spawned Truviso where I work was
> accelerating data capture from sensor networks, I know this general style of
> approach is quite beneficial here. For example,
> http://telegraph.cs.berkeley.edu/telegraphcq/v2.1/ is an open-source
> implementation from that research that's integrated with an older version of
> PostgreSQL. If you look at the "windowed aggregates" example there, it
> shows what a streaming query similar to the requirements here would look
> like: an average and other statistics produced on a per-interval basis.
>
> --
> * Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

Thank you for your pointers, I'll check them out...

I'm aware of the fact that the problem could be solved in other
more efficient ways (for example as you've said, by aggregating the
data in memory and flushing only the aggregates), but as I've said in
the beginning, I also want to test different storage systems in such a
situation...

Ciprian Craciun.