2

In PostgreSQL, what are the pros and cons of using a GIN index on a column of type integer[] to model sets of tags, instead of doing a relation table?

I am trying to design a simple data model with a table called post where each post can have a set of tags, similarly to Instagram or StackExchange. It seems that I would be able to search for a post that contains a set of tags without having to join two tables, but I won't be able to easily get the list of tag strings for one specific post.

Improve this question
2
  • 1
    databasesoup.com/2015/01/tag-all-things.html Commented Jul 10, 2015 at 21:50
  • actually once you have the post id you get all the tags string with a simple query. SELECT p.id, t.tagname FROM post p JOIN tag t ON t.id = ANY(p.tagids) where p.id = <Post ID> Commented Aug 20, 2015 at 19:21

1 Answer 1

Reset to default
3

I think you already more or less answered your own question.

While a GIN index can speed up searching inside an array quite significantly, you have to be aware that relational database engines are designed to do joins very efficiently. Also, when you need the actual tags, you will have to join the tag table regardless of you use the array approach or not.

Furthermore, storing the tags as an array of integer makes enforcing referential integrity (that it is impossible to insert a nonexistent tag ID into the array) quite complicated. The only way I can imagine this is creating a trigger, which never makes changing the data faster.

The best way to choose among these alternatives is, though, to create a sufficiently big amount of test data and measure the performance yourself.

Improve this answer

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.