Re: hstore for handling large amounts of events?

Johannes Staffans <johannes.staffans@mysema.com> wrote:

> My use case is recording a large-ish amount of timestamped, single-value
> events which are continuously pushed to the server from outside sources.
> The structure of one event is (event_id, source_id, timestamp, value). I
> also have to provide reporting capabilities (e.g. display reports that
> accumulate values over a given period of time). I never want to edit the
> events after they have been recorded.

Is an event_id unique?  If so, you have your event table defined
above, it seems.

> I've always thought that some kind of NoSQL-ish solution would be well
> suited for this,

It seems like it would be easy to store into a NoSQL product, but I
suspect that you will have more powerful reporting capabilities in
a relational database than a NoSQL approach.

> but having read a bit (e.g. [1]), I've started
> wondering. Do you think hstore would be good for this and if so, why? Am
> I better off with a more traditional solution?

I don't quite see where hstore fits with what you describe above.

> The scale of it all is about 2M events/month. According to my
> calculations, that should mean about 45 Mb of data/month, which is not
> really that much.

Yeah, that's pretty small.  It should be pretty easy to get good
performance.

--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company