PostgreSQL / 系统课时
判断何时使用 JSONB
判断何时使用 JSONB
JSONB 不是“字段不确定时就塞进去”的万能容器。本课从查询方式、约束强度和数据关系出发,在固定列、关联表与 JSONB 之间做选择,并用一个商品动态属性模型验证什么数据适合进入 JSONB。
学习目标
- 根据字段稳定性、查询方式和约束要求判断是否使用 JSONB
- 区分应保留为固定列、拆成关联表和放入 JSONB 的数据
- 为动态属性设计一个可查询且边界清晰的 JSONB 模型
先从数据的职责判断
设计表结构时,先问“这项数据如何被使用”,而不是先问“能不能放进 JSONB”。如果一个字段参与主键、外键、唯一约束、频繁排序、范围查询或核心业务计算,通常应该保留为普通列,因为关系模型能直接表达类型和完整性约束。
JSONB 更适合结构会随对象类型变化、字段集合稀疏,且多数属性以整体读取、少量条件过滤为主的数据。例如不同品类商品拥有不同参数:显示器有刷新率,键盘有轴体,固定为几十个可空列会迅速失控。
关联表则适合真正的多值实体关系。例如商品拥有多个标签、订单拥有多个明细,这些数据需要独立查询、关联、约束或维护生命周期,不应仅因为结构灵活就压入 JSONB 数组。
| 数据特征 | 更合适的建模方式 | 主要原因 |
|---|---|---|
| 字段稳定,频繁过滤、排序或约束 | 固定列 | 类型、约束和索引最直接 |
| 对象之间存在独立的一对多或多对多关系 | 关联表 | 可建立外键并独立查询 |
| 字段因对象类型而变化,属性稀疏 | JSONB | 避免大量空列并保留结构 |
| 数据几乎完全不查询,只原样归档 | JSONB 或其他文档存储 | 重点是保存而非关系运算 |
建立一个边界清晰的模型
下面的商品表把稳定且高频使用的数据保留为普通列,仅将不同商品类型的动态属性放入 attributes。这是比“整行 JSON 化”更容易治理的折中方式。
CREATE TABLE products (
product_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
sku text NOT NULL UNIQUE,
category text NOT NULL,
price numeric(12, 2) NOT NULL CHECK (price >= 0),
attributes jsonb NOT NULL DEFAULT '{}'::jsonb
);
INSERT INTO products (sku, category, price, attributes)
VALUES
('MONITOR-27', 'monitor', 2199.00,
'{"resolution":"2560x1440","refresh_hz":165,"hdr":true}'),
('KEYBOARD-87', 'keyboard', 599.00,
'{"switch":"red","layout":"87-key","wireless":true}');
SELECT sku, category, attributes ->> 'resolution' AS resolution
FROM products
ORDER BY sku;预期结果
sku | category | resolution
--------------+----------+------------
KEYBOARD-87 | keyboard |
MONITOR-27 | monitor | 2560x1440这里 sku、category 和 price 仍然是强类型列,可以直接施加唯一性、检查约束并进行常规查询;只有每个品类不一致的属性进入 JSONB。实际列宽与 psql 对齐方式可能不同。
识别不适合 JSONB 的信号
当某个 JSONB 键逐渐成为所有记录都有的必填属性,并频繁参与 WHERE、JOIN、ORDER BY 或统计计算时,它已经开始表现得像正式列。继续把它藏在 JSONB 中,会增加类型转换、表达式索引和数据校验的复杂度,此时应评估迁移为普通列。
如果 JSONB 内部保存的是可独立存在的实体,也要警惕模型退化。例如把订单明细全部保存成一个数组,会让商品外键、单条明细更新以及跨订单汇总变得更难。这类数据通常应该拆到明细表中。
另一个信号是需要大量跨字段一致性规则。普通列可以直接使用 NOT NULL、CHECK、UNIQUE 和外键;JSONB 虽然也能通过表达式和约束检查部分内容,但规则越复杂,维护成本越高。
用三个问题完成选择
面对一个候选字段,可以依次判断:它是否属于核心且稳定的业务属性;它是否代表需要独立约束和关联的实体;如果两者都不是,它的结构是否真的会随对象类型变化。前两种情况分别优先考虑固定列和关联表,只有第三种情况通常适合 JSONB。
一个可持续的 JSONB 模型不是追求“最灵活”,而是明确哪些数据允许灵活。把稳定事实留给关系模型,把真正动态、稀疏且半结构化的属性交给 JSONB,后续查询与索引治理才有清晰边界。