On Wed, Nov 12, 2014 at 7:17 PM, Tim Uckun <timuckun@xxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
