Re: Managing Key Value tags on rows

On Wed, Nov 12, 2014 at 7:17 PM, Tim Uckun <timuckun(at)gmail(dot)com> wrote:
> What is the most efficient way to model key value tags on records. The keys
> and values will be variable and changing over time. The values may be of
> various types (numbers, dates, strings etc). There will be filters and
> group by selects based on tag names and maybe even tag values.
>
> Postgres gives me the option of using hstore, JSON or just a linked table
> with all the keys and values and I am wondering which would be most
> efficient in terms of SELECT speeds. The writes are done in batches so this
> is mostly for analytical purposes.

For pre-9.4 you should be contrasting hstore to EAV model, where each
key value pair is stored in its own record. Text mode json (that is,
not jsonb) is generally not a good choice for arbitrary searching.

If you'll permit a gross generalization, if the data is mostly static
hstore should work pretty well otherwise you may want to use the 'old
school' wasteful but flexible EAV mechanic, double so if you have to
store other describing criteria than the type itself.

9.4 and above, I'd be looking pretty intently at jsonb with its
fancier searching options, better type handling, and ability to store
complicated structures for this type of work. Frequent updates will
still be a pain point however especially if there are a large number
of keys per object.

merlin