Re: Managing Key Value tags on rows

My Main worry is that the tag values will be of different types and ideally
I would be able to search for using type specific ranges. For example if
the tag value is a date then be able to do a date interval search but if
the tag values are strings then do an ilike search.

I was thinking of creating different columns for different types so that I
can do a search like 'WHERE tag_name = 'blah' and date_value between ....'
. In other words I would have a string_value, integer_value,
numeric_value, date_value ... columns.

On Tue, Nov 18, 2014 at 5:40 AM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:

> 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
>