On 16/08/2016 15:10, Ilya Kazakevich wrote:
>>>> 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).
Well, maybe, but none of us wants to do that ;-)
> 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":)
I don't understand the second half of this I'm afraid...
> PostgreSQL, however, supports key-value based hstore.
Right, but hstore only allows single values for each key, if I
understand correctly?
Okay, so that leaves me with a jsonb "tags" column with a gin index, but
I still have a couple of choices..
So, in order to best answer these types of queries:
>> - 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
...which of the following is going to be most performant:
# SELECT '{"tag1":["v1", "v2", "v3"]}'::jsonb @> '{"tag1": ["v1"]}'::jsonb;
?column?
----------
t
(1 row)
# SELECT '[{"tag1":"v1"}, {"tag1": "v2"}, {"tag1": "v3"}]'::jsonb @>
'[{"tag1": "v1"}]'::jsonb;
?column?
----------
t
(1 row)
So, should I go for a tag name that maps to a list of values for that
tag, or should I go for a sequence of one-entry mappings of tag name to
tag value?
cheers,
Chris