Suppose that you want to store "tags" on your object (say, a post). With release 9.4 you have 3 main choices:
In many cases, 3rd would be out of question since it wouldn't allow query conditional to 'tags' value. In my current development, I don't need such queries, tags are only there to be shown on posts list, not to filter posts.
So, choice is mostly between text[] and jsonb. Both can be queried.
What would you use? And why?
In most cases I would use a normalized schema with a table option_tag implementing the many-to-many relationship between the tables option and tag. Reference implementation here:
It may not be the fastest option in every respect, but it offers the full range of DB functionality, including referential integrity, constraints, the full range of data types, all index options and cheap updates.
For completeness, add to your list of options:
hstore (good option)xml more verbose and more complex than either hstore or jsonb, so I would only use it when operating with XML.If the list is just for display and rarely updated, I would consider a plain array, which is typically smaller and performs better for this than the rest.
Read the blog entry by Josh Berkus @a_horse linked to in his comment. But be aware that it focuses on selected read cases. Josh concedes:
I realize that I did not test comparative write speeds.
And that's where the normalized approach wins big, especially when you change single tags a lot under concurrent load.
jsonb is a good option if you are going to operate with JSON anyway, and can store and retrieve JSON "as is".