Re: schema advice for event stream with tagging and filtering

On 16/08/2016 14:29, Ilya Kazakevich wrote:
> Hi,
>
>> An event is a row with a primary key along the lines of (colo, host,
>> category) and an associated set of tags, where each tag has a type and a value
>> (eg: {"color": "red", "owner": "fred", "status": "open"...}).
>
> What about simple table with several columns and hstore field for tags?

Interesting!

> You may also normalize it (move hosts and categories to separate table).

Why? These form part of the primary key for the event...

> indexes should help you with fast filtering, or you may load data from this table to denormalized olap table once a day and build index there to speed-up queries.

What kind of index is recommended here? The kind of queries would be:

- show me a list of tag types and the count of the number of events of
that type

- show me all events that have tag1=x, tag2=y and does not have tag3

cheers,

Chris