PHP小码农

Appearance

JSONB 典型应用场景 ​

前面五节把 JSONB 的操作、查询、更新、索引、聚合逐个讲透了。本节换个视角：在真实的表结构设计里，到底什么数据该放进 JSONB，什么数据不该放。结论先摆出来——JSONB 不是「万能列」，它解决的是「结构可变」这一特定问题；把关系型数据塞进去，只会既失去约束又拖慢查询。

前置知识

阅读本节前，建议先了解：JSON 与 JSONB 的对比

基础概念 ​

先建立一个判断标准：数据字段的结构稳定性与查询频率决定它该放哪。

维度倾向独立列（规范化）倾向 JSONB
结构字段固定、个数可枚举字段可变、事先不知道有哪些
查询高频筛选、排序、连接整体读取，或按需少量筛选
约束需要外键、唯一、非空内聚为一份文档，强一致要求低
变更加字段要 DDL 上线加字段只是写多一个键
典型金额、状态、时间、外键属性、配置、快照、事件负载

JSONB 真正擅长的四类场景：

场景说明代表字段
EAV 替代实体-属性-值模型（Entity-Attribute-Value），过去要三张表，现在一个 jsonb 列装下products.attributes
混合设计核心字段独立成列，扩展字段放 jsonb，兼顾约束与弹性orders + orders.extra
多租户自定义字段每个租户自己要一套字段，共用一张表tenants.custom_fields
快照与事件下单时的收货地址、事件负载等，写入后不再改，要求保真orders.shipping_snapshot

关键术语：

  • EAV（Entity-Attribute-Value）：把「属性名 + 属性值」拆成行的建模方式，灵活性高但查询复杂。jsonb 让它退化成「一行装所有属性」。
  • 混合设计（Hybrid Design）：主键、状态、时间等需要约束与索引的字段用独立列；不确定的扩展信息用 JSONB 兜住。
  • 模式漂移（Schema Drift）：同一列里不同行的结构逐渐不一致，是 JSONB 列最常见的长期问题。
  • 写时复制语义（Snapshot）：历史单据必须保留「当时的样子」，即使主数据后来改了。

语法与用法 ​

本节涉及的 DDL 手段集中在三类：

text
-- 1. 独立列 + jsonb 扩展字段（混合设计）
CREATE TABLE orders (
  id      bigint PRIMARY KEY,
  status  varchar(16) NOT NULL,          -- 需要约束与索引的字段
  extra   jsonb       NOT NULL DEFAULT '{}'::jsonb   -- 弹性字段
);

-- 2. 结构守护约束
CONSTRAINT ck_x_is_object CHECK (jsonb_typeof(extra) = 'object')
CONSTRAINT ck_x_size     CHECK (pg_column_size(extra) < 8192)
CONSTRAINT ck_x_required CHECK (extra ?& array['k1', 'k2'])

-- 3. 按查询模式建索引
CREATE INDEX idx_x_gin ON orders USING gin (extra);                       -- 结构包含
CREATE INDEX idx_x_expr ON orders ((extra ->> 'channel'));                -- 单值查询
CREATE INDEX idx_x_partial ON orders ((extra ->> 'channel'))
  WHERE status = 'pending';                                              -- 部分索引

判断一个字段该不该放进 JSONB，先回答三个问题：

text
1. 它的键是固定的吗？          固定 → 独立列
2. 它会被高频筛选/排序/连接吗？  会   → 独立列（或表达式索引）
3. 它需要外键/唯一约束吗？      需要 → 独立列
三个都答「否」，才是 JSONB 的好候选。

详细说明 ​

场景一：EAV 替代——商品动态属性 ​

电商的商品属性是典型场景：手机要「屏幕尺寸、电池容量」，服装要「尺码、面料、季节」。字段集合随品类变化，无法用固定列表达。

传统 EAV 要三张表：

