PostgreSQL / 系统课时

判断何时使用 JSONB

判断何时使用 JSONB

JSONB 不是“字段不确定时就塞进去”的万能容器。本课从查询方式、约束强度和数据关系出发,在固定列、关联表与 JSONB 之间做选择,并用一个商品动态属性模型验证什么数据适合进入 JSONB。

学习目标

  • 根据字段稳定性、查询方式和约束要求判断是否使用 JSONB
  • 区分应保留为固定列、拆成关联表和放入 JSONB 的数据
  • 为动态属性设计一个可查询且边界清晰的 JSONB 模型

先从数据的职责判断

设计表结构时,先问“这项数据如何被使用”,而不是先问“能不能放进 JSONB”。如果一个字段参与主键、外键、唯一约束、频繁排序、范围查询或核心业务计算,通常应该保留为普通列,因为关系模型能直接表达类型和完整性约束。

JSONB 更适合结构会随对象类型变化、字段集合稀疏,且多数属性以整体读取、少量条件过滤为主的数据。例如不同品类商品拥有不同参数:显示器有刷新率,键盘有轴体,固定为几十个可空列会迅速失控。

关联表则适合真正的多值实体关系。例如商品拥有多个标签、订单拥有多个明细,这些数据需要独立查询、关联、约束或维护生命周期,不应仅因为结构灵活就压入 JSONB 数组。

数据特征 更合适的建模方式 主要原因
字段稳定,频繁过滤、排序或约束 固定列 类型、约束和索引最直接
对象之间存在独立的一对多或多对多关系 关联表 可建立外键并独立查询
字段因对象类型而变化,属性稀疏 JSONB 避免大量空列并保留结构
数据几乎完全不查询,只原样归档 JSONB 或其他文档存储 重点是保存而非关系运算

建立一个边界清晰的模型

下面的商品表把稳定且高频使用的数据保留为普通列,仅将不同商品类型的动态属性放入 attributes。这是比“整行 JSON 化”更容易治理的折中方式。

sql
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;

预期结果

text
     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,后续查询与索引治理才有清晰边界。

按自己的节奏,确认真正理解。

目录与阅读设置

记录一个想法

判断何时使用 JSONB

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通