PostgreSQL JSONB 实战:关系与文档的平衡

做平台型产品的同学,几乎都被一种需求折磨过:"用户资料要加几个扩展字段"。产品今天想加"所在行业",明天想加"公司规模",后天要加一个标签数组。每次都 ALTER TABLE users ADD COLUMN ...?列越加越多,表像刺猬,半年后没人记得清哪个字段还有没有数据、归谁负责。

要是直接上 MongoDB,又得多养一套组件:多一份备份、多一套权限、多一份跨库一致性要操心(写完 PG 还得同步过去,事务边界模糊)。为了"字段灵活"付出整个架构的复杂度,不划算。

PostgreSQL 的 jsonb 就是为这种夹缝生的:一个列装下"可变结构",还能建索引高效查。在一个数据库里,你同时拿到关系的强约束和文档的灵活。本文把 JSONB 的存取、查询、索引,以及最关键的——什么时候该用、什么时候别用——讲清楚。

一、半结构化数据的尴尬:ALTER 之痛与多组件之累

把可变字段塞进纯关系表,有几道硬伤:

1. DDL 不是免费的。加 nullable 列虽然 9.6 以后不再重写整表,但当一张表累积到几十上百列,SELECT * 的宽行、统计信息、ORM 映射、迁移脚本都会变成噩梦。更别提删一个字段要先确认没有依赖、没有视图、没有索引。
2. 稀疏数据浪费空间且语义混乱。一百个租户,每个租户都有自己 5 个"自定义字段",你建 500 列?那 95% 的列对每个租户都是 NULL,表结构彻底失去可读性。
3. 嵌套结构表达不了。一个"用户偏好"里嵌着 notification.email.enabled、notification.push.frequency,关系表要么拆成多张表(JOIN 烦死人),要么平铺成 notif_email_enabled 这种丑陋命名。

那为什么不全交给文档库?因为绝大多数业务的核心数据(订单、账户、支付)是强关系、强一致性的,把这部分搬进 Mongo 是开倒车。理想做法是:核心关系留在关系表,可变结构塞进 jsonb 列,两者在同一张表里共处。

二、JSONB vs JSON:差一个字母,差一个世界

PostgreSQL 同时有 json 和 jsonb 两种类型,新手第一反应是"选哪个"。答案几乎永远是 jsonb。

• json:把输入文本原样存下来(类似 text 带个 JSON 校验)。写入快,因为不做解析;但每次查询都要重新解析,不能高效索引。
• jsonb:写入时解析成二进制表示(解析树),丢弃无关空格、不保留键的重复(同名键只留最后一个)、不保留键的原始顺序。写入稍慢(要解析),但查询快、可建索引,这才是要紧的。

一句话区别:json 是"存进去什么样,读出来还什么样",jsonb 是"存进去的是结构,不是文本"。

-- 同名键的差别(官方文档经典示例)
SELECT '{"bar":"x", "bar":"y"}'::json;   -- {"bar":"x","bar":"y"}  原样保留
SELECT '{"bar":"x", "bar":"y"}'::jsonb;  -- {"bar": "y"}           只留最后一个

实务上,新项目一律用 jsonb。只有一种情况用 json:你纯粹把 PG 当审计日志仓库,只写不查内部字段,偶尔整段吐回去——这种场景省下解析开销有点意义。其余时候 jsonb 全面碾压。

三、存取:-> 取 jsonb,->> 取文本

两个操作符要先分清,后面所有查询都建立在它们之上。

• -> :取出来还是 jsonb(可以继续往下钻)
• ->>:取出来是 text(到叶子了,变成普通字符串)

CREATE TABLE users (
  id   bigserial PRIMARY KEY,
  name text,
  meta jsonb
);

INSERT INTO users(name, meta) VALUES
  ('Alice', '{"age":30, "tags":["pg","go"], "addr":{"city":"上海"}}');

-- -> 取 jsonb:还能继续往下钻
SELECT meta -> 'addr' -> 'city' FROM users;   -- "上海"(带引号的 jsonb string)

-- ->> 取 text:拿去比较、拼接、展示
SELECT (meta -> 'addr') ->> 'city' FROM users; -- 上海(纯文本,无引号)

口诀:要继续钻路径用 ->,要拿到能比较/输出的值用 ->>。新手最容易踩的坑:用 -> 拿到 '"上海"'(带引号)去做 = '上海',永远匹配不上。