text
-- 传统 EAV：三张表 + 每次查询都要多表连接
CREATE TABLE products        (id bigint PRIMARY KEY, name text);
CREATE TABLE attributes      (id bigint PRIMARY KEY, name text);
CREATE TABLE product_attr    (product_id bigint, attribute_id bigint, value text);

换成 jsonb 只需要一个列：

sql
DROP TABLE IF EXISTS products;

CREATE TABLE products (
  id         bigint      GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  sku        varchar(32) NOT NULL UNIQUE,
  name       varchar(128) NOT NULL,
  category   varchar(32)  NOT NULL,
  price      numeric(12, 2) NOT NULL,
  attributes jsonb       NOT NULL DEFAULT '{}'::jsonb,

  -- 守护结构：必须是对象，且必须带品牌与类目
  CONSTRAINT ck_attributes_is_object CHECK (jsonb_typeof(attributes) = 'object'),
  CONSTRAINT ck_attributes_required  CHECK (attributes ?& array['brand'])
);

-- 属性整体检索用的 GIN 索引
CREATE INDEX idx_products_attributes ON products USING gin (attributes);
text
DROP TABLE
CREATE TABLE
CREATE INDEX

写入不同品类的商品，各行结构完全不同，但都合法：

sql
INSERT INTO products (sku, name, category, price, attributes) VALUES
('PH-1001', '旗舰手机',   'phone',  4999.00,
 '{"brand": "Acme", "screen_inch": 6.7, "battery_mah": 5000, "nfc": true}'),
('CL-2001', '纯棉T恤',    'cloth',    99.00,
 '{"brand": "BasicWear", "size": ["S", "M", "L"], "fabric": "cotton", "season": "summer"}'),
('PH-1002', '入门手机',   'phone',  1299.00,
 '{"brand": "Acme", "screen_inch": 6.1, "battery_mah": 4000, "nfc": false}');
text
INSERT 0 3

按「属性 + 值」筛选，用 @> 一条语句搞定：

sql
-- 找出所有 Acme 品牌且支持 NFC 的商品
SELECT sku, name, attributes ->> 'brand' AS brand
FROM products
WHERE attributes @> '{"brand": "Acme", "nfc": true}'
ORDER BY sku;
text
   sku   |   name   | brand
---------+----------+-------
 PH-1001 | 旗舰手机 | Acme
(1 row)

数值比较（比如「电池容量大于 4500」）用 @> 表达不了，要换 jsonpath：

sql
SELECT sku, name, (attributes ->> 'battery_mah')::int AS battery_mah
FROM products
WHERE jsonb_path_exists(attributes, '$.battery_mah ? (@ > 4500)')
ORDER BY battery_mah DESC;
text
   sku   |   name   | battery_mah
---------+----------+-------------
 PH-1001 | 旗舰手机 |        5000
(1 row)

配上报文里常见的一句：@> 能用 GIN 索引，jsonb_path_exists 也能（jsonb_ops 操作符类），但表达式里带强制类型转换的写法不能走索引——(attributes ->> 'battery_mah')::int > 4500 只能顺序扫描。高频的数值筛选建议抽成独立列或表达式索引。

场景二：混合设计——订单的独立列与扩展字段 ​

订单表是最能体现「混合设计」价值的场景。订单号、状态、金额必须严格约束；而渠道、来源、营销标签等字段则因业务线而异。

sql
DROP TABLE IF EXISTS orders;

CREATE TABLE orders (
  -- 一、必须严格约束的核心字段：独立列
  id          bigint        GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id     bigint        NOT NULL,
  order_no    varchar(32)   NOT NULL UNIQUE,
  status      varchar(16)   NOT NULL DEFAULT 'pending',
  total       numeric(12, 2) NOT NULL DEFAULT 0,
  created_at  timestamptz   NOT NULL DEFAULT now(),

  -- 二、随业务变化的扩展字段：jsonb
  extra       jsonb         NOT NULL DEFAULT '{}'::jsonb,

  CONSTRAINT ck_orders_status CHECK (status IN ('pending', 'paid', 'shipped', 'closed')),
  CONSTRAINT ck_orders_extra_is_object CHECK (jsonb_typeof(extra) = 'object'),
  -- 守护体积：扩展信息不该超过 2 KB，超了说明该拆表
  CONSTRAINT ck_orders_extra_size CHECK (pg_column_size(extra) < 2048)
);

