是否JSONB，让PostgreSQL阵列没用？ [英] Does JSONB make PostgreSQL arrays useless?

问题描述

假设你想要的标签存储您的对象（比方说，一个后）。随着版本9.4你有3个主要的选择：

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

• 标记文本[]


• 标记jsonb


• 标签为文本（和你存储一个JSON字符串文本）

在许多情况下，第3将是不成问题的，因为它不会允许查询条件到标签的值。在我目前的发展，我不需要这样的质疑，标签只是那里的帖子列表中显示，不过滤的帖子。

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.

所以，选择的大多是之间的文本[] 和 jsonb 。两者都可以进行查询。结果
你会用什么？为什么？

So, choice is mostly between text[] and jsonb. Both can be queried.
What would you use? And why?

推荐答案

在大多数情况下，我会用一个归与表 option_tag 实施表之间的许多一对多的关系选项和架构 标记。参考实现在这里：

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:

• How实施PostgreSQL中的许多一对多的关系？

• How to implement a many-to-many relationship in PostgreSQL?

这可能不是在各个方面最快的选项，但它提供了全面的数据库功能，包括引用完整性，约束，全方位的数据类型，所有的指数期权和廉价的更新。

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 （很好的选择）


• XML 更详细，比更复杂或者 hstore 或 jsonb ，所以我只能用工作时用它XML。


• 的逗号分隔值字符串（很简单，大多是坏的选择）


• EAV（实体 - 属性 - 值）或名称 - 值对（大多是坏的选择）结果
  下dba.SE此相关的问题的详细信息：
  
  
  
  • 是否有此数据库结构的名称？
  
  
  • 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:
    • Is there a name for this database structure?

    如果该列表只是用于显示和很少更新，我会考虑一个简单阵列，其通常较小，并执行此优于其他

    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.

    阅读由Josh Berkus如是博客条目> @a_horse挂在他的评论。但要注意，它侧重于选择读的情况。乔希承认：

    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 只有当你打算反正用JSON来操作，并且可以存储和检索JSON作为是一个不错的选择。

    jsonb is only a good option if you are going to operate with JSON anyway, and can store and retrieve JSON "as is".

    这篇关于是否JSONB，让PostgreSQL阵列没用？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！