「Postgresql架构」使用PostgreSQL中的JSONB数据类型加快操作

从版本9.4开始，PostgreSQL在使用JSON数据的二进制表示jsonb时提供了显着的加速，这可以为您提供增加性能所需的额外优势。

什么是jsonb

由PostgreSQL文档定义的数据类型json和jsonb几乎相同;关键的区别在于json数据存储为JSON输入文本的精确副本，而jsonb以分解的二进制形式存储数据;也就是说，不是ASCII / UTF-8字符串，而是二进制代码。

这有一些直接的好处：

1. 效率更高，
2. 加工速度明显加快
3. 支持索引（这可能是一个重要的优势，我们稍后会看到），
4. 更简单的模式设计（用jsonb列替换实体 - 属性 - 值（EAV）表，可以查询，索引和连接，从而使性能提高到1000倍！）

还有一些缺点：

1. 输入稍慢（由于增加的转换开销），
2. 它可能需要比普通json更多的磁盘空间，因为更大的表占用空间，尽管并非总是如此，
3. 由于缺乏统计信息，某些查询（尤其是聚合查询）可能会变慢。

最后一个问题背后的原因是，对于任何给定的列，PostgreSQL保存描述性统计信息，例如不同和最常见值的数量，NULL条目的分数，以及 - 对于有序类型 - 数据分布的直方图。当信息作为JSON字段输入时，所有这些都将不可用，并且您将遭受严重的性能损失，尤其是在大量JSON字段之间聚合数据（COUNT，AVG，SUM等）时。

为避免这种情况，您可以考虑存储稍后可能在常规字段上汇总的数据。

有关此问题的进一步评论，您可以阅读Heap的博客文章何时在PostgreSQL架构中避免使用JSONB。

用例：书籍条目

让我们使用带有书籍条目的玩具模型来说明在PostgreSQL中使用JSON数据时的一些基本操作。

如果您使用json或jsonb，本节中的操作将基本相同，但让我们回顾它们以刷新我们可以用JSON做什么，并在我们看到jsonb好吃之后立即设置我们的用例。

在表中定义列

很简单，我们使用jsonb数据类型指定数据列：

CREATE TABLE books ( book_id serial NOT NULL, data jsonb );

插入JSON数据

要将数据输入books表，我们只需将整个JSON字符串作为字段值传递：

INSERT INTO books VALUES (1, '{"title": "Sleeping Beauties", "genres": ["Fiction", "Thriller", "Horror"], "published": false}');
INSERT INTO books VALUES (2, '{"title": "Influence", "genres": ["Marketing & Sales", "Self-Help ", "Psychology"], "published": true}');
INSERT INTO books VALUES (3, '{"title": "The Dictator''s Handbook", "genres": ["Law", "Politics"], "authors": ["Bruce Bueno de Mesquita", "Alastair Smith"], "published": true}');
INSERT INTO books VALUES (4, '{"title": "Deep Work", "genres": ["Productivity", "Reference"], "published": true}');
INSERT INTO books VALUES (5, '{"title": "Siddhartha", "genres": ["Fiction", "Spirituality"], "published": true}');

查询数据

我们现在可以查询JSON数据中的特定键：

SELECT data->'title' AS title FROM books;

这将从JSONB数据中提取的标题作为列返回：

title --------------------------- "Sleeping Beauties" "Influence" "The Dictator's Handbook" "Deep Work" "Siddhartha" (5 rows)

过滤结果

您也可以使用WHERE子句但通过JSON键以正常方式过滤结果集：

SELECT * FROM books WHERE data->'published' = 'false';

在这种情况下，返回原始JSON数据：

book_id | data ---------+------------------------------------------------------------------------------------------------- 1 | {"title": "Sleeping Beauties", "genres": ["Fiction", "Thriller", "Horror"], "published": false} (1 row)

展开数据

这是一个重要的问题，因为它将使我们能够在处理关系数据库时使用我们熟悉的聚合函数，但是在JSON数据的反直觉环境中也是如此。

SELECT jsonb_array_elements_text(data->'genres') AS genre FROM books WHERE book_id = 1; 这会将JSON数组扩展为一列： genre ---------- Fiction Thriller Horror (3 rows)

特殊的jsonb功能

除了效率之外，还有其他方法可以让您以二进制形式存储JSON。

其中一个增强功能是GIN（广义倒置索引）索引以及随附的新品牌运营商。

检查遏制（Checking Containment）

