I have data that fits an entity-attribute-value structure but with very few entries (same attributes but different values) per entity.

The main characterizations of my data are as follows:

  • ~700 different entity types, but only 3-4 entries for each type.
  • ~1400 attributes, but only 2 including the id are used for every type.
  • ~75% of the attributes are unique to a type, but a substantial number are used for many types.
  • The data will be very static, very rarely requiring changes.
  • Queries will primarily consist of filtering on attributes.

Based on my use-case and the fact that there will only be a few thousand entries total, my initial thought is to use a single table inheritance model. That said, the sparsity of the table leaves me wondering if jsonb or hstore or something else I haven't thought of might be a better solution.

I'm using Postgres 9.5

  • Sounds like a good fit for a hstore column. – a_horse_with_no_name Sep 23 '16 at 5:50
  • Or a jsonb one, and then you already answered your question :) I think, jsonb has a richer set of functionality around it that hstore, so I'd possibly use the former. – dezso Sep 23 '16 at 8:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.