38

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

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

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

  3. 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:

  1. 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?
  2. 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)?
Improve this question
9
  • 13
    Joins are not expensive. Who said it to you? As basically the whole concept of relational databases revolve around joins (from a practical point of view), these product are very good at joining. The normal way of thinking is starting with properly normalized structures and going into fancy denormalizations and similar stuff when the performance really needs it on the reading side. JSON(B) and hstore (and EAV) are good for data with unknown structure.
    – András Váczi
    Commented Sep 22, 2015 at 20:44
  • 7
    @Yogesch those links contain some interesting and wildly contradicting stuff :) As a moral, it looks like MySQL is (was) bad at joins, and NoSQL people tend to generalize this notion without any actual factual basis. On the other hand, Aaron and Max are sensitive to that p-word - its wide usage shows how non-native speakers (myself included) use happily the wrong word.
    – András Váczi
    Commented Sep 22, 2015 at 21:52
  • 4
    @Yogesch realistically I'm sure there is a source on the Internet to "prove" anything, just like any religious text can be used to justify atrocities (as dramatically shown across history). It is true the less work you do the less it costs, but there is always some trade off.
    – Erik
    Commented Sep 22, 2015 at 22:07
  • 4
    @Yogesch: Avoiding joins is important for read-heavy operations where you know the data access pattern in advance, and so you can safely put all data you need into a single row. However, this makes other joins potentially more costly. Who's to say you won't need to join the data in many different ways to answer various questions? Now we're going to simply descend into the theory of relational data modeling...
    – Chris
    Commented Sep 22, 2015 at 22:08
  • 6
    @Yogesch In my practice, with databases the bottleneck is rarely the RAM or the CPU but it is I/O - this way avoiding storing redundant data is still an important thing. As Chris says, if you always see your data in only one way, this might be worth the price. If not, you are there with a bulky and highly unflexible chunk of data.
    – András Váczi
    Commented Sep 22, 2015 at 22:13

1 Answer 1

Reset to default
63

Relational databases are designed around joins, and optimized to do them well.

Unless you have a good reason not to use a normalized design, use a normalised design.

jsonb and things like hstore are good for when you can't use a normalized data model, such as when the data model changes rapidly and is user defined.

If you can model it relationally, model it relationally. If you can't, consider json etc. If you're choosing between json/jsonb/hstore, generally choose jsonb unless you have a reason not to.

That's what I said in my blog post, which addresses just this topic. Please read the whole post. The paragraph you quoted points out that if you're choosing a dynamic structure you should choose jsonb over hstore, but the rest of the blog post is about why you should usually prefer to model relationally if you can.

So. Model the main structured part relationally. If the tables are really wide with lots of columns, this might be a sign that further normalization is required. Do not be afraid of joins. Learn to love joins. Joining many small tables will often be faster than querying and maintaining big denormalized tables. Denormalize only if you need to for specific cases, and preferably via materialized views ... but don't do it until you know you need to and have an actual concrete problem to solve.

For user-contributed data that's freeform and unstructured, use jsonb. It should perform as well as hstore, but it's more flexible and easier to work with.

One relevant thing to understand: GiST and GIN indexes like those used on jsonb are generally much less efficient than a plain b-tree index. They're more flexible, but a b-tree index on a normal column will almost always be much, much faster.

Improve this answer
5
  • 1
    Many thanks Craig, now I have a much better understanding and know what to do. A followup question: if I am storing something like likes or followers in a two column format (post_id and user_id, for likes), is it better to use a relational table with two columns, or a hstore? (I don't mind making this into a new question)
    – ahron
    Commented Sep 23, 2015 at 10:05
  • 7
    @Yogesch It sounds like a bog-standard m:n join table with a consistent and stable format. The question should always be "is there a good reason I shouldn't do this the usual relational way for this particular case?".
    – Craig Ringer
    Commented Sep 23, 2015 at 10:30
  • hstore is deprecated. Use jsonb.
    – Melroy van den Berg
    Commented May 4, 2016 at 12:40
  • 4
    @danger89 Actually, it's not formally deprecated, though I don't think there's any reason to use it in favour of jsonb anymore. In any case ... that's kind of missing the point. The question is about whether to model relationally or use a structured data type.
    – Craig Ringer
    Commented May 4, 2016 at 15:15
  • 1
    @MelroyvandenBerg in no way hstore is deprecated. Simplicity itself is a strong point
    – Alex Yu
    Commented Dec 29, 2021 at 14:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.