路径深的时候,用 #> 和 #>> 一次到位:

SELECT meta #>> '{addr,city}' FROM users;  -- 上海,等价于 (meta->'addr')->>'city'

四、查询与条件:@>、?、路径匹配

光会取值不够,业务里 90% 的需求是"按 JSON 内部字段过滤"。= 对 jsonb 是"整体相等",过滤内部字段要用专门的操作符。

1. @>(包含) ——最常用的条件操作符。左边 jsonb "包含"右边 jsonb 的结构。

-- meta 里有 "age":30
SELECT * FROM users WHERE meta @> '{"age":30}';

-- tags 数组里包含 "go"
SELECT * FROM users WHERE meta @> '{"tags":["go"]}';

-- 嵌套:addr.city = 上海
SELECT * FROM users WHERE meta @> '{"addr":{"city":"上海"}}';

@> 的语义是"右边的键值对都在左边里出现"——数组是"包含元素",对象是"递归包含"。它不管顺序,非常适合"标签命中""属性过滤"。

2. ?(键存在) ——判断顶层有没有某个键。

SELECT * FROM users WHERE meta ? 'age';                      -- 有 age 键
SELECT * FROM users WHERE meta ?| array['vip','corp'];       -- 任一存在
SELECT * FROM users WHERE meta ?& array['vip','corp'];       -- 全部存在

注意 ? 只看顶层键,钻不进嵌套。要查嵌套得配合 ->:meta -> 'addr' ? 'city'。

3. SQL/JSON 路径(jsonb_path_*) ——PG 12+ 引入的 jsonpath,表达力最强,支持条件表达式。

-- 找年龄大于 25 的用户,$ 是根变量,@ 是当前匹配元素
SELECT * FROM users WHERE jsonb_path_exists(meta, '$.age ? (@ > 25)');

关键认知:这些操作符默认都是全表扫描——PostgreSQL 不知道 jsonb 内部哪个值该走索引。要让它们起飞,必须配合下一节的 GIN 索引,而且查询用的操作符要和索引支持的操作符匹配(用 @> 就配 GIN)。

五、GIN 索引:让 JSONB 查询从秒级到毫秒级

没有索引的 jsonb 查询,等于把每一行的 jsonb 全部解析一遍做匹配,百万行就把 CPU 顶满。GIN(倒排索引)是 jsonb 的命脉。

最直接的建法:

CREATE INDEX idx_users_meta ON users USING GIN(meta);

默认 GIN 索引支持 @>、?、?|、?& 这一组操作符,体积稍大(为每个键值都建倒排项),但覆盖最广。

如果查询只用 @>,可以选更小的 jsonb_path_ops 操作符类——索引体积能小一半到几分之一,但只支持 @>(和 jsonpath):

CREATE INDEX idx_users_meta_path ON users USING GIN(meta jsonb_path_ops);

-- 这个查询能命中上面的索引
SELECT * FROM users WHERE meta @> '{"tags":["go"]}';

实战建议:既查键存在(?)又查包含(@>)用默认 GIN;只做 @> 过滤(最常见,比如属性匹配、标签命中)用 jsonb_path_ops,索引小、写入快、查询一样快。

表达式索引适合"只关心某个固定路径"的场景。如果你经常按 meta ->> 'city' 过滤/排序,直接对它建 B-tree:

CREATE INDEX idx_users_city ON users((meta ->> 'city'));

SELECT * FROM users WHERE meta ->> 'city' = '上海';  -- 走 B-tree,还能排序

比 GIN 整列更精准。判断标准:你是要"任意键都能查"(用 GIN),还是"只查固定某几个键"(用表达式索引)?

还有一个工程化利器:生成列。把常用路径物化成普通列,既能建普通索引,又能加约束:

ALTER TABLE users ADD COLUMN city text
  GENERATED ALWAYS AS (meta ->> 'city') STORED;

CREATE INDEX idx_users_city ON users(city);

写 jsonb 时 city 自动同步,读时按普通列查,索引/约束/JOIN 都能用。这是把"jsonb 的灵活"和"关系表的强"揉在一起的最佳粘合剂。

六、什么时候该用 JSONB

讲清了怎么用,更要讲清什么时候用。下面这些场景,JSONB 是对的工具:

