作者：劳伦茨·阿尔贝（Laurenz Albe）
劳伦茨·阿尔贝是 CYBERTEC公司的高级顾问和支持工程师。自2006年以来,他一直在参与和为PostgreSQL做贡献,为核心编写了补丁,并编写了oracle_fdw。他拥有维也纳大学数学硕士学位和维也纳技术大学计算机科学硕士学位。在业余时间,他喜欢给孩子们读书,思考语言的根源。
概述
全面的 Json 支持是PostgreSQL最受欢迎的功能之一。很多人—特别是那些在javaSoript编程背景比关系数据库中的强的人—广泛地使用它。然而,我的经验是绝大多数人没有正确使用它。从长远来看,这将导致问题和不幸。
在本文中,我将试图指出PostgreSQL中JSON的好的和坏的用途,并为您提供可以遵循的指导方针。
糟糕的例子
这个数据模型举例说明了所有你可能做错的事情:
/* this table is fine */
CREATE TABLE people (
id bigint PRIMARY KEY,
name text NOT NULL
);
INSERT INTO people VALUES
(1, 'laurenz'),
(2, 'julian'),
(3, 'ants'),
(4, 'kaarel');
/* this table is ill-defined */
CREATE TABLE rooms (
id bigint PRIMARY KEY,
data jsonb
);
INSERT INTO rooms VALUES
(1,
'{ "name": "Room 1",
"reservations": [
{ "who": 1, "from": "2021-06-01 09:00:00", "to": "2021-06-01 10:00:00" },
{ "who": 3, "from": "2021-06-01 10:00:00", "to": "2021-06-01 11:30:00" },
{ "who": 2, "from": "2021-06-01 13:15:00", "to": "2021-06-01 14:45:00" },
{ "who": 1, "from": "2021-06-01 15:00:00", "to": "2021-06-01 16:00:00" }
]
}'),
(2,
'{ "name": "Room 2",
"reservations": [
{ "who": 2, "from": "2021-06-01 09:30:00", "to": "2021-06-01 10:30:00" }
]
}');
第一个错误:将常规列建模为JSON属性
我没有理由 不 把房间的名字作为常规列。毕竟,每个房间都会有一个名称,我们可能希望在房间名称上强制执行类似唯一性的约束。
第二个错误:将表格数据模型作为JSON数组
房间预订完全是规则的表格数据,定义了房间和人之间多到多的关系。用一个连接表对相同的数据进行建模会很简单:
/* no primary key - we'll get to that later */
CREATE TABLE reservations (
people_id bigint REFERENCES people NOT NULL,
room_id bigint REFERENCES rooms NOT NULL,
reserved tsrange NOT NULL
);
INSERT INTO reservations VALUES
(1, 1, '[2021-06-01 09:00:00,2021-06-01 10:00:00)'),
(3, 1, '[2021-06-01 10:00:00,2021-06-01 11:30:00)'),
(2, 1, '[2021-06-01 13:15:00,2021-06-01 14:45:00)'),
(1, 1, '[2021-06-01 15:00:00,2021-06-01 16:00:00)'),
(2, 2, '[2021-06-01 09:30:00,2021-06-01 10:30:00)');
许多人似乎认为在表中存储很少的大行比存储许多小行更有效。这是有一些事实的,因为每一行都有一些开销,而且PostgreSQL压缩了大的数据。但是,如果您只想检索数据的一部分,或者想修改它们,那么许多小行就会更加高效。
第三个错误:在JSON中存储外键
“who”属性存储对people的外键引用。这不是一个好主意，因为数据库不可能强制执行这样的约束:我也可以插入对不存在的人的引用。对于上面的连接表，定义外键很简单。
此外，您经常希望外键连接。使用JSON，这将需要与未嵌套的JSON数组交叉连接:
SELECT rooms.data ->> 'name',
people.name
FROM rooms
CROSS JOIN LATERAL jsonb_array_elements(
rooms.data -> 'reservations'
) AS res(j)
JOIN people
ON res.j ->> 'who' = people.id::text;
如果有连接台的话
SELECT rooms.name,
people.name
FROM rooms
JOIN reservations AS r ON r.room_id = rooms.id
JOIN people ON r.people_id = people.id;
您可能可以猜测这两个查询中哪一个会更有效率。
第四个错误:修改JSON数据
如果要添加一个新预订，必须执行如下语句：
UPDATE rooms
SET data = jsonb_set(
data,
'{reservations,100000}',
'{"who": 3, "from": "2021-06-01 11:00:00", "to": "2021-06-01 12:00:00"}',
TRUE
)
WHERE id = 2;
这将获取完整的JSON对象，从中构造一个新的JSON，并将该新对象存储在表中。整个JSON对象必须被读取和写入，这比您想要的I/O要多——特别是如果JSON对象很大并且存储在行外。
比较一下，同样的操作在连接表中是多么简单:
INSERT INTO reservations VALUES
(3, 2, '[2021-06-01 11:00:00,2021-06-01 12:00:00)');
此语句只会写入少量数据。
删除预订是同样复杂和昂贵的,作为练习留给读者。
第五个错误:试图对JSON施加约束
到目前为止，我们的数据模型没有提供针对重叠保留的保护，而在数据库中执行重叠保留是很好的。
对于JSON，我们就不那么幸运了。想到的最好的方法是约束触发器，但这需要精心设计锁或SERIALIZABLE事务隔离级别，以避免竞争条件。此外，代码也远不简单。
有了结表，练习就简单了;我们所要做的就是添加一个排除约束来检查与&&操作符是否重叠:
CREATE EXTENSION IF NOT EXISTS btree_gist;
ALTER TABLE reservations ADD EXCLUDE USING gist (
reserved WITH &&,
room_id WITH =
);
需要扩展来创建一个GiST索引在bigint 列上。
第六个错误:JSON中的复杂搜索
可以使用JSON包含操作符@>执行简单的相等性搜索，并且这种搜索可以由GIN索引支持。但复杂的搜索是一种痛苦。
假设我们想要搜索在20121-06-01 15:30:00被占用的所有房间。使用JSON，这看起来有点像
SELECT id
FROM rooms
CROSS JOIN LATERAL jsonb_array_elements(
rooms.data -> 'reservations'
) AS elem(j)
WHERE CAST(elem.j ->> 'from' AS timestamp) <= TIMESTAMP '2021-06-01 15:30:00'
AND CAST(elem.j ->> 'to' AS timestamp) > TIMESTAMP '2021-06-01 15:30:00';
使用我们的连接表,查询变成
SELECT room_id
FROM reservations
WHERE reserved @> TIMESTAMP '2021-06-01 15:30:00';
这个查询可以使用我们在上面创建的排除约束中的GISE索引。
如果上述都是错误的,那么我们是否应该在PostgreSQL中使用JSON呢?
请不要误解我的意思:在PostgreSQL中支持JSON是一件很棒的事情。只是很多人不知道如何正确使用它。例如,大多数关于使用JSON所产生的问题,最好是避免使用。当您考虑在PostgreSQL中使用JSON时,请遵循这些指南:
1.对于容易存储在数据库表中的数据，不要使用JSON。
2.如果要修改单个属性，请避免使用大型JSON对象。
3.如果你想在复杂的WHERE条件下使用属性，不要使用JSON。
4.如果希望在某些属性上连接，请避免使用JSON。
5.如果你想对数据进行约束，就不要使用JSON。
6.不要在JSON属性中存储对其他表的引用。
通常情况下,将一些属性存储为常规表列和在JSON中存储其他属性可能是一个好主意。您越少需要处理数据库中的数据,就越容易将它们存储为JSON。
很好的例子
为了演示如何有效地使用数据库中的JSON，让我们考虑一个销售各种混合商品的商店。所有或大多数商品都有一些属性，如价格、重量、制造商、可用数量或包装尺寸。其他属性可能是罕见的，比如电源插头的类型，或者是模糊的，比如螺丝或音叉的音高。
而不是为所有可能的属性定义一个具有数百列的表，其中大多数将是NULL，我们用普通表列建模最常见的属性，并使用JSON来处理其余的:
CREATE TABLE wares (
id bigint PRIMARY KEY,
name text UNIQUE NOT NULL,
price numeric(15,2) NOT NULL,
weight_kg double precision NOT NULL,
available integer NOT NULL
CHECK (available >= 0),
manufacturer text,
package_size integer NOT NULL
CHECK (package_size >= 0),
attributes jsonb
);
/* to search in "attributes" */
CREATE INDEX ON wares USING gin (attributes);
/* for similarity search on "name" */
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX ON wares USING gin (name gin_trgm_ops);
这将允许有效的查询，如
SELECT name, price, available
FROM wares
WHERE name LIKE '%tuning fork%'
AND attributes @> '{"pitch": "a"}';
结论
当在PostgreSQL中使用JSON时，你可能会犯很多错误，特别是如果你不熟悉关系数据库。然而，如果使用得当，它可以成为一个强大的工具。
本文分享自微信公众号 - 开源软件联盟PostgreSQL分会（kaiyuanlianmeng）。
如有侵权，请联系 support@oschina.cn 删除。
本文参与“OSC源创计划”，欢迎正在阅读的你也加入，一起分享。
