27

Suppose that you want to store "tags" on your object (say, a post). With release 9.4 you have 3 main choices:

  • tags as text[]
  • tags as jsonb
  • tags as text (and you store a JSON string as text)

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?

Improve this question
5
  • 10
    databasesoup.com/2015/01/tag-all-things-part-2.html Commented Mar 8, 2015 at 18:43
  • 1
    nice link. i have my answer : i'm suprised how fast JSONB is, but still ARRAY performs usually better. thx. Commented Mar 9, 2015 at 16:57
  • 1
    Really nice link! The queries for the JSONB and List examples (part 3 in the article) look a lot simpler as well, compared to the normalized table versions. Commented Apr 8, 2018 at 18:55
  • Yeah, if a normalized schema isn't desired, seems like jsonb and text[] are apparently similar, with text[] winning out in a few scenarios in the tag-all-things link above- Commented Apr 6, 2022 at 14:16
  • Something else to consider is that the array type allows for more comparison options (&&, @>, etc), the json array only @> (restrictive AND contains); something I found out the hard way Commented Mar 25, 2023 at 11:50

2 Answers 2

Reset to default
15

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.
  • "string of comma-separated values" (very simple, mostly bad option)
  • EAV (Entity-Attribute-Value) or "name-value pairs" (mostly bad option)
    Details under this related question on dba.SE:

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

Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

normalized schema are not my cups of tea : it adds two tables, needs join to perform requests, but above all, fragments data. i guess that's why arrays have been implemented : one object, one table. also my viewpoint is that most of 'normalized schema' advantages have to be implemented in the application, otherwise you have a problem with the application. why do you say "jsonb is only a good option if you are going to operate with JSON anyway" ? that's the case, but jsonb has one great advantage : flexibility.
If anything picking custom datatypes such as JSONB does not offer flexibility. You will be limited by what database libraries/ORMs you can use as most do not support collection like datatypes.
that's why ORMs gains are (in my mind) questionable. Not sure what you gain in simplicity worth what you loose in flexibility. They lag DB progress, they have a (small) cost to get in. But it's a personal view though.
If tags are ordered, updates will perform terribly.
@comte: You can formulate involved joins as subqueries if you prefer. But, while joins have a cost, the claim that joins are slow is too unspecific to be true. It depends.
|
1

I have used both a normalized schema and just a plain text field with CSV separated values instead of custom data types (instead of CSV you can use JSON or whatever other encoding like www-urlencoding or even XML attribute encoding). This is because many ORM's and database libraries are not very good at supporting custom datatypes (hstore, jsonb, array etc).

@ErwinBrandstetter missed a couple of other benefits of normalized one being the fact that it is much quicker to query for all possible previously used tags in a normalized schema than the array option. This is a very common scenario in many tag systems.

That being said I would recommend using Solr (or elasticsearch) for querying for tags as it deals with tag count and general tag prefix searching far better than what I could get Postgres to do if your willing to deal with the consistency aspects of synchronizing with a search engine. Thus the storage of the tags becomes less important.

Improve this answer

1 Comment

I certainly missed most of the pros and cons, since this is a huge topic and I just named a couple of the prominent features. You added some more, ORM support probably being important for many ...

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.