实测 3 万字符 JSONB：PG TOAST 如何影响查询

问题背景：JSONB 很大时，数据页里到底发生了什么

很多业务把审计日志、接口报文、规则配置直接存进 JSONB。字段只有一列，逻辑上很简单，但当单条文档达到 3 万字符，查询延迟、缓存命中和更新代价都会发生明显变化。原因不在 JSONB 的语法，而在 PostgreSQL 的页面限制与 TOAST 机制。

PostgreSQL 默认数据页为 8KB，普通堆元组不能无限变大。当一行超过 TOAST 阈值，数据库会先尝试压缩，再把过大的字段切片存到专属 TOAST 表，主表只保留一个指向外部数据的引用。对于 JSONB 这类可变长、可压缩的大字段，这就是典型场景。

下面用可复现实验观察：3 万字符 JSONB 是留在主表、被压缩，还是被移到 TOAST 表；不同查询方式又会带来多少额外 I/O。

TOAST 工作机制：压缩优先，超限后行外存储

TOAST 是 The Oversized-Attribute Storage Technique 的缩写。每个包含超大字段的主表都可能拥有一张关联 TOAST 表。写入或更新时，大致经历如下流程：

需要强调，TOAST 对用户透明，SQL 仍然查询主表，但透明不等于免费。读取不需要大字段时，PostgreSQL 通常不必访问 TOAST 数据；一旦 SELECT payload、把大字段传给函数，或更新整行，就可能产生解压、TOAST 索引查找和多个 chunk 的读取。

JSONB 默认存储策略通常是 xmain，即允许压缩和行外存储。也可以通过 ALTER TABLE ... ALTER COLUMN ... SET STORAGE 调整，但普通业务一般不应贸然禁用压缩或外存。

方案对比：整列存放、拆分扩展字段与索引投影

面对 3 万字符 JSONB，常见设计有三种。

第一种是整列存放，优点是写入简单、事务一致性强、JSONB 操作灵活；缺点是主表与 TOAST 表体积变大，宽列查询和高频更新成本高。

第二种是冷热拆分，把经常查询的业务键提取为独立列，把完整报文放到附表或仅低频访问的 JSONB 字段。这样主表更窄，顺序扫描和索引回表都更友好。

第三种是 JSONB 加索引或生成列。对高频路径使用 GIN 表达式索引、B-tree 表达式索引，或把路径固化为生成列，可减少扫描范围，但索引并不能让 SELECT payload 返回大文档本身变得免费。

选型上，如果只是归档和偶尔按主键查看完整报文，TOAST 很合适；如果经常按 JSON 内字段过滤、排序、聚合，应把关键字段结构化；如果大文档更新频繁但只改其中一个小字段，也要警惕整字段重写带来的写放大。

实操步骤：构造 3 万字符 JSONB 并观察 TOAST

以下示例可直接在 PostgreSQL 12+ 的测试库执行。示例通过 repeat 构造高重复文本，使压缩效果明显；真实 JSON 若包含大量随机字符串，压缩率会低很多。

-- 1. 建表：id 用于点查，payload 存放约 3 万字符 JSONB
DROP TABLE IF EXISTS toast_jsonb_demo;
CREATE TABLE toast_jsonb_demo (
    id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    biz_no  text NOT NULL,
    payload jsonb NOT NULL
);

-- 2. 插入 1000 行。
-- message 约 30000 字符，重复内容便于观察压缩效果。
INSERT INTO toast_jsonb_demo (biz_no, payload)
SELECT
    'BIZ-' || g,
    jsonb_build_object(
        'order_id', g,
        'customer', 'customer_' || (g % 100),
        'type', CASE WHEN g % 2 = 0 THEN 'paid' ELSE 'refund' END,
        'amount', g * 1.25,
        'message', repeat('PostgreSQL TOAST JSONB 大字段存储测试 ', 800),
        'tags', jsonb_build_array('vip', 'online', 'batch')
    )
