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

On Sun, Nov 23, 2008 at 12:26 AM, Alvaro Herrera
<alvherre@commandprompt.com> wrote:
> Ciprian Dorin Craciun escribió:
>
>>     I've tested also Sqlite3 and it has the same behavior as
>> Postgres... Meaning at beginning it goes really nice 20k inserts,
>> drops to about 10k inserts, but after a few million records, the HDD
>> led starts to blink non-stop, and then it drops to unde 1k....
>
> The problem is, most likely, on updating the indexes.  Heap inserts
> should always take more or less the same time, but index insertion
> requires walking down the index struct for each insert, and the path to
> walk gets larger the more data you have.
>
> Postgres does not have bulk index insert, which could perhaps get you a
> huge performance improvement.
>
> --
> Alvaro Herrera                                http://www.CommandPrompt.com/
> The PostgreSQL Company - Command Prompt, Inc.

    I don't think the index depth is a problem. For example in the
case of BerkeleyDB with BTree storage, the tree height is 3 after 100m
inserts... So this is not the problem.

    I think the problem is that after a certain amount of data,
perdicaly the entire index is touched, and in this case the HDD
becomes a bottleneck... (Demonstrated by the vmstat 1 output I've put
in a previous email.)

    Ciprian.