Containment测试一个文档（一个集合或一个数组）是否包含在另一个文档中。这可以使用@>运算符在jsonb数据中完成。

例如，数组[“Fiction”，“Horror”]包含在数组[“Fiction”，“Thriller”，“Horror”]中（其中t代表true）：

SELECT '["Fiction", "Thriller", "Horror"]'::jsonb @> '["Fiction", "Horror"]'::jsonb; t

然而，相反的是["Fiction", "Thriller", "Horror"]包含在["Fiction", "Horror"]中，是错误的：

SELECT '["Fiction", "Horror"]'::jsonb @> '["Fiction", "Thriller", "Horror"]'::jsonb;
f

使用这个原则，我们可以轻松检查单一书籍类型：

SELECT data->'title' FROM books WHERE data->'genres' @> '["Fiction"]'::jsonb;
"Sleeping Beauties"
"Siddhartha"

通过传递一个数组（注意它们的关键顺序根本不重要），或者同时使用多个类型：

SELECT data->'title' FROM books WHERE data->'genres' @> '["Fiction", "Horror"]'::jsonb;
"Sleeping Beauties"

此外，从9.5版开始，PostgreSQL引入了检查顶级键和空对象包含的功能：

SELECT '{"book": {"title": "War and Peace"}}'::jsonb @> '{"book": {}}'::jsonb;
t

检查存在

作为包含的变体，jsonb还有一个存在运算符（？），可用于查找是否存在对象键或数组元素。

在这里，让我们计算出输入作者字段的书籍：

SELECT COUNT(*) FROM books WHERE data ? 'authors';

在这种情况下只有一个（“独裁者的手册”）：

count ------- 1 (1 row)

创建指数/索引

让我们花点时间提醒自己索引是关系数据库的关键组成部分。没有它们，每当我们需要检索一条信息时，数据库就会扫描整个表格，这当然效率很低。

jsonb相对于json数据类型的显着改进是能够索引JSON数据。

我们的玩具示例只有5个条目，但如果它们是数千或数百万个条目，我们可以通过构建索引来减少一半以上的搜索时间。

例如，我们可以索引出版的书籍：

CREATE INDEX idx_published ON books (data->'published');

由于idx_published索引，这个简单的索引将自动加速我们在已发布的书籍上运行的所有聚合函数（WHERE data - >'published'='true'）。

事实上，我们可以 - 并且可能应该在DB大小增加时 - 索引在过滤结果时要在WHERE子句上使用的任何内容。

注意事项

切换到jsonb数据类型时，您需要考虑一些技术细节。

jsonb更严格，因此，除非数据库编码为UTF8，否则它不允许非ASCII字符（U + 007F以上的字符）的Unicode转义。它还拒绝NULL字符（\ u0000），它不能用PostgreSQL的文本类型表示。

它不会保留空白区域，它会剥离JSON字符串中的前导/滞后空白区域以及JSON字符串中的空白区域，所有这些都只会使代码不整齐（毕竟这对你来说可能不是件坏事） 。）

它不保留对象键的顺序，处理键的方式与Python字典中的处理方式非常相似 - 未排序。如果您依赖JSON密钥的顺序，则需要找到解决此问题的方法。

最后，jsonb不会保留重复的对象键（这可能不是一件坏事，特别是如果你想避免数据中的歧义），只存储最后一个条目。

结论

PostgreSQL文档建议大多数应用程序应该更喜欢将JSON数据存储为jsonb，因为我们已经看到有显着的性能增强和仅有的小警告。

jsonb带来的功能非常强大，您可以很好地处理关系数据，就像在常规RDBMS中一样，但是所有这些都在JSON中，并且在性能上有非常显着的提升，结合了NoSQL解决方案的实用性。RDBMS的强大功能。

切换到jsonb时的主要缺点是遗留代码，例如，可能依赖于对象密钥的排序;这是需要更新以按预期工作的代码。并且说明显而易见的是，作为9.4版中引入的一个特性，jsonb不是向后兼容的，你需要使用的jsonb关键字设置JSON表将破坏传统平台上的SQL代码。

最后，请注意我已经涵盖了指数及其运算符的一些典型用法;有关更多详细信息和示例，请查看官方PostgreSQL文档中的jsonb索引以及JSON函数和运算符。

原文：https://www.compose.com/articles/faster-operations-with-the-jsonb-data-type-in-postgresql/

本文：http://pub.intelligentx.net/faster-operations-jsonb-data-type-postgresql

讨论：请加入知识星球或者小红圈【首席架构师圈】