FROM generate_series(1, 1000) AS g;

-- 3. 确保统计信息和可见性映射最新
VACUUM (ANALYZE) toast_jsonb_demo;

查看主表、TOAST 表以及字段的物理信息：

-- 查看主表对应的 TOAST 表 OID
SELECT
    c.oid AS table_oid,
    c.relname AS table_name,
    c.reltoastrelid AS toast_oid,
    t.relname AS toast_table_name
FROM pg_class AS c
JOIN pg_namespace AS n ON n.oid = c.relnamespace
LEFT JOIN pg_class AS t ON t.oid = c.reltoastrelid
WHERE n.nspname = current_schema()
  AND c.relname = 'toast_jsonb_demo';

-- 查看 payload 字段存储策略
-- xmain：允许压缩，也允许移动到 TOAST 表
SELECT
    a.attname,
    a.attlen,
    a.attstorage,
    pg_catalog.format_type(a.atttypid, a.atttypmod) AS data_type
FROM pg_attribute AS a
JOIN pg_class AS c ON c.oid = a.attrelid
JOIN pg_namespace AS n ON n.oid = c.relnamespace
WHERE n.nspname = current_schema()
  AND c.relname = 'toast_jsonb_demo'
  AND a.attname = 'payload';

-- 观察主表与 TOAST 表大小
SELECT
    pg_size_pretty(pg_relation_size('toast_jsonb_demo')) AS heap_size,
    pg_size_pretty(pg_total_relation_size('toast_jsonb_demo')) AS total_size;

还可以使用 pgstattuple 扩展观察 TOAST 表的实际页面占用：

-- 如测试库未安装扩展，可由超级用户执行：CREATE EXTENSION pgstattuple;
SELECT *
FROM pgstattuple(
    (SELECT c.reltoastrelid::regclass
     FROM pg_class AS c
     JOIN pg_namespace AS n ON n.oid = c.relnamespace
     WHERE n.nspname = current_schema()
       AND c.relname = 'toast_jsonb_demo')
);

查询实测：不取大字段与取回大字段的差异

先比较只查轻量列和查询完整 JSONB 的缓冲代价。测试前建议多执行两次，让数据预热，避免把冷缓存误判成 TOAST 本身的问题。

-- 查询轻量列：不需要解析完整 payload
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, biz_no
FROM toast_jsonb_demo
WHERE id = 100;

-- 查询完整大字段：需要取得 TOAST 指针内容并反压缩/构造 JSONB
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, biz_no, payload
FROM toast_jsonb_demo
WHERE id = 100;

-- 只提取一个小路径：优化器和执行器只需处理目标表达式所需数据
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, payload ->> 'customer' AS customer
FROM toast_jsonb_demo
WHERE id = 100;

在主键点查中，主表定位很快，但 SELECT payload 的耗时通常更高，尤其当 JSONB 已经被切成多个 TOAST chunk 时，会出现额外 heap block 读取。相反，只查 id、biz_no 基本只访问主表页面。

再测试按 JSONB 内字段过滤。全表扫描时，即使最终只返回很少行，数据库也可能要读取大量大字段：

-- 无索引：需要扫描大量 JSONB 才能判断 type
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, biz_no
FROM toast_jsonb_demo
WHERE payload ->> 'type' = 'paid';

-- 为高频过滤路径建立 B-tree 表达式索引
CREATE INDEX IF NOT EXISTS idx_demo_payload_type
ON toast_jsonb_demo ((payload ->> 'type'));

ANALYZE toast_jsonb_demo;

-- 有索引：通过索引定位，再按可见性检查或回表取轻量列
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, biz_no
FROM toast_jsonb_demo
WHERE payload ->> 'type' = 'paid';

如果条件选择性强，例如按 order_id 查一行，B-tree 表达式索引效果更好；如果是 @>、存在性判断、数组键查询，GIN 索引更合适。不要对所有路径盲目建 GIN，因为写入大 JSONB 时，索引维护也会消耗明显。

-- 高选择性路径：B-tree 表达式索引
CREATE INDEX IF NOT EXISTS idx_demo_payload_order_id
ON toast_jsonb_demo (((payload ->> 'order_id')::bigint));

SELECT id, biz_no
FROM toast_jsonb_demo
WHERE (payload ->> 'order_id')::bigint = 123;

-- 包含查询或键存在性：GIN 索引更适合
CREATE INDEX IF NOT EXISTS idx_demo_payload_gin
ON toast_jsonb_demo USING gin (payload jsonb_path_ops);

SELECT id
FROM toast_jsonb_demo
WHERE payload @> '{"type":"paid"}'::jsonb;

更新代价：TOAST 不是原地修改 JSON 节点

JSONB 存储是二进制的，但更新一个路径并不意味着数据库只在 TOAST chunk 中修改几个字节。PostgreSQL 的堆元组采用多版本机制，更新大字段通常会产生新的元组版本；若新值仍需外存，还可能重写相关 TOAST 数据。

-- 观察更新一个小路径后的表膨胀情况
SELECT pg_size_pretty(pg_total_relation_size('toast_jsonb_demo')) AS before_size;

UPDATE toast_jsonb_demo
SET payload = jsonb_set(payload, '{amount}', '999.99')
WHERE id = 100;

VACUUM (ANALYZE) toast_jsonb_demo;

SELECT pg_size_pretty(pg_total_relation_size('toast_jsonb_demo')) AS after_size;

如果 amount 是高频变化字段，不应把它埋在 3 万字符 JSONB 中反复更新。更合理的做法是把金额、状态、更新时间拆成独立列，JSONB 只保存低频变化的上下文。

避坑总结

第一，不要用可压缩的人造数据评估真实容量。 常见现象是测试库中 3 万字符重复文本压缩率极高，TOAST 占用很小；上线后报文里多是 Base64、UUID、哈希或日志行，压缩率骤降。应使用脱敏后的真实样本执行 pg_total_relation_size、pgstattuple 和压测，而不是只看 length(payload::text)。

第二，避免 SELECT * 把大字段带回应用。 很多接口列表页本来只显示标题和状态，却使用 SELECT *，导致 ORM 把完整 JSONB 读出、解压、反序列化并经过网络传输。修正方法是显式列出轻量列，或为列表建立专用投影；确实需要详情时，再按主键查询完整 payload。

第三，表达式索引的表达式必须与查询条件匹配。 常见错误是建立 ((payload ->> 'order_id')::bigint)，查询却写成 payload -> 'order_id' = to_jsonb(123)，类型或操作符不一致会导致索引用不上。可用 EXPLAIN (ANALYZE, BUFFERS) 确认是否出现 Index Scan，并关注实际选择率。

第四，不要把高频更新字段放进超大 JSONB。 现象是只改一个状态，表和 TOAST 总大小却持续增长，更新延迟高，vacuum 压力大。应把状态、金额、计数等热字段拆为普通列，JSONB 承载冷数据或半结构化扩展信息。

第五，监控不能只看主表大小。 只查询 pg_relation_size 会漏掉 TOAST 表和索引。容量规划、备份恢复、autovacuum 调优都应使用 pg_total_relation_size，并同时观察主表、TOAST 表、GIN 索引的膨胀与缓存命中。

TOAST 让 PostgreSQL 能优雅容纳超大 JSONB，但它解决的是“能不能存”的问题，不自动保证“查得快、更新省”。把 3 万字符文档放入数据库前，先区分访问路径：完整详情按主键取，热字段结构化，高频过滤路径建匹配索引，才能真正把 JSONB 的灵活性和 PostgreSQL 的性能同时拿到。

免责声明

本工具与文章内容仅用于学习测试场景，优化建议仅供参考，请勿直接用于生产环境，由此产生的风险自行承担。