I am trying to decide on the database design, with as few assumptions (regarding how the web app actually evolves) as possible at this stage.
As a first step, understanding that JOINS are expensive, I am considering a small number of monolithic tables as opposed to a large number of normalized smaller tables. As a second point, I am confused between using hstore vs. regular tables vs. JSONB (with GiST indexing).
AFAIK (please feel free to correct):
Generally, in Postgres, hstore is known to perform better than other data types. This presentation from FOSDEM PGDAY has some interesting stats (in the second half of the slides). https://wiki.postgresql.org/images/b/b4/Pg-as-nosql-pgday-fosdem-2013.pdf
An advantage with hstore is the fast indexing (GiN or GiST). However, with JSONB, GiN and GiST indexing can also be applied to JSON data.
This blog from a professional at 2nd Quadrant says "At this point it’s probably worth replacing hstore use with jsonb in all new applications" (scroll to the end): http://blog.2ndquadrant.com/postgresql-anti-patterns-unnecessary-jsonhstore-dynamic-columns/
So I would like to decide on the following:
- For the main (structured) part of the data: should it go in a couple of relational tables (relatively large with many columns), or should it be a number of key-value stores using hstore?
- For the ad hoc (user contributed/unstructured) data, should it be in JSON or ad hoc key value stores in hstore (with the keys stored in one of the main relational tables)?