1

Let's assume I have an Article, which can have multiple tags. The conventional way, would be to create 3 tables: articles, tags, article_tags.


I'm curious about performance implications of using a single articles table with a JSONB column to store the tags. Adding a btree index on tags.

What is the performance trade-off in such case?

  • Improvement: simpler DB structure
  • Con: SELECT statements to get all articles containing tag is slower. E.g. SELECT * FROM articles WHERE tag ANY(tags) (Does this justify a more complex DB structure?)

2 Answers 2

2

Con: SELECT statements to get all articles containing tag is slower. E.g. SELECT * FROM articles WHERE tag ANY(tags) (Does this justify a more complex DB structure?)

Yep, less efficient and more complex to query (so arguably more complex of a design in itself). Managing the data becomes less performant and more complicated, such as if you wanted to remove a Tag (or ban it from being used). Or even worse, any variation of it being used. At a minimum a normalized Tags table of the allowed words should really be used, to help manage them.

Another con is you lose relational integrity - e.g. there is no native constraint at the database layer preventing the same tag being added to the same article multiple times. (Per a_horse_with_no_name, it is possible to emulate with a function, but I believe would be less efficient than a native constraint.)

A native unique constraint or index on a normalized linking table would automatically enforce this, efficiently, improving your data integrity. One should normalize their tables when possible.

6
  • I do agree with the normalization approach (although "tags", might actually be one of the rare cases where de-normalization might make sense). However it is possible to create a check constraint to prevent duplicate tags in the array (it requires a custom function, but it's possible)
    – user1822
    Commented Feb 20, 2023 at 7:06
  • @a_horse_with_no_name "However it is possible to create a check constraint to prevent duplicate tags in the array (it requires a custom function, but it's possible)" - Fair enough, good to know. But would it usually be reasonable to do in terms of performance and complexity?...I would imagine the function would iterate the entire array every time it's ran. If so and if the collection was large, that must be significantly less efficient than a native unique constraint which is typically backed by an index which wouldn't scan the entire dataset.
    – J.D.
    Commented Feb 20, 2023 at 13:59
  • @J.D. Thanks in the case of articles, it would be at most 10K rows during the blog lifetime. It's trivial to assume that index on tag table would be more efficient the larger the collection, but at which size will it start to matter? 10K, 100K, 1M?
    – GRS
    Commented Feb 20, 2023 at 14:06
  • @GRS Well it's not really the the number of articles, rather it's the number of tags per article - is there a limit? B-Tree indexes have a search time complexity of O(log(n)) where a scan of an array would be O(n), so indexes are always faster to search. "but at which size will it start to matter?" - That is a subjective question with a lot of variables and only you can answer that for yourself by testing it both ways. Even though indexes are always faster, depending on the size of the data, it could be a 1 millisecond difference or a 1 minute difference or worse.
    – J.D.
    Commented Feb 20, 2023 at 14:11
  • 1
    That's a great point too. I guess it's better to go with the M-M relation in the end.
    – GRS
    Commented Feb 21, 2023 at 17:18
2

To efficiently search in tags, you'd need a GIN index on the array. Then you can query like

WHERE tags && ARRAY['tag1','tag2']

or

WHERE tags @> ARRAY['tag1']

But GIN indexes are much slower to update than B-tree indexes.

Which brings us to your actual question. What value has a “simple” database structure for you? I would opine that this could be valuable if either your queries or your data modification statements become simpler or faster. And of course it depends on how frequently the data are modified or queried. The solution with the array can possibly speed up inserts if there are many tags (at some point the many inserts will be slower than updating the GIN index). Queries will probably not become noticeably faster.

I think that a final answer can only come from a performance test with a realistic amount of data.

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.