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

On Sat, Nov 22, 2008 at 8:04 PM, Shane Ambler <pgsql(at)sheeky(dot)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.