-- 状态 + 时间的组合是最高频的查询，建普通 B-tree 索引
CREATE INDEX idx_orders_status_created ON orders (status, created_at DESC);
-- 扩展字段里的渠道是固定查询入口，抽表达式索引
CREATE INDEX idx_orders_channel ON orders ((extra ->> 'channel'));
text
DROP TABLE
CREATE TABLE
CREATE INDEX
CREATE INDEX

关键设计原则：凡是出现在 WHERE / ORDER BY / JOIN 里高频使用的键，都要么提升为独立列，要么建表达式索引。反之，只参与展示的字段留在 jsonb 里即可。

sql
-- 渠道是高频筛选条件 → 表达式索引生效
EXPLAIN (ANALYZE, BUFFERS)
SELECT order_no, status, total
FROM orders
WHERE extra ->> 'channel' = 'app'
ORDER BY created_at DESC
LIMIT 20;
text
                                      QUERY PLAN
--------------------------------------------------------------------------------------
 Limit  (cost=24.35..24.40 rows=1 width=44) (actual time=0.048..0.049 rows=1 loops=1)
   Buffers: shared hit=6
   ->  Sort  (cost=24.35..24.36 rows=1 width=44) (actual time=0.047..0.048 rows=1 loops=1)
         Sort Key: created_at DESC
         Sort Method: quicksort  Memory: 25kB
         Buffers: shared hit=6
         ->  Bitmap Heap Scan on orders  (cost=16.11..24.34 rows=1 width=44) (actual time=0.026..0.033 rows=1 loops=1)
               Recheck Cond: ((extra ->> 'channel'::text) = 'app'::text)
               Heap Blocks: exact=1
               Buffers: shared hit=6
               ->  Bitmap Index Scan on idx_orders_channel  (cost=0.00..16.11 rows=1 width=0) (actual time=0.018..0.018 rows=1 loops=1)
                     Index Cond: ((extra ->> 'channel'::text) = 'app'::text)
                     Buffers: shared hit=3
 Planning Time: 0.243 ms
 Execution Time: 0.084 ms

要读的关键行：Bitmap Index Scan on idx_orders_channel 说明扩展字段上的表达式索引被用上了，Index Cond: ((extra ->> 'channel'::text) = 'app'::text) 表明条件被下推到了索引。若这里出现的是 Seq Scan on orders，说明表达式与索引定义不匹配（常见的写法差异是 extra->>'channel' 与索引里的 (extra ->> 'channel') 类型推导不一致，或者是用了 = 'app' 之外的隐式转换）。

场景三：多租户自定义字段 ​

SaaS 系统里，每个租户都想要自己的字段。给每个租户建一套表显然不可行，于是「公共列 + 租户自定义字段」的混合设计成为标准做法。

text
tenants.custom_fields 存「字段定义」（有哪些字段、什么类型）
users.custom_values   存「字段值」（每个用户填了什么）
sql
DROP TABLE IF EXISTS tenant_field_defs;
DROP TABLE IF EXISTS user_profiles;

CREATE TABLE tenant_field_defs (
  tenant_id   bigint      NOT NULL,
  field_key   varchar(64) NOT NULL,
  field_label varchar(64) NOT NULL,
  field_type  varchar(16) NOT NULL,
  required    boolean     NOT NULL DEFAULT false,
  PRIMARY KEY (tenant_id, field_key),
  CONSTRAINT ck_field_type CHECK (field_type IN ('text', 'number', 'date', 'bool'))
);

