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

On Sun, Nov 23, 2008 at 3:09 AM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> wrote:
> On Sat, Nov 22, 2008 at 5:54 PM, Scara Maccai <m_lists(at)yahoo(dot)it> wrote:
>> Since you always need the timestamp in your selects, have you tried indexing only the timestamp field?
>> Your selects would be slower, but since client and sensor don't have that many distinct values compared to the number of rows you are inserting maybe the difference in selects would not be that huge.
>
> Even better might be partitioning on the timestamp. IF all access is
> in a certain timestamp range it's usually a big win, especially
> because he can move to a new table every hour / day / week or whatever
> and merge the old one into a big "old data" table.
>
> --
> 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

Yes, If i would speed the inserts tremendously... I've tested it
and the insert speed is somewhere at 200k->100k.

But unfortunately the query speed is not good at all because most
queries are for a specific client (and sensor) in a given time
range...

Ciprian Craciun.