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

On Sat, Nov 22, 2008 at 8:04 PM, Shane Ambler <pgsql@sheeky.biz> wrote:
> Ciprian Dorin Craciun wrote:
>
>>
>>    I would try it if I would know that it could handle the load... Do
>> you have some info about this? Any pointers about the configuration
>> issues?
>>
>>    Ciprian.
>>
>
>
> Apart from the configure options at build time you should read -
> http://www.sqlite.org/pragma.html
>
> It was a few versions ago so may be changed by now, but I reckon it
> was the temp_store setting - which is described as temp tables and
> indexes but is (or was) also used for large query and sorting needs.
> Setting this to memory did make a difference for some queries.
>
>
> --
>
> Shane Ambler
> pgSQL (at) Sheeky (dot) Biz
>
> Get Sheeky @ http://Sheeky.Biz

    Hello all!

    (This email now is about Sqlite3, but it also relates to Postgres
as a coparison.)

    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....

    I've used exactly the same schema as for Postgres, and the
following pragmas:
    * page_size = 8192;
    * fullsync = 0;
    * synchronous = off;
    * journal_mode = off; (this has a 10 fold impact... from 1k
inserts at the beginning to 10 or 20k...)
    * cache_size = 65536; (this is in pages, and it results at 512MB,
but I don't see the memory being used during inserts...)
    * auto_vacuum = none;
    * analyze at the end of the inserts;

    So I would conclude that relational stores will not make it for
this use case...

    I'll rerun the tests tomorrow and post a comparison between SQLite
and Postgres.

    Ciprian Craciun.