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

From: "Ciprian Dorin Craciun" <ciprian(dot)craciun(at)gmail(dot)com>
To: "Alvaro Herrera" <alvherre(at)commandprompt(dot)com>
Cc: "Michal Szymanski" <dyrex(at)poczta(dot)onet(dot)pl>, pgsql-general(at)postgresql(dot)org
Subject: Re: Using Postgres to store high volume streams of sensor readings
Date: 2008-11-23 06:31:37
Message-ID: 8e04b5820811222231o14569660x2c41a392ea4e1e33@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On Sun, Nov 23, 2008 at 12:32 AM, Alvaro Herrera
<alvherre(at)commandprompt(dot)com> wrote:
>
>> On 21 Lis, 13:50, ciprian(dot)crac(dot)(dot)(dot)(at)gmail(dot)com ("Ciprian Dorin Craciun")
>> wrote:
>
>> > What have I observed / tried:
>> > * I've tested without the primary key and the index, and the
>> > results were the best for inserts (600k inserts / s), but the
>> > readings, worked extremly slow (due to the lack of indexing);
>> > * with only the index (or only the primary key) the insert rate is
>> > good at start (for the first 2 million readings), but then drops to
>> > about 200 inserts / s;
>
> I didn't read the thread so I don't know if this was suggested already:
> bulk index creation is a lot faster than retail index inserts. Maybe
> one thing you could try is to have an unindexed table to do the inserts,
> and a separate table that you periodically truncate, refill with the
> contents from the other table, then create index. Two main problems: 1.
> querying during the truncate/refill/reindex process (you can solve it by
> having a second table that you "rename in place"); 2. the query table is
> almost always out of date.
>
> --
> Alvaro Herrera http://www.CommandPrompt.com/
> The PostgreSQL Company - Command Prompt, Inc.
>
> --
> 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

The concerts you have listed are very important to me... I will
use the database not only for archival and offline analysis, but also
for realtime queries (like what is the power consumption in the last
minute)...

Of course I could use Postgres only for archival like you've said,
and some other solution for realtime queries, but this adds complexity
to the application...

Thanks,
Ciprian Craciun.

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Ciprian Dorin Craciun 2008-11-23 06:34:57 Re: Using Postgres to store high volume streams of sensor readings
Previous Message Ciprian Dorin Craciun 2008-11-23 06:28:37 Re: Using Postgres to store high volume streams of sensor readings