由 John Doe 十月 25, 2024
摘要：在本文中，我们将了解一些在 PostgreSQL 中对 JSON 结构的滥用情况。
目录
介绍
PostgreSQL 支持 JSON，但您不应该将它用于您正在做的大部分操作。这也适用于 hstore 和新的 JSONB 类型。这些类型在需要的地方是很有用的特性，但在 PostgreSQL 中建模数据时，不应成为您的首选，因为它会使查询和操作变得更加困难。
一些读者可能会熟悉实体-属性-值模型。长期以来，EAV 一直在数据库结构中受到诟病。这是我们都建议不要使用和看不起的东西，然后只有在现实和商业要求情况下，并非所有东西都可以用正确建模的关系将其放入到确定的字段时，才会考虑使用它。对于那些不知道它的人来说，这是一种模型设计，你可以使用一个包含 object_id（“实体”）、key（“属性”）和 value 列的表，将关系数据库变成低级的键/值存储。查询它通常要涉及大量自连接。
来自维基百科
实体-属性-值模型（EAV）是一种数据模型，它针对稀疏或临时的属性和数据值的空间高效存储进行了优化，适用于运行时使用模式任意、受用户变化或无法使用固定设计预见需要的情况。
JSON 是新的 EAV – 当你需要它时，它是一个很棒的特性，但不是你应该作为首选的东西。
（这里的大部分内容，也适用于 PostgreSQL 数组，它们是构建查询的好特性，但通常不是存储正规数据的合适方式）。
JSON 二进制大对象表
自 PostgreSQL 9.3 发布以来，甚至自 9.4 中的 JSONB 发布以来，有越来越多的 Stack Overflow 问题，人们询问如何对 JSON 对象进行连接，如何查询任何数组元素内的嵌套键等。这些都是很合理的事情，但是当被要求提供表结构和示例数据时，他们通常会提交如下的内容：
CREATE TABLE people(
id serial primary key,
data jsonb not null
);
INSERT INTO people(data) VALUES ($$
{
"name": "Bob",
"addresses": [
{
"street": "Centre",
"streetnumber": 24,
"town": "Thornlie",
"state": "WesternAustralia",
"country": "Australia"
},
{
"street": "Example",
"streetnumber": "4/311-313",
"town": "Auckland",
"country": "NewZealand"
}
],
"phonenumbers": [
{
"type": "mobile",
"number": "12345678"
}
]
}
$$);
INSERT INTO people(data) VALUES ($$
{
"name": "Fred",
"phonenumbers": [
{ "type": "mobile", "number": "12345678" }
]
}
$$);
INSERT INTO people(data) VALUES ($$
{
"name": "John Doe"
}
$$);
并询问 “如何找到具有相同电话号码的人？“
经验丰富的关系数据库用户已经畏缩了。但请记住，并不是每个人都习惯于颠倒他们的思维，从子对象跟随外键向后找到父对象，并将复合实体分解为正常形式。仔细想想，关系数据库很奇怪。
不过，PostgreSQL 的大部分能力都源自于该关系模型。当您将数据存储为 JSON 二进制大对象时，您就剥夺了查询规划器根据表和列的统计信息做出明智决策的能力，您将无法利用大部分索引特性和扫描类型，并且通常只能做非常原始的操作。要执行任何高级的操作，往往会涉及许多自连接和过滤条件。
巴洛克式查询
例如，查找具有匹配电话号码的人的要求，可以转换为如下查询：
select
p1.id AS person1,
p2.id AS person2,
p1.data ->> 'name' AS "p1 name",
p2.data ->> 'name' AS "p2 name",
pns1 ->> 'type' AS "type",
pns1 ->> 'number' AS "number"
from people p1
inner join people p2
on (p1.id > p2.id)
cross join lateral jsonb_array_elements(p1.data -> 'phonenumbers') pns1
inner join lateral jsonb_array_elements(p2.data -> 'phonenumbers') pns2
on (pns1 -> 'type' = pns2 -> 'type' AND pns1 -> 'number' = pns2 -> 'number');
这样看上去不是也挺好看的吗？
JSON 支持的函数和运算符没有什么特别的问题，只是使用关系连接和谓词条件进行查询，获取嵌套的对象式数据，会很别扭。
至少 PostgreSQL 支持 LATERAL；否则，这简直是编写查询的噩梦。
碰巧的是，针对此特定问题，以关系模式写出的等效查询并没有好多少：
SELECT
p1.id AS "person1",
p2.id AS "person2",
p1.name AS "p1 name",
p2.name AS "p2 name",
pns1.phoneno AS "p1 phone",
pns2.phoneno AS "p2 phone"
FROM
person p1
INNER JOIN person p2 ON (p1.id < p2.id)
INNER JOIN phone pns1 ON (p1.id = pns1.person_id)
INNER JOIN phone pns2 ON (p2.id = pns2.person_id)
WHERE
pns1."type" = pns2."type"
AND pns1.phoneno = pns2.phoneno;
但是，如果在（phone.phoneno）上有索引，运行速度可能会比基于 JSON 的查询快得多，并且在此过程中从磁盘读取的数据要少得多。
无固定数据类型
上面给出的基于 JSON 的查询也有问题，因为 JSONB 的比较对数据类型很敏感，因此 JSONB 值 “12345678” 和 12345678 不相等：
SELECT '12345678'::jsonb, '"12345678"'::jsonb, '12345678'::jsonb = '"12345678"'::jsonb AS "isequal";
jsonb | jsonb | isequal
----------+------------+---------
12345678 | "12345678" | f
所以这个：
insert into people (data) values
('{"phonenumbers": [{"type":"mobile","number":12345678}]}');
不会被标识为重复项，即使它应该被标识为重复项。
请注意，这可以说是 PostgreSQL 的限制，因为 JavaScript 解释器的宽松类型意味着它们比较的结果相等：
> 12345678 == "12345678"
> true
要使用 PostgreSQL 查询获取要匹配的数字，我们必须更改查询，以让：
on (pns1 -> 'type' = pns2 -> 'type' AND pns1 -> 'number' = pns2 -> 'number');
改用 -» JSON-文本值 运算符：
on (pns1 -> 'type' = pns2 -> 'type' AND pns1 ->> 'number' = pns2 ->> 'number');
因为数字的文本形式是相同的。
无约束
当然，有人可能会插入：
insert into people (data) values
('{"phonenumbers": [{"type":"Mobile","number":"1234 5678"}]}');
由于有大写 M 和数字中的空格，因此不会匹配。
由于所有内容都在 JSON 二进制大对象中，因此无法轻松地对值添加 CHECK 约束、使用 ENUM 或 DOMAIN 类型、创建由触发器维护的影子规范化列，或者使用任何用于处理数据规范化的常用方法，例如。用 PL/v8 等过程语言编写 JSON 验证/转换的触发器，或者在应用程序中完成所有操作，这都是有必要的。
缺少统计信息，查询速度缓慢
对于传统数据类型，PostgreSQL 会存储有关每个表的每一列中的值的分布统计信息，例如：
- 看到的不同值的数量
- 最常见的值
- 值为 NULL 的条目的占比
- 对于有序类型，构建一个列中值的分布的直方图草图
对于给定的查询，查询规划器使用这些统计信息，来估计哪个执行计划将是最快的。例如，让我们创建一个表，其中包含 100 万条三个指标的 “测量值”，每个值都从{0, 1}中均匀随机选择。每次测量由 10,000 名科学家中的一名进行，每位科学家来自三个实验室之一：
CREATE TABLE measurements (
tick BIGSERIAL PRIMARY KEY,
value_1 INTEGER,
value_2 INTEGER,
value_3 INTEGER,
scientist_id BIGINT
);
INSERT INTO measurements (value_1, value_2, value_3, scientist_id)
SELECT
trunc(2 * random()),
trunc(2 * random()),
trunc(2 * random()),
trunc(10000 * random() + 1)
FROM generate_series(0, 999999);
CREATE TABLE scientist_labs (scientist_id BIGSERIAL PRIMARY KEY, lab_name TEXT);
INSERT INTO scientist_labs (lab_name)
SELECT (
'{"Tsinghua University", "Industrial Labs", "Western Science People"}'::TEXT[]
)[i % 3 + 1]
FROM generate_series(1, 10000) i;
ANALYZE;
假设我们想要获取所有三个值都为0时的刻度线（大约为它们的 1/8），并查看每个实验室在相应科学家中代表了多少次。我们的查询将如下所示：
SELECT lab_name, COUNT(*)
FROM (
SELECT scientist_id
FROM measurements
WHERE
value_1 = 0 AND
value_2 = 0 AND
value_3 = 0
) m
JOIN scientist_labs AS s
ON (m.scientist_id = s.scientist_id)
GROUP BY lab_name
我们的查询计划将如下所示：
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=28905.96..28905.99 rows=3 width=20) (actual time=297.276..297.278 rows=3 loops=1)
Group Key: s.lab_name
-> Hash Join (cost=296.00..28279.80 rows=125232 width=20) (actual time=5.606..262.124 rows=125222 loops=1)
Hash Cond: (measurements.scientist_id = s.scientist_id)
-> Seq Scan on measurements (cost=0.00..24853.00 rows=125232 width=8) (actual time=0.016..177.659 rows=125222 loops=1)
Filter: ((value_1 = 0) AND (value_2 = 0) AND (value_3 = 0))
Rows Removed by Filter: 874778
-> Hash (cost=171.00..171.00 rows=10000 width=28) (actual time=5.575..5.575 rows=10000 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 599kB
-> Seq Scan on scientist_labs s (cost=0.00..171.00 rows=10000 width=28) (actual time=0.006..2.328 rows=10000 loops=1)
Planning time: 0.603 ms
Execution time: 297.346 ms
(12 rows)
Time: 300.463 ms
这就是我们希望看到的：规划器从我们的表统计信息中知道，表measurements中大约 1/8 的行将具有value_1、value_2和value_3等于0，因此其中大约 125,000 行需要与科学家的实验室连接，而数据库是通过哈希连接来执行的。也就是说，将scientist_labs的内容加载到以scientist_id为键的哈希表中，扫描measurements中的匹配行，然后按其scientist_id值在哈希表中查找每行。执行速度很快 — 在一台普通机器上大约需要 300 毫秒。
假设我们将测量值存储为 JSONB 二进制大对象，如下所示：
CREATE TABLE measurements (tick BIGSERIAL PRIMARY KEY, record JSONB);
INSERT INTO measurements (record)
SELECT (
'{ "value_1":' || trunc(2 * random()) ||
', "value_2":' || trunc(2 * random()) ||
', "value_3":' || trunc(2 * random()) ||
', "scientist_id":' || trunc(10000 * random() + 1) || ' }')::JSONB
FROM generate_series(0, 999999) i
类似的读取查询将如下所示：
SELECT lab_name, COUNT(*)
FROM (
SELECT (record ->> 'scientist_id')::BIGINT AS scientist_id
FROM measurements
WHERE
(record ->> 'value_1')::INTEGER = 0 AND
(record ->> 'value_2')::INTEGER = 0 AND
(record ->> 'value_3')::INTEGER = 0
) m
JOIN scientist_labs AS s
ON (m.scientist_id = s.scientist_id)
GROUP BY lab_name
性能明显变差 — 在一台普通机器上长达 584 秒，慢了大约 2000 倍：
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=58553.01..58553.02 rows=1 width=20) (actual time=583724.702..583724.703 rows=3 loops=1)
Group Key: s.lab_name
-> Nested Loop (cost=0.00..58553.00 rows=1 width=20) (actual time=5.457..583510.640 rows=124616 loops=1)
Join Filter: (((measurements.record ->> 'scientist_id'::text))::bigint = s.scientist_id)
Rows Removed by Join Filter: 1246035384
-> Seq Scan on measurements (cost=0.00..58182.00 rows=1 width=105) (actual time=0.032..1134.662 rows=124616 loops=1)
Filter: ((((record ->> 'value_1'::text))::integer = 0) AND (((record ->> 'value_2'::text))::integer = 0) AND (((record ->> 'value_3'::text))::integer = 0))
Rows Removed by Filter: 875384
-> Seq Scan on scientist_labs s (cost=0.00..171.00 rows=10000 width=28) (actual time=0.003..0.864 rows=10000 loops=124616)
Planning time: 0.603 ms
Execution time: 583724.765 ms
(11 rows)
Time: 583730.320 ms
根本原因是 PostgreSQL 不知道如何保留 JSONB 列中字段值的统计信息。例如，它无法知道大约 50% 的情况下，record ->> 'value_2' = 0是正确的，因此它采用的是硬编码的估计值，即 0.1% 。因此，它估计measurements表中的匹配行占比为 0.1% * 0.1% * 0.1%（它四舍五入到大约为 1 行）。因此，它选择了一个嵌套的循环连接：对于measurements中匹配过滤条件的每一行，通过后一个表的主键查找scientist_labs中相应的lab_name。但是由于有大约 125,000 行这样的测量记录，而不是大约 1 行，结果需要很长的时间。
与往常一样，准确的统计信息是获得良好数据库性能的关键因素。如果没有它们，规划器无法确定哪些连接算法、连接顺序或扫描类型将使查询更快。结果会让这些无知的查询对您造成影响。这是使用 JSONB 的一个隐含代价：您的数据没有统计信息，因此查询规划器是盲目的。
这不只是理论上的风险。这会给我们带来生产上的问题，解决这些问题的唯一方法是将嵌套循环作为连接选项完全禁用，并将全局设置为enable_nestloop = off。通常，你永远不应该做这样的事情。
在键值/文档型存储的业务负载中，这可能不会让您感到困扰，但是如果您将 JSONB 与分析查询一起使用，则很容易遇到这种情况。
表占用的空间更大
在后台，PostgreSQL 的 JSON 数据类型将您的二进制大对象存储为字符串，它恰好知道这些字符串是有效的 JSON。JSONB 编码的开销要多一些，但好处是您无需解析 JSON 来检索特定字段。在这两种情况下，数据库至少要将每个键和值存储在每一行中。PostgreSQL 没有做任何优化的事情，来对经常出现的键进行重复数据删除。
再次拿上面的measurements表来说，我们的表开始采用无 JSONB 的版本，占用 79 MB 的磁盘空间，而引入 JSONB 以后占用 164 MB，这是原来的两倍多。也就是说，我们大部分的表内容都是一遍又一遍重复的字符串，如：value_1、value_2、value_3和scientist_id。因此，在这种情况下，您需要支付两倍的磁盘费用，更不用说使各种操作变慢或开销更高的后续影响了。原始结构的缓存效果要好得多，或者还可能完全放入内存。较小的空间意味着对于大型读取或维护操作，它只需要一半的 I/O。
在某一次优化实践过程中，我们发现从 JSONB 中提取 45 个常用字段并放入关系列中，节省了大约 30% 的磁盘空间。在 PB 级数据集上，这是一个相当可观的收益了。
根据经验，表中每行的每列开销约为 1 个位，无论该列的值是否为 null。因此，例如，如果一个可选字段在你的 JSONB 二进制大对象中有一个 10 个字符的键，因此在它存在的每一行中存储该键至少需要 80 个位，那么如果它存在于至少 1/80 的行中，那么将它转换为一个关系列将节省空间。
对于具有许多可选值的数据集，将每个可选值作为表列来存放，通常不切实际或不可能。在此类情况下，JSONB 非常适合，无论是简单性还是性能。但是，对于大多数行中出现的值，最好将它们分开。在实践中，通常还有其他背景因素会影响您如何组织数据，例如管理关系结构所需的工程量，或者这样做对类型安全和 SQL 可读性的好处。但是，不必要地对数据进行 JSON 化处理，通常也会带来重要的性能损失。
什么时候才应该使用 JSON 呢？
从上面来看，你可能会认为本文在反对在数据库中使用 JSON 类型。事实并非如此。当您拥有不适合传统关系模型的动态列数据时，它们通常比 EAV 更好。或者，正如我过去看到的人们尝试的那样，使用一个外部的 MongoDB 来存储 JSON。
例如，有时应用程序开发者希望能够向实体添加任意属性。这是一项业务需求。当你说数据库不能这样做，所以他们不能在他们的应用程序中有备注时，客户会不太喜欢，他们会反馈 “只是在应用程序中做这件事” 或 “我们在 MongoDB 上没有这些问题”。
因此，你可以对典型的内容进行关系建模（尽管不加选择地使用代理键），但只为一些额外内容添加一个 JSON 字段，以便应用程序可以在显示对象实体时获取它们。
如何决定何时使用 JSON
如果您的数据无法放入使用普通关系建模的数据库，请使用 JSON。如果你在考虑使用 EAV、或将 Java/Ruby/Python 对象序列化为 bytea 字段，或存储一个键以在其他地方查找外部的结构化对象，对这些方案犹豫不决时，这时您应该使用 JSON 字段。
从这一点来说，您可能应该在所有新的应用程序中用 JSONB 替换 hstore 的使用。
请注意，纯 JSON 字段也很有用。如果您不打算为 JSON 创建索引，并对其中的字段进行查询，它们通常会更紧凑，发送和接收速度更快。