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

On Sun, Nov 23, 2008 at 3:28 PM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> * Ciprian Dorin Craciun (ciprian(dot)craciun(at)gmail(dot)com) wrote:
>> > 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.
>>
>> 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...
>
> Have you set up your partitions correctly (eg, with appropriate CHECK
> constraints and with constraint_exclusion turned on)? Also, you'd want
> to keep your indexes on the individual partitions, of course.. That
> should improve query time quite a bit since it should only be hitting
> the partitions where the data might be.
>
> Stephen
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.9 (GNU/Linux)
>
> iEYEARECAAYFAkkpWpEACgkQrzgMPqB3kihvyACgm6ITdkodTqZvDLCjqavj9lkR
> w1oAnRrB1rbW+bF6Spr77VcH5/Mty4S6
> =G7aX
> -----END PGP SIGNATURE-----

Well, now that I've read the previous two emails better, I
understand what Scot and Stephen are talking about...

So if I understood it correctly: I should build indexes only for
certain parts of the data (like previous full hour and so). But I see
a problem: wouldn't this lead to a lot of indices beeing created (24 /
hour, ~150 / week, ...)?

Another question: wouldn't the index creation impact the insertion
and query speed during they are created?

Either case I don't think this is a very easy to implement solution...

Ciprian Craciun.