1. 可变结构 / 自定义字段。多租户 SaaS 的"租户扩展属性"、表单引擎的"动态字段"、CMS 的"内容元数据"——结构因租户/模板而异,强塞关系表就是前面说的刺猬。
2. 事件 / 审计 / 埋点负载。埋点字段经常变(今天加 utm_source,明天加 device_id),把可变部分塞 jsonb,核心字段(id、时间、用户)留关系列,既好查也好索引。
3. 嵌套配置对象。feature flag 的配置、dashboard 的 layout、工作流定义——天然是树状结构,平铺成关系表反而难表达。
4. 整体读写、不常查内部字段。比如把整个 API 请求体存下来做审计,平时只按外部字段(时间、用户)查,内部 jsonb 几乎不参与过滤——这种 text 都能用,jsonb 多一个"将来想查能查上"的保险。

判断标准一句话:结构会变、内部字段不是查询主键、整体读写多于字段级操作——用 JSONB。

七、什么时候别用:老实拆关系表

JSONB 不是银弹,以下场景硬上会后悔:

1. 字段固定且是查询/排序主体。订单的金额、状态、时间——这种字段塞进 jsonb,等于放弃了 B-tree 的排序、范围扫描和统计信息。该独立成列。
2. 需要 JOIN 和聚合。SUM/GROUP BY/JOIN 对 jsonb 内部字段几乎无能为力(要先 ->> 物化出来再算,优化器帮不了你)。报表字段、对账字段必须拆成关系列。
3. 强一致性约束。外键、CHECK、UNIQUE、NOT NULL 在 jsonb 内部都不生效。你没法让数据库保证"每个用户的 age 必须是正整数"。要约束,就拆出来。
4. 频繁按内部字段更新。jsonb 更新是"整列重写"(MVCC 新版本),哪怕只改一个键。高频更新某个内部值,既慢又产生 bloat。这种字段必须独立成列走 HOT 更新。

一个实用边界:只要某个内部字段出现在 WHERE/ORDER BY/JOIN/GROUP BY 里,就认真考虑把它拎出来——要么独立列,要么生成列。留在 jsonb 里只图灵活,会拿查询性能和约束力去换。

八、反模式:别把 JSONB 当 MongoDB 用

最危险的用法是:看到 jsonb 这么灵活,干脆把整张表设计成 id + data jsonb。一个 users 表只有 id 和一大坨 jsonb,所有字段都塞进去。

这正是把 PostgreSQL 用成了"无 schema 的 Mongo"——而且更糟:你既失去了 Mongo 对文档查询的成熟工具链,又失去了关系数据库的约束、JOIN、外键。两边的好都没捞着。

后果是可预见的:

• 没人知道 jsonb 里到底有哪些键(没有 schema 文档,没有列名约束)
• 一个键写错了类型("age":"三十"),数据库不报错,查询静默失败
• 想给某个内部字段加唯一约束、加外键,做不到
• 查询性能全靠 GIN,没有关系表那种"按列精准索引"的细粒度
• 半年后接手的人想重构,不知道哪些键被代码引用,只能一行行 grep

正确的心智模型:jsonb 是关系表的"补充",不是"替代"。核心实体(用户、订单、账户)的关系结构稳如磐石,可变属性(配置、元数据、扩展字段)用 jsonb 灵活挂靠。一张表里:关系列当骨架,jsonb 列当弹性外挂,这才是 PostgreSQL 文档/关系混合能力的本意。

一句话收口:JSONB 解决的是"这一小撮字段结构会变"的问题,不是"整张表不要 schema"的借口。

小结

1. 新项目一律用 jsonb,不用 json:二进制存储、可索引、查询快,代价只是写入多一次解析。
2. 两个操作符先分清:-> 取 jsonb(能继续钻),->> 取 text(拿去比较);用错就永远匹配不上。
3. 过滤内部字段用 @>(包含)、?(键存在);这些操作符默认是全表扫,必须配 GIN 索引才能起飞。
4. GIN 是 jsonb 的命脉:通用查询用默认 GIN,只用 @> 用 jsonb_path_ops(更小);固定路径查询用表达式索引或生成列,还能加约束、能 JOIN。
5. 选型边界是核心:结构会变、整体读写、内部字段不常查——用 JSONB;字段固定、要排序/JOIN/聚合/约束——老实拆关系列。

金句:JSONB 是给关系表装上"弹性外挂",不是把关系表换成无 schema 的 Mongo——它补的是灵活,补不了纪律。