CREATE TABLE user_profiles (
  id         bigint      GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id  bigint      NOT NULL,
  user_id    bigint      NOT NULL,
  base       jsonb       NOT NULL DEFAULT '{}'::jsonb,   -- 全租户共有的字段
  custom     jsonb       NOT NULL DEFAULT '{}'::jsonb,   -- 租户自定义字段值
  UNIQUE (tenant_id, user_id)
);
text
DROP TABLE
DROP TABLE
CREATE TABLE
CREATE TABLE

「必填自定义字段是否都填了」这类校验交给 SQL 做，比在应用层循环判断可靠：

sql
-- 按租户的定义，找出漏填必填字段的用户
SELECT p.tenant_id, p.user_id, d.field_key
FROM user_profiles p
JOIN tenant_field_defs d
  ON d.tenant_id = p.tenant_id
 AND d.required = true
WHERE NOT (p.custom ? d.field_key)
ORDER BY p.tenant_id, p.user_id, d.field_key;
text
 tenant_id | user_id | field_key
-----------+---------+-----------
         1 |    1001 | department
(1 row)

也可以把校验固化到触发器里（这里只给出关键片段）：

sql
CREATE OR REPLACE FUNCTION check_required_custom_fields() RETURNS trigger AS $$
DECLARE
  missing text;
BEGIN
  SELECT string_agg(d.field_key, ', ')
    INTO missing
  FROM tenant_field_defs d
  WHERE d.tenant_id = NEW.tenant_id
    AND d.required = true
    AND NOT (NEW.custom ? d.field_key);

  IF missing IS NOT NULL THEN
    RAISE EXCEPTION '缺少必填自定义字段: %', missing;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;
text
CREATE FUNCTION

⚠️ 注意上面这个触发器要生效，还得 CREATE TRIGGER ... BEFORE INSERT OR UPDATE，这里从略——本节的重点是把「结构定义」与「结构校验」也纳入数据库，而不是留一个无约束的 jsonb 列任应用层乱写。

场景四：快照与事件负载 ​

两类数据天生适合 JSONB：一旦写入就不再修改的快照，和结构天然开放的事件。

订单的收货地址是快照的经典例子。用户可能半年后改了默认地址，但历史订单必须保留当时那份：

text
orders.shipping_snapshot = '{"name": "张三", "phone": "138****", "province": "浙江", "city": "杭州", "detail": "..."}'

关键点是不能用外键指向 user_addresses。一旦用户删了旧地址，历史订单就断链了。快照必须是一份完整的自包含副本：

sql
SELECT
  order_no,
  shipping_snapshot ->> 'name'   AS receiver,
  shipping_snapshot #>> '{province}' AS province
FROM orders
WHERE extra ->> 'channel' = 'app';

事件负载同理。埋点事件的字段随版本迭代不断新增，用固定列会天天 ALTER TABLE：

sql
DROP TABLE IF EXISTS user_events;

CREATE TABLE user_events (
  id          bigint      GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id     bigint      NOT NULL,
  event_type  varchar(32) NOT NULL,
  payload     jsonb       NOT NULL DEFAULT '{}'::jsonb,
  occurred_at timestamptz NOT NULL DEFAULT now()
);

-- 事件类型 + 时间是最常用的切片维度
CREATE INDEX idx_events_type_time ON user_events (event_type, occurred_at DESC);
-- 负载内的少数固定键抽表达式索引
CREATE INDEX idx_events_sku ON user_events ((payload ->> 'sku'))
  WHERE event_type = 'add_to_cart';
text
DROP TABLE
CREATE TABLE
CREATE INDEX
CREATE INDEX

注意最后那个部分索引：只有 add_to_cart 事件才有 sku 字段，所以把索引限定在 WHERE event_type = 'add_to_cart' 上，索引体积和写入代价都小得多。

反模式：这些数据不要放进 JSONB ​

反面清单比正面清单更重要。以下写法在线上事故里反复出现：

反模式为什么错正确做法
把用户表整体塞进一个 jsonb 列失去外键与唯一约束，无法连接核心实体用独立列/独立表
jsonb 里的键当成外键用数据库无法保证引用完整性提取为真实的 bigint 外键列
金额、状态放 jsonb无法加 CHECK，统计时要反复转换提升为 numeric / varchar 独立列
高频 WHERE doc->>'k' = v 无索引每次全表扫描 + 逐行解析建表达式索引或提升为列
一个文档几百 KB 还在高频更新整值重写 + TOAST + 索引写放大拆成多行/多表
用 jsonb 存有序列表当数组用顺序不保证，去重语义混乱用 text[] / 关联表 + sort_order
sql
-- 错误！把用户核心字段塞进 JSONB，等于放弃了数据库的全部约束能力
CREATE TABLE bad_users (
  id   bigint PRIMARY KEY,
  data jsonb     -- 里面有 username / email / status / role ...
);

-- 正确：核心字段独立，扩展字段才用 jsonb
CREATE TABLE users (
  id         bigint      GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  username   varchar(64) NOT NULL UNIQUE,     -- 唯一约束有意义
  email      varchar(128),                    -- 可加格式校验
  status     varchar(16) NOT NULL DEFAULT 'active',
  extra      jsonb       NOT NULL DEFAULT '{}'::jsonb
);

一条经验法则：如果某个键在每个租户/每个用户那里都必然存在且类型相同，它就应该是一个独立列。 JSONB 只留给「可能不存在、存在时类型还可能不同」的那部分。

实战示例 ​

下面把「混合设计」落到一个完整可跑的流程里：建表、写数据、分别验证独立列索引与 jsonb 索引、用聚合拼出 API 响应、最后查看体积。

第一步：建表与索引

sql
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS users;

CREATE TABLE users (
  id         bigint      GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  username   varchar(64) NOT NULL UNIQUE,
  email      varchar(128),
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE orders (
  id                bigint        GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id           bigint        NOT NULL REFERENCES users (id),
  order_no          varchar(32)   NOT NULL UNIQUE,
  status            varchar(16)   NOT NULL DEFAULT 'pending',
  total             numeric(12, 2) NOT NULL DEFAULT 0,
  shipping_snapshot jsonb         NOT NULL DEFAULT '{}'::jsonb,
  extra             jsonb         NOT NULL DEFAULT '{}'::jsonb,
  created_at        timestamptz   NOT NULL DEFAULT now(),

  CONSTRAINT ck_orders_status CHECK (status IN ('pending', 'paid', 'shipped', 'closed')),
  CONSTRAINT ck_orders_snapshot_is_object CHECK (jsonb_typeof(shipping_snapshot) = 'object'),
  CONSTRAINT ck_orders_extra_is_object    CHECK (jsonb_typeof(extra) = 'object'),
  CONSTRAINT ck_orders_extra_size         CHECK (pg_column_size(extra) < 2048)
);

CREATE INDEX idx_orders_status_created ON orders (status, created_at DESC);
CREATE INDEX idx_orders_channel        ON orders ((extra ->> 'channel'));
CREATE INDEX idx_orders_tags_gin       ON orders USING gin ((extra -> 'tags'));
CREATE INDEX idx_orders_snapshot_city  ON orders ((shipping_snapshot ->> 'city'));
text
DROP TABLE
DROP TABLE
CREATE TABLE
CREATE TABLE
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX

第二步：写入数据

sql
INSERT INTO users (username, email) VALUES
('alice', 'alice@example.com'),
('bob',   'bob@example.com'),
('carol', 'carol@example.com');

INSERT INTO orders (user_id, order_no, status, total, shipping_snapshot, extra) VALUES
(1, 'SO-2026-0001', 'paid',    598.00,
 '{"name": "张三", "province": "浙江", "city": "杭州"}',
 '{"channel": "app",    "tags": ["promo", "vip"], "coupon": "NEW50"}'),
(1, 'SO-2026-0002', 'pending', 179.70,
 '{"name": "张三", "province": "浙江", "city": "杭州"}',
 '{"channel": "web",    "tags": ["digital"]}'),
(2, 'SO-2026-0003', 'paid',    224.00,
 '{"name": "李四", "province": "广东", "city": "深圳"}',
 '{"channel": "app",    "tags": ["vip"]}'),
(3, 'SO-2026-0004', 'shipped',  99.00,
 '{"name": "王五", "province": "北京", "city": "北京"}',
 '{"channel": "mini",   "tags": []}');
text
INSERT 0 3
INSERT 0 4

第三步：独立列查询——走 B-tree 索引

sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT order_no, total
FROM orders
WHERE status = 'paid'
  AND created_at > now() - interval '30 day'
ORDER BY created_at DESC;
text
                                         QUERY PLAN
--------------------------------------------------------------------------------------------
 Index Scan using idx_orders_status_created on orders  (cost=0.14..8.16 rows=1 width=24) (actual time=0.017..0.019 rows=2 loops=1)
   Index Cond: (((status)::text = 'paid'::text) AND (created_at > (now() - '30 days'::interval)))
   Buffers: shared hit=3
 Planning Time: 0.187 ms
 Execution Time: 0.046 ms

Index Scan using idx_orders_status_created + Index Cond 里的两个条件同时出现，说明独立列上的组合索引完全命中了查询。这是独立列的核心价值：一条索引直接服务整个 WHERE + ORDER BY。

第四步：扩展字段查询——走表达式索引与 GIN 索引

sql
-- 4.1 表达式索引：按渠道筛选
EXPLAIN (ANALYZE, BUFFERS)
SELECT order_no FROM orders WHERE extra ->> 'channel' = 'app';

-- 4.2 GIN 索引：按标签数组筛选
EXPLAIN (ANALYZE, BUFFERS)
SELECT order_no FROM orders WHERE extra -> 'tags' @> '["vip"]'::jsonb;
text
-- 4.1
                                        QUERY PLAN
-------------------------------------------------------------------------------------------
 Bitmap Heap Scan on orders  (cost=16.11..24.34 rows=1 width=11) (actual time=0.023..0.028 rows=2 loops=1)
   Recheck Cond: ((extra ->> 'channel'::text) = 'app'::text)
   Heap Blocks: exact=1
   Buffers: shared hit=4
   ->  Bitmap Index Scan on idx_orders_channel  (cost=0.00..16.11 rows=1 width=0) (actual time=0.013..0.013 rows=2 loops=1)
         Index Cond: ((extra ->> 'channel'::text) = 'app'::text)
 Planning Time: 0.221 ms
 Execution Time: 0.053 ms

-- 4.2
 Bitmap Heap Scan on orders  (cost=20.24..32.47 rows=2 width=11) (actual time=0.031..0.036 rows=2 loops=1)
   Recheck Cond: ((extra -> 'tags'::text) @> '["vip"]'::jsonb)
   Heap Blocks: exact=1
   Buffers: shared hit=4
   ->  Bitmap Index Scan on idx_orders_tags_gin  (cost=0.00..20.24 rows=2 width=0) (actual time=0.015..0.015 rows=2 loops=1)
         Index Cond: ((extra -> 'tags'::text) @> '["vip"]'::jsonb)
 Planning Time: 0.233 ms
 Execution Time=0.061 ms

两条计划都出现了 Bitmap Index Scan。这里有个容易忽略的细节：4.2 的索引建在 (extra -> 'tags') 这个子表达式上，所以查询里也必须写成 extra -> 'tags' @> ...，不能写成 extra @> '{"tags": ["vip"]}'——后者虽然语义等价，但表达式不匹配索引定义，会退化成对整列 GIN 索引的扫描（如果没有整列索引，就是顺序扫描）。

第五步：快照查询——按收货城市统计

sql
SELECT
  shipping_snapshot ->> 'city' AS city,
  count(*)                     AS order_count,
  sum(total)                   AS amount
FROM orders
WHERE status <> 'closed'
GROUP BY shipping_snapshot ->> 'city'
ORDER BY amount DESC;
text
 city | order_count |  amount
------+-------------+---------
 杭州 |           2 |  777.70
 深圳 |           1 |  224.00
 北京 |           1 |   99.00
(3 rows)

第六步：拼装分层 API 响应

把独立列与扩展字段合并成一份前端直接可用的结构：

sql
SELECT jsonb_build_object(
         'order_no',  o.order_no,
         'status',    o.status,
         'total',     o.total,
         'channel',   o.extra ->> 'channel',
         'tags',      coalesce(o.extra -> 'tags', '[]'::jsonb),
         'receiver',  jsonb_build_object(
                        'name', o.shipping_snapshot ->> 'name',
                        'city', o.shipping_snapshot ->> 'city'
                      )
       ) AS payload
FROM orders o
WHERE o.user_id = 1
ORDER BY o.created_at DESC;
text
                                      payload
-----------------------------------------------------------------------------------
 {"tags": ["promo", "vip"], "total": 598.00, "status": "paid", "receiver": {"name": "张三", "city": "杭州"}, "channel": "app", "order_no": "SO-2026-0001"}
 {"tags": ["digital"], "total": 179.70, "status": "pending", "receiver": {"name": "张三", "city": "杭州"}, "channel": "web", "order_no": "SO-2026-0002"}
(2 rows)

第七步：体积体检

判断 jsonb 列是否已经过胖，直接量一量：

sql
SELECT
  count(*)                                          AS rows,
  pg_size_pretty(sum(pg_column_size(extra)))        AS extra_total,
  pg_size_pretty(avg(pg_column_size(extra))::bigint) AS extra_avg,
  pg_size_pretty(sum(pg_column_size(shipping_snapshot))) AS snapshot_total
FROM orders;
text
 rows | extra_total | extra_avg | snapshot_total
------+-------------+-----------+----------------
    4 | 164 bytes   | 41 bytes  | 132 bytes
(1 row)

单行几十字节时 jsonb 完全无压力；当 extra_avg 涨到几 KB 以上，就要考虑拆列或拆表了。

第八步：清理

sql
DROP TABLE orders;
DROP TABLE users;
text
DROP TABLE
DROP TABLE

注意事项 ​

  • 不要把核心实体整体塞进 jsonb。一旦这么做，外键、唯一约束、非空约束全部失效，数据库退化成「带 SQL 接口的文件柜」。核心字段必须是独立列。

  • jsonb 里的键不能当外键。extra ->> 'user_id' 里的值数据库无法校验引用完整性，脏数据会静默累积。需要引用就提升为 bigint 列并加 REFERENCES。

  • 匹配不到索引定义就会退化成顺序扫描。表达式索引要求查询表达式与索引定义逐字对应：

    sql
    -- 索引：ON orders ((extra ->> 'channel'))
WHERE extra ->> 'channel' = 'app'          -- 命中
WHERE (extra ->> 'channel')::text = 'app'  -- 不一定命中（多了显式转换）
WHERE extra @> '{"channel": "app"}'        -- 不命中（表达式不同）

  • jsonb 列上不要放高频更新的热点数据。计数器、最后活跃时间这类字段放进去会引发整值重写，应提升为独立列。

  • CHECK (jsonb_typeof(col) = 'object') 是必加的基础约束。没有它，一次 || 的意外就能把列变成数组，后续所有查询一起坏掉。

  • 模式漂移要主动监控。同一列的键集合会随时间漂移，用下面的查询定期体检：

    sql
    -- 统计每个键出现的行数，观察是否出现意料之外的新键
SELECT kv.key, count(*) AS row_count
FROM orders o
CROSS JOIN LATERAL jsonb_object_keys(o.extra) AS kv(key)
GROUP BY kv.key
ORDER BY row_count DESC;

  • 数组型 jsonb 不保证顺序，也不做去重。要「有序列表」就用 text[] 配独立的排序列；要「去重集合」就在写入前显式去重，别指望 jsonb 帮你。

  • 快照字段不要用外键指向主数据表。历史单据的价值就在于「不随主数据变化」，用外键等于把这份价值扔掉了。

  • 大文档会触发 TOAST。超过约 2 KB 的 jsonb 会被压缩/行外存储，读取变慢、更新代价更高。pg_column_size 超过这个量级就要审视设计。

  • jsonb 不能替代全文检索。要搜文本内容应该用 tsvector + GIN，对 jsonb 内的字符串做 LIKE '%x%' 是全表扫描。

  • 多租户场景一定要把 tenant_id 做成独立列并参与索引。放进 jsonb 会影响分区裁剪与行级安全策略（RLS）的表达式匹配，也拖慢所有查询。

最佳实践 ​

  • 默认从规范化开始，只在必要时引入 jsonb。先把字段建成独立列，等真的遇到「结构不确定」的需求再迁一部分过去，比一开始全放 jsonb 再往回搬要容易得多。

  • 采用混合设计：核心独立列 + jsonb 扩展字段，并在列注释里写明「哪些键是约定键、哪些是自由键」：

    sql
    COMMENT ON COLUMN orders.extra IS
  '约定键: channel(渠道), tags(数组), coupon(券码)；其余键为业务线自由扩展';

  • 给每个 jsonb 列配三件套约束：类型守护、体积上限、必需键集合：

    sql
    CONSTRAINT ck_extra_is_object CHECK (jsonb_typeof(extra) = 'object'),
CONSTRAINT ck_extra_size      CHECK (pg_column_size(extra) < 2048),
CONSTRAINT ck_extra_required  CHECK (extra ?& array['channel'])

  • 按查询模式选索引类型，不要盲目建全列 GIN：

    查询形态索引选择
    extra ->> 'k' = v（单值等值）表达式 B-tree 索引
    extra @> '{"k": v}'（结构包含）整列 GIN（jsonb_ops）
    extra -> 'arr' @> '[...]'（数组包含）子表达式 GIN
    只在某状态下的特定查询部分索引 WHERE ...

  • 高频键提升为独立列。同一个键在 WHERE 里出现得越频繁，越应该离开 jsonb。判断标准很直白：能建索引的键，不如直接变成列。

  • 快照类数据用 NOT NULL DEFAULT '{}' 并写入完整副本，绝不引用其他表。宁可多存一份冗余，也不要让历史单据在半年后变成空指针。

  • 多租户表把 tenant_id 放在第一列并作为所有索引的前导列，这样既服务查询，也方便后续按租户分区。

  • 定期做模式漂移体检与体积体检，把第一节里那两条查询做成巡检项：

    sql
    -- 体积 Top 10，超过阈值就该拆
SELECT id, pg_column_size(extra) AS bytes
FROM orders ORDER BY bytes DESC LIMIT 10;

  • 迁移路径要从头设计。如果某个键从「自由扩展」变成了「核心字段」，提前准备好迁移脚本：

    sql
    -- 1. 加列
ALTER TABLE orders ADD COLUMN channel varchar(16);
-- 2. 回填
UPDATE orders SET channel = extra ->> 'channel' WHERE extra ? 'channel';
-- 3. 加约束与新索引
ALTER TABLE orders ALTER COLUMN channel SET NOT NULL;
CREATE INDEX idx_orders_channel_col ON orders (channel);
-- 4. 建表达式索引替代旧索引
DROP INDEX idx_orders_channel;

  • 关键查询上线前用 EXPLAIN (ANALYZE, BUFFERS) 逐条验证，确认没有意外的 Seq Scan，尤其是引用了 jsonb 子表达式的条件。

下一节

继续学习：数组声明与初始化

参考链接 ​