>>> 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?
BTW, "pure SQL" approach here is to use separate tables: Tags(TagId, TagName) and TagValues(EventId,TagId,Value).
But in this case it will be painful to filter events by tag values directly, so only separate denormalized OLAP table
shouldbe used in "pure SQL":)
PostgreSQL, however, supports key-value based hstore.
>> You may also normalize it (move hosts and categories to separate table).
>
>Why? These form part of the primary key for the event...
Host and category could be part of PK, but it may be better to have "HostId" field and "Hosts(HostId, Host)" table than
"text"field with many "www.foo.bar" values)
>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
Hstore supports GIN and GIST (https://www.postgresql.org/docs/9.1/static/textsearch-indexes.html). I'd start with GIN.
Do you need real-time data or, say, one-day old data is ok? In latter case it is better to load data to denormalized
tableto speed-up queries and use no index on normalized(OLTP) table because index update operation is not free)
Ilya Kazakevich
JetBrains
http://www.jetbrains.com
The Drive to Develop
