PostgreSQL引入的JSONB的说明 [英] Explanation of JSONB introduced by PostgreSQL

问题描述

PostgreSQL刚刚引入了黑客新闻上流行.如果有人可以解释它与以前在PostgreSQL中存在的Hstore和JSON有何不同，那将是很棒的.它的优点和局限性是什么?何时应该考虑使用它?

解决方案

首先， hstore 是一个contrib模块，它仅允许您存储key =>值对，其中键和值只能为text s(但是值也可以为sql NULL s).

两个json& jsonb允许您存储有效的JSON 值(在其规范中定义). >

F.ex.这些是有效的JSON表示形式:与JSON相比，null，true，[1,false,"string",{"foo":"bar"}]，{"foo":"bar","baz":[null]}-hstore只是一个很小的子集(但如果您只需要此子集，就可以了). /p>

json& jsonb是它们的存储空间:

• json以其纯文本格式存储，而
• jsonb以某种二进制表示形式存储

这有3个主要后果:

• jsonb通常要比json占用更多的磁盘空间(有时不是)
与输入json 相比，
• jsonb需要花费更多的时间来构建其输入表示形式.
• json操作比jsonb花费的时间更多 (每次以json键入的值执行某些操作时，也需要进行解析)

当jsonb随稳定发行版一起发布时，将有两个主要用例，您可以在它们之间轻松选择:

1. 如果仅在应用程序中使用JSON表示形式，则PostgreSQL仅用于存储&检索此表示形式，应使用json.
2. 如果您对PostgreSQL中的JSON值进行了大量操作，或对某些JSON字段使用索引，则应使用jsonb.

PostgreSQL just introduced JSONB and it's already trending on hacker news. It would be great if someone could explain how it's different from Hstore and JSON previously present in PostgreSQL. What are it's advantages and limitations and when should someone consider using it?

解决方案

First, hstore is a contrib module, which only allows you to store key => value pairs, where keys and values can only be texts (however values can be sql NULLs too).

Both json & jsonb allows you to store a valid JSON value (defined in its spec).

F.ex. these are valid JSON representations: null, true, [1,false,"string",{"foo":"bar"}], {"foo":"bar","baz":[null]} - hstore is just a little subset compared to what JSON is capable (but if you only need this subset, it's fine).

The only difference between json & jsonb is their storage:

• json is stored in its plain text format, while
• jsonb is stored in some binary representation

There are 3 major consequences of this:

• jsonb usually takes more disk space to store than json (sometimes not)
• jsonb takes more time to build from its input representation than json
• json operations take significantly more time than jsonb (& parsing also needs to be done each time you do some operation at a json typed value)

When jsonb will be available with a stable release, there will be two major use cases, when you can easily select between them:

1. If you only work with the JSON representation in your application, PostgreSQL is only used to store & retrieve this representation, you should use json.
2. If you do a lot of operations on the JSON value in PostgreSQL, or use indexing on some JSON field, you should use jsonb.

这篇关于PostgreSQL引入的JSONB的说明的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！