极客工场

Appearance

共 1,475 字预计阅读约 5 分钟

PostgreSQL JSONB 存储与 GIN 索引性能 ​

更新时间：2026-09-18。PostgreSQL 的半结构化能力主要靠 JSONB，而不是关系库常见的「JSON 文本 + 弱函数」。本篇讲透 JSONB 为什么快、GIN 索引的两种 opclass 怎么选、什么时候改用表达式索引，以及 JSONB 更新的真实代价。索引家族全景见 PostgreSQL 索引类型，执行计划验证见 EXPLAIN (ANALYZE, BUFFERS)。

本文要回答的问题 ​

  • json 和 jsonb 到底差在哪？为什么几乎总该用 jsonb？
  • 大 JSONB 怎么存？TOAST 何时介入？
  • GIN 的 jsonb_ops 与 jsonb_path_ops 索引大小、支持操作符、性能差多少？
  • 什么时候 GIN 不如「针对某条路径的表达式索引」？
  • 频繁整体更新一个大 JSONB 有什么代价？怎么设计才省？

一、json 与 jsonb：存文本 vs 存解析后的二进制 ​

维度jsonjsonb
存储原始文本，逐字保真解析后的二进制，键去重、去空格
写入快（原样存）略慢（要解析+序列化）
查询每次现解析预解析，查询快
键顺序/重复键保留（保留最后一个语义但存储都在）不保证顺序、重复键取最后
可建 GIN否（操作符受限）是
适用纯存档、几乎不在库里查绝大多数场景
sql
CREATE TABLE events (id bigserial PRIMARY KEY, payload jsonb);

-- 提取运算符
SELECT payload->'user'->>'name'      FROM events LIMIT 1;  -- -> 得 jsonb，->> 得 text
SELECT payload#>>'{user,address,city}' FROM events LIMIT 1; -- 路径取 text
SELECT payload @> '{"event":"login"}'::jsonb;               -- 包含（可走 GIN）
SELECT payload ? 'vip';                                     -- 是否含顶层键

经验：只要会在 WHERE 里按 JSON 内容过滤、或要建索引，就用 jsonb；只有「只写入不查询、要求字节级保真」才用 json。

二、大对象怎么落盘：TOAST ​

单个元组不能跨多个堆页（页 8KB，行实际目标约 2KB）。超大的 JSONB 字段会被 TOAST 机制处理：先压缩，再切片存到独立的 TOAST 表，主表里只放一个指向 TOAST 的引用。

sql
-- 看某字段是否被 TOAST 以及压缩策略（PG14+ 支持 LZ4）
SELECT relname, attname, attstorage, attcompression
FROM pg_attribute a JOIN pg_class c ON c.oid=a.attrelid
WHERE c.relname='events' AND attname='payload';
-- attstorage: x=外部(TOAST), m=压缩+外部, p=普通, e=仅外部不压缩

SELECT pg_column_size(payload) AS total,
       octet_length(payload::text) AS text_len
FROM events ORDER BY total DESC LIMIT 5;

坑一：改大 JSONB 的一个小键，也要重写整个 TOAST 外部值。 jsonb 没有「就地改子树」，jsonb_set 产生一个新值，若值超 TOAST 阈值，整个外部对象被重写并伴随多版本死元组。对超大、高频局部更新的文档，应拆表：把频繁变动的小字段提为独立列/独立行，而不是塞进一个巨型 JSONB 反复整体改。这与 MVCC 更新膨胀 是同一类代价。

三、GIN 的两种 opclass：默认 vs path_ops ​

sql
-- A) 默认 jsonb_ops：为每个键和值都建倒排项
CREATE INDEX idx_events_payload_gin ON events USING gin (payload);

-- B) jsonb_path_ops：对“路径+值”做哈希，索引项更少
CREATE INDEX idx_events_payload_path ON events USING gin (payload jsonb_path_ops);
维度jsonb_ops（默认）jsonb_path_ops
支持操作符@> <@ ? `??&` 等
索引体积较大（键、值分别建项）明显更小（哈希路径值）
查询速度快通常更快、更省空间
能否查「键存在」能（? 系列）不能

选择规则：

  • 主要做包含查询 @>（最常见）→ 用 jsonb_path_ops，索引小、写放大小。
  • 还要查「有没有这个键」(?/?|/?&) → 只能用默认 jsonb_ops。
sql
-- 典型包含查询（两种索引都能服务 @>）
SELECT count(*) FROM events
WHERE payload @> '{"event":"pay","channel":"wx"}'::jsonb;

坑二：GIN 写放大与 pending list。 默认 fastupdate=on 时，新写入先进一个未合并的 pending 区域，查询时既要查主索引又要扫 pending list；若 pending 长期不合并（autovacuum 不及时），点查延迟会抖。批量导入应先 DROP GIN、导完重建：

sql
-- 大批量加载更快的姿势
DROP INDEX IF EXISTS idx_events_payload_path;
COPY events(payload) FROM '/data/events.jsonl';   -- 批量灌入
CREATE INDEX idx_events_payload_path ON events USING gin (payload jsonb_path_ops);
ANALYZE events;

四、什么时候不用 GIN：表达式索引更省 ​

GIN 索引整个文档，体积可能很大。如果业务只按固定的一两条路径过滤，针对该路径建更小的 B-tree/布尔表达式索引往往更优：

sql
-- 只按 payload->>'userId' 等值/范围查：B-tree 表达式索引更小、更省写
CREATE INDEX idx_events_userid ON events ((payload->>'userId'));
SELECT * FROM events WHERE payload->>'userId' = '8848';

-- 常按某数值路径范围查：转 numeric
CREATE INDEX idx_events_amount ON events (((payload->>'amount')::numeric));
SELECT * FROM events WHERE (payload->>'amount')::numeric > 100;

-- 布尔/标签热路径：部分索引进一步缩小
CREATE INDEX idx_events_pay ON events(id)
WHERE payload @> '{"event":"pay"}'::jsonb;
方案索引大小适合
GIN jsonb_path_ops中~大多种键路径、临时探索式包含查询
GIN jsonb_ops大还要按键存在性查
B-tree 表达式索引小固定路径的等值/范围、排序
部分索引很小只关心少部分行

判据：查询模式稳定 → 表达式/部分索引；查询路径多变、文档结构不固定 → GIN。

五、用执行计划验证是否真的命中 ​

sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*) FROM events
WHERE payload @> '{"event":"pay"}'::jsonb;
-- 期望看到：
-- Bitmap Heap Scan on events
--   ->  Bitmap Index Scan on idx_events_payload_path
--        Index Cond: (payload @> ...)

如果仍走 Seq Scan，按 EXPLAIN 解读 的顺序排查：① 表太小（顺序扫更便宜，属正常）；② 选择性不足（命中行太多本就该扫表）；③ 统计信息/成本参数（random_page_cost、effective_cache_size）让优化器嫌弃索引；④ opclass 与操作符不匹配（如在 path_ops 上用 ?）。

六、建模建议小结 ​

  1. 默认用 jsonb，并想清楚文档边界；不要把本该是关系行的东西硬塞 JSON（丢失 JOIN、约束与规范化）。
  2. 主要包含查 @> 选 jsonb_path_ops；需要键存在性查再上默认 GIN。
  3. 固定热路径用表达式 B-tree / 部分索引，别用大 GIN 覆盖一切。
  4. 超大 JSONB 的高频局部更新要拆字段/拆表，避免 TOAST 整体重写 + 多版本膨胀。
  5. 批量加载先删 GIN 再重建，加载后 ANALYZE。
  6. 用 EXPLAIN (ANALYZE, BUFFERS) 验证，而非想当然。

七、相关与延伸 ​

索引家族与 GIN 原理见 PostgreSQL 索引类型；计划验证见 EXPLAIN (ANALYZE, BUFFERS)；JSONB 更新带来的死元组治理见 VACUUM 与表膨胀治理；MySQL 半结构化能力对照见 PostgreSQL 与 MySQL 差异。

总结 ​

PostgreSQL JSONB 与 GIN 索引：jsonb 以二进制预解析存储、键去重、可索引，几乎总优于 json；超大值走 TOAST 且局部更新会整体重写故大文档要拆表；包含查询 @> 用更小更快的 jsonb_path_ops，需要键存在性 ? 才用默认 jsonb_ops，固定热路径改用更小的 B-tree 表达式/部分索引；GIN 写放大高，批量导入先删后建，最后用 EXPLAIN (ANALYZE,BUFFERS) 确认命中。