PostgreSQL：把版本清理、JSONB 与索引选对

PostgreSQL 的灵活性很容易让人一次加入许多东西：一列 JSONB、一棵 GIN 索引、几个扩展，再把维护交给默认设置。真正需要理解的是，每个选择改变了什么：旧行版本由谁清理，查询用了哪类运算，索引能排除哪些候选，扩展增加了哪些部署责任。

本文以 PostgreSQL 18 为文档基线，实际运行版本为 18.6、Linux/amd64。实验使用官方单用户离线模式，验证 SQL、行位置变化、JSONB 和 GIN/GiST/BRIN 执行计划；没有真实多会话，也不以它证明 autovacuum 调度、并发锁或生产性能。全部复现输入放在文末。

更新一行，为什么可能留下两个版本

普通 PostgreSQL 表把数据放在堆页里，索引另存。MVCC 让每条查询依据快照判断哪个行版本可见：别的事务提交新版本时，旧读者可能仍需要旧版本；当前事务也能看到自己的先前写入。版本不能一更新就立即抹掉。^[1]^[2]

本次建一行 id=1,value='old'，更新成 new，前后观察：

sql

SELECT id, value, ctid FROM versions;
UPDATE versions SET value = 'new' WHERE id = 1;
SELECT id, value, ctid FROM versions;

text

1|old|(0,1)
1|new|(0,2)

ctid 表示一个行版本的物理位置。本例发生了位置变化，符合“UPDATE 创建新元组版本”的机制；这些坐标只属于这份小数据，不保证每次都是同样数值。应用不能把 ctid 当稳定业务主键，因为更新和重写表都可能改变它。xmin、xmax 等系统信息参与版本判断，xmax 也有锁和其他状态含义，不能仅看它非零就断言行已经删除。^[3]

某些更新可以走 HOT：当条件满足，例如没有更新非摘要型索引所引用的列（包括 INCLUDE 列、索引表达式或部分索引谓词引用的列），并且原页有空间时，版本链可减少新索引条目的维护。PostgreSQL 18 的条件允许摘要型索引例外，核心中 BRIN 属于此类；对应摘要仍可能需要更新。HOT 不是“原地改旧行”，也不能保证每次更新都用上。判断开销时应考虑索引、页剩余空间和实际统计。^[4]

正在绘制图示…

查看图源代码

flowchart LR
    I["索引访问"] --> H["堆页中的候选版本"]
    H --> V["用快照与事务状态判断可见性"]
    V --> O["旧快照可能需要旧版本"]
    V --> N["新快照可看到已提交的新版本"]
    O --> C["没有读取者再需要它"]
    C --> P["清理失效版本<br/>空间留给后续复用"]

原始尺寸下可横向或纵向滚动；按 Escape 关闭。

正在绘制图示…

查看图源代码

flowchart LR
    I["索引访问"] --> H["堆页中的候选版本"]
    H --> V["用快照与事务状态判断可见性"]
    V --> O["旧快照可能需要旧版本"]
    V --> N["新快照可看到已提交的新版本"]
    O --> C["没有读取者再需要它"]
    C --> P["清理失效版本<br/>空间留给后续复用"]

版本清理的安全性依赖还活着的读取边界。长事务、长期闲置在事务内的连接，以及某些复制保留机制，都可能让可清理范围落后。看到表变大时，先找长期保留的快照和维护进度，而不是直接跑一次 VACUUM FULL。^[5]^[6]

VACUUM、ANALYZE、VACUUM FULL 是三件不同的事

动作	主要解决什么	重要代价和边界
普通 `VACUUM`	回收不再需要的行版本所占空间供复用，维护可见性信息与冻结等	会产生 I/O；一般不把中间空洞立刻还给操作系统；尾部完全空页在条件允许时可能截断
`ANALYZE`	采样数据，更新优化器用的统计信息	不清理所有旧版本；统计是估计，不是真实行数保证
`VACUUM (ANALYZE)`	清理与更新统计一起做	仍需为读取、I/O 和维护窗口考虑成本
`VACUUM FULL`	重写并压紧整张表以归还更多空间	需要额外磁盘空间与 ACCESS EXCLUSIVE 锁，会阻塞其他使用
autovacuum	根据阈值等条件自动安排 vacuum/analyze	不是永远足够快，也不会自动替你执行 VACUUM FULL

VACUUM 后文件大小没有立即下降，不代表它没做事。空闲空间可能留在表内，下一批 INSERT/UPDATE 可以复用；一个持续写入的表，追求稳定占用往往比不停压到最小更合理。VACUUM FULL 适合明确需要重写且有窗口的情形，不适合作为每天例行“加速按钮”。^[5:1]^[7]^[8]

可见性图 visibility map 标记某些堆页的 all-visible、all-frozen 状态。Index Only Scan 如果能从这份图确认候选所在页对所有相关事务可见，就可减少回堆检查。因此 vacuum 不只影响磁盘垃圾，也可能影响只读查询到底要不要访问堆。写入会改变相关可见性状态，覆盖索引本身不能保证零 Heap Fetches。^[9]^[10]

事务 ID 还引出一项必须长期做的维护：冻结老版本，防止有限宽度事务 ID 循环使用时把很老的数据误判为未来。PostgreSQL 有抗回卷 vacuum 及保护措施；不能因为系统不忙、业务没删除数据就关闭维护。监控数据库 age(datfrozenxid)、表的 dead tuple 估计、上次 autovacuum 和长期事务，是有用的起点。不要等接近保护阈值才发现维护积压。^[5:2]

这里的 n_dead_tup 是估计值；“很多旧版本”也不等于它们现在全可删。文末 monitor.sql 适用于正常服务实例的观察，本文未拿离线模式的后台统计当作 autovacuum 实测。

JSONB 保存了结构，但不会替你决定数据模型

json 保存输入文本的表示，jsonb 转为便于处理的二进制结构，支持更丰富的索引。JSONB 不保留原来的空白、对象键顺序或重复键；同名键出现多次时保留最后一个。需要保留签名原文、原始排版或逐字归档时，不能指望 JSONB 还能还原原始字节。^[11]

sql

SELECT '{"b":2,"a":1,"a":3}'::jsonb;

实际输出：

text

{"a": 3, "b": 2}

输出中的键次序来自该次序列化，不应成为程序协议。JSONB 的主要价值在于按结构读取和匹配：

要做的事	常见操作	需要区分什么
保留 JSON 类型取字段	`attrs -> 'name'`	得到 JSONB 值，不一定是文本
取文本	`attrs ->> 'name'`	文本若要参与数值比较，要合理校验并转换
取嵌套路径	`#>`、`#>>`	结构不存在与 JSON null 的区别
判断包含某结构	`@>`	是结构包含，不是任意字符串子串搜索
判断顶层键/数组字符串元素	`?`、`?\|`、`?&`	`?` 不会递归查所有深层键
JSON path 查询	`@?`、`@@` 等	表达式语义与可用索引操作符类

SQL NULL 与 JSON 的 null 是不同层级。下面三个结果依次为 JSON null、本值是否 SQL NULL、缺字段是否 SQL NULL：

sql

SELECT '{"x":null}'::jsonb -> 'x',
       ('{"x":null}'::jsonb -> 'x') IS NULL,
       ('{}'::jsonb -> 'x') IS NULL;

text

null|f|t

显式 JSON null 仍是一个 JSONB 值，所以第二列为 false；字段不存在时提取结果为 SQL NULL。若用 ->> 取文本，这些情况又可能合并成 SQL NULL，业务必须决定需不需要保留区分。^[12]

将可扩展的商品属性放 JSONB 很自然，但订单金额、外键、状态等核心约束不该因为“JSON 灵活”就失去明确类型和约束。可以把稳定核心字段放普通列，把变化较多的附加属性放 JSONB，再按实际过滤条件增加索引。大文档上的修改仍然是行更新，可能需要锁整行并产生版本与存储开销；拆得太少也会形成争用热点。^[11:1]

先看运算，再挑索引名字

PostgreSQL 的索引类型是不同的访问方法；操作符类 operator class 告诉访问方法，某个类型的运算应该怎样映射到索引。写了 USING gin 并不表示任何 JSON 查询都能走它。

访问方法	怎样减少候选	典型用途	主要取舍
B-tree	沿有序键定位范围	等值、范围、排序；提取出的 JSON 标量	对全文包含、空间重叠等不总合适
GIN	把一份复合值拆成多个键，保存键到记录位置的关联	JSONB 包含、数组成员、全文词项	多键维护导致写放大；通常不能直接还原整个值以做 Index Only Scan
GiST	可扩展的树框架，以运算相关的摘要缩小候选	范围重叠、几何关系、某些最近邻	效率、是否有损和可用排序取决于操作符类
BRIN	按连续物理页区间保存摘要，排除不可能匹配的区间	与物理位置相关的时间、递增序列大表	索引小，但候选页仍需重检；数据无相关性时可能筛不掉多少

这些不是“好、较好、最好”的排名。要查哪些运算、更新多频繁、列与物理顺序多相关，才决定合适的索引。^[13]^[14]^[15]^[16]

GIN：从“某个属性值”找到包含它的文档

本次造了 10,000 份文档，每份含不同的 rare 值，建立 jsonb_path_ops GIN：

sql

CREATE INDEX docs_gin ON docs USING gin(attrs jsonb_path_ops);
SELECT id FROM docs WHERE attrs @> '{"rare":7777}';

实际返回 7777，执行计划包含：

text

Bitmap Index Scan on docs_gin ... (actual rows=1.00 loops=1)
  Index Cond: (attrs @> '{"rare": 7777}'::jsonb)

计划外层还有 Bitmap Heap Scan 与重检条件，不能把命中 GIN 理解为直接从索引返回完整文档。

JSONB GIN 操作符类	支持的主要查询	如何取舍
默认 `jsonb_ops`	`?`、`?\|`、`?&`，以及 `@>`、`@?`、`@@` 等	查询形态更广；索引可能更大
`jsonb_path_ops`	`@>`、`@?`、`@@`，不支持键存在运算符那一组	对适合的路径/值包含查询通常更紧凑、更有选择性；不适合只查某些空结构等场景

如果业务主要按 attrs->>'status' = 'open' 过滤，可以考虑对应表达式的 B-tree，而不是指望任意整列 GIN 都匹配这个表达式。GIN 的 fastupdate/pending list 等机制可以推迟部分维护工作，但最终仍要清理处理，写入延迟和维护成本需要一起观察。^[17]^[18]

GiST：把“范围相交”交给理解范围的索引

本例每条范围是 [g,g+10)，查与 [500,501) 相交的记录：

sql

CREATE INDEX ranges_gist ON ranges USING gist(during);
SELECT COUNT(*) FROM ranges
WHERE during && int4range(500,501,'[)');

实际输出 10，对应起点 491 到 500。实际计划使用 ranges_gist 的 Bitmap Index Scan。&& 表示范围重叠，GiST 与内置 range 操作符类能利用这类关系定位候选。若改成两个普通端点列，也能写出逻辑等价条件，但索引规划能力不必完全相同。^[19]

范围类型还能结合排他约束表达“同一资源的预约时间不得重叠”，但约束涉及列类型、操作符类和可能的扩展支持，需要单独设计，不能仅在应用里先查一次再插入。

BRIN：先排除整片物理区域，再检查区域内的行

实验按 id 递增插入 100,000 行，建立每 16 页一个范围的 BRIN。查询 id BETWEEN 99000 AND 99010，实际返回 11 行。计划关键部分：

text

Bitmap Heap Scan on events ... (actual rows=11.00 loops=1)
  Rows Removed by Index Recheck: 1653
  Heap Blocks: lossy=32
  -> Bitmap Index Scan on events_brin

BRIN 帮忙选出了 32 个候选堆页，页里还有大量不匹配行，重检后去掉 1,653 行。lossy 在这里表示索引/位图只精确到页区域等较粗粒度，不表示查询结果错误。它用更小的索引换取更多候选重检。若时间列与插入顺序高度相关，很多无关区间可以整体跳过；如果数据随机散落，摘要范围彼此重叠，效果会差很多。^[16:1]

pages_per_range 越小通常摘要更细，但索引也更大。新页区间还需要 summary，autosummarize 等维护设置影响何时补齐；不能只建完一次就假定后续追加数据永久具有同样摘要覆盖。本文只验证静态测试表，不测长期维护效果。

扩展生态：能力很强，也意味着多一份依赖

扩展把函数、类型、操作符、索引支持等对象作为一个可安装和升级的单元。服务器必须先有相应扩展文件，再在指定数据库执行 CREATE EXTENSION。某些扩展需要超级用户，有些被标记为 trusted，在满足相应数据库权限时可由普通角色安装；这是能力与安全边界，不是一个可以随意运行外部代码的免审入口。^[20]

扩展	值得了解的实际用途	采用前检查
`pg_stat_statements`	按归一化语句累计规划/执行统计，帮助找到总耗时高的 SQL	需要相关预加载与配置；观察的是累计统计，重启/重置和采样窗口要明确
`pg_trgm`	将文本拆成三字符片段，支持相似度及合适的 LIKE/正则查询索引	短查询、语言与分词需求可能不同；相似度不是语义理解
`postgres_fdw`	在一个 PostgreSQL 中访问另一 PostgreSQL 的外部表，部分运算可下推	网络开销、远端计划、权限、事务一致性边界；不能当作免费的本地表
PostGIS	为地理/几何数据提供类型、关系运算及空间索引能力	坐标系、单位、geometry/geography 区别和版本兼容
`pgvector`	在 PostgreSQL 中保存向量并做精确或近似近邻查询	向量维度、距离度量、索引维护、过滤后召回率；近似查询需要质量验证

前三项属于 PostgreSQL 官方随附扩展文档；后两项来自各项目官方资料，未在本次环境安装或运行。pgvector 的说明固定参照 v0.8.0 README，不声称这是当前最新版。扩展能让一套数据库承接更多查询，但也把升级、备份恢复兼容和资源争用带进同一系统。^[21]^[22]^[23]^[24]^[25]

选扩展时先问：这是需要在事务库旁边直接完成的功能，还是会抢占在线事务资源的大型专用负载？已有 SQL、权限和备份能力能复用是优点，独立扩容、不同故障域和专门运维也是成本维度。没有真实负载数据时，不能仅凭“支持插件”下容量结论。

这组实验能说明什么

实际看到了 UPDATE 后 ctid 变化、JSONB 重复键合并及 null 区别、GIN 包含检索、GiST 范围重叠、BRIN 的有损候选重检。执行计划未强制关闭顺序扫描，保留了优化器自行选择。临时表、人工分布、单用户模式只适合观察机制，不适合用单次耗时推导生产吞吐。

没有测试 autovacuum 后台触发、长事务积压、事务 ID 回卷、扩展安装/升级或并发访问。这些部分严格以官方资料解释。真正上线前，应在接近实际的持久表、更新比例、索引组合与维护配置下补测。

参考资料

完整复现材料

需要 PostgreSQL 18.6 的 postgres/initdb 和 Python 3。只对新建可丢弃数据目录执行，不能与其他进程同时使用同一目录。单用户模式限制见官方 postgres 说明。

export POSTGRES=/path/to/postgresql-18.6/bin/postgres
export PGDATA="$(mktemp -d)/data"
/path/to/postgresql-18.6/bin/initdb -D "$PGDATA" -A trust --no-locale -E UTF8
python3 run_single.py postgresql.sql

monitor.sql 是正常服务实例的只读检查材料，本次未运行。运行它需要正常连接和相应统计可见权限，按自己的连接设置使用 psql -X -f monitor.sql。不要从不可见的统计行推断“没有长事务”。

postgresql.sql

代码块 · 41 行 · 点击收起 / 展开

sql

\set ON_ERROR_STOP on
CREATE TEMP TABLE versions(id integer PRIMARY KEY, value text);
INSERT INTO versions VALUES(1,'old');
\echo version-before
SELECT id,value,ctid FROM versions;
UPDATE versions SET value='new' WHERE id=1;
\echo version-after
SELECT id,value,ctid FROM versions;
VACUUM (ANALYZE) versions;
\echo jsonb-shape
SELECT '{"b":2,"a":1,"a":3}'::jsonb;
SELECT '{"x":null}'::jsonb->'x' AS json_null,
       ('{"x":null}'::jsonb->'x') IS NULL AS json_is_sql_null,
       ('{}'::jsonb->'x') IS NULL AS missing_is_sql_null;
SELECT '{"tags":["go","sql"]}'::jsonb @> '{"tags":["go"]}'::jsonb AS contains,
       '{"tags":["go","sql"]}'::jsonb ? 'tags' AS top_key;
CREATE TEMP TABLE docs(id integer PRIMARY KEY, attrs jsonb NOT NULL);
INSERT INTO docs SELECT g,jsonb_build_object('team',g%100,'rare',g,'status','open')
FROM generate_series(1,10000) g;
CREATE INDEX docs_gin ON docs USING gin(attrs jsonb_path_ops);
ANALYZE docs;
\echo gin
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT id FROM docs WHERE attrs @> '{"rare":7777}';
SELECT id FROM docs WHERE attrs @> '{"rare":7777}';
CREATE TEMP TABLE ranges(id integer PRIMARY KEY,during int4range NOT NULL);
INSERT INTO ranges SELECT g,int4range(g,g+10,'[)') FROM generate_series(1,10000) g;
CREATE INDEX ranges_gist ON ranges USING gist(during);
ANALYZE ranges;
\echo gist
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT id FROM ranges WHERE during && int4range(500,501,'[)');
SELECT COUNT(*) FROM ranges WHERE during && int4range(500,501,'[)');
CREATE TEMP TABLE events(id integer NOT NULL,payload text NOT NULL);
INSERT INTO events SELECT g,repeat('x',120) FROM generate_series(1,100000) g;
CREATE INDEX events_brin ON events USING brin(id) WITH(pages_per_range=16);
ANALYZE events;
\echo brin
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT id FROM events WHERE id BETWEEN 99000 AND 99010;
SELECT COUNT(*) FROM events WHERE id BETWEEN 99000 AND 99010;

run_single.py

代码块 · 42 行 · 点击收起 / 展开

python

"""Run the deliberately simple fixtures shipped with these articles.
Input: ordinary SQL and optional psql \\set/\\pset/\\echo directives.
This is a fixture adapter, not a general SQL parser. No fixture may contain
semicolons inside quoted values, comments, function bodies, or identifiers.
"""
import os
from pathlib import Path
import re
import subprocess
import sys

sql = Path(sys.argv[1]).read_text()
lines = []
for line in sql.splitlines():
    if line.startswith('\\echo '):
        label = line[len('\\echo '):].replace("'", "''")
        lines.append("SELECT '" + label + "';")
    elif line.startswith('\\'):
        continue
    else:
        lines.append(line)
commands = []
for part in '\n'.join(lines).split(';'):
    part = part.strip()
    if not part:
        continue
    # COPY gives legible values; it does not change the SELECT's semantics.
    if re.match(r'(?i)^(SELECT|WITH)\b', part):
        part = "COPY (" + part + ") TO STDOUT WITH (DELIMITER '|', NULL 'NULL')"
    commands.append(part + ';\n\n')
run = subprocess.run(
    [os.environ.get('POSTGRES', 'postgres'), '--single', '-j',
     '-D', os.environ['PGDATA'], 'postgres'],
    input=''.join(commands), text=True, capture_output=True)
print(run.stdout, end='')
print(run.stderr, end='', file=sys.stderr)
if run.returncode:
    sys.exit(run.returncode)
# The stand-alone backend may recover from a SQL error and exit zero.
# An ERROR is still a failed fixture; expected failures are checked outside.
if re.search(r'\b(ERROR|FATAL|PANIC):', run.stderr):
    sys.exit(1)

monitor.sql

sql

-- PostgreSQL 18 normal server inspection; not measured in single-user lab.
SELECT relname,n_live_tup,n_dead_tup,last_autovacuum,last_autoanalyze
FROM pg_stat_user_tables ORDER BY n_dead_tup DESC;
SELECT datname,age(datfrozenxid) AS xid_age FROM pg_database ORDER BY xid_age DESC;
SELECT pid,state,xact_start,backend_xmin,wait_event_type,wait_event
FROM pg_stat_activity WHERE xact_start IS NOT NULL ORDER BY xact_start;
SELECT name,default_version,installed_version FROM pg_available_extensions
WHERE name IN ('pg_trgm','pg_stat_statements','postgres_fdw','postgis','vector')
ORDER BY name;

PostgreSQL：把版本清理、JSONB 与索引选对 ​

更新一行，为什么可能留下两个版本 ​

VACUUM、ANALYZE、VACUUM FULL 是三件不同的事 ​

JSONB 保存了结构，但不会替你决定数据模型 ​

先看运算，再挑索引名字 ​

GIN：从“某个属性值”找到包含它的文档 ​

GiST：把“范围相交”交给理解范围的索引 ​

BRIN：先排除整片物理区域，再检查区域内的行 ​

扩展生态：能力很强，也意味着多一份依赖 ​

这组实验能说明什么 ​

参考资料 ​

完整复现材料 ​

postgresql.sql ​

run_single.py ​

monitor.sql ​

PostgreSQL：把版本清理、JSONB 与索引选对

更新一行，为什么可能留下两个版本

VACUUM、ANALYZE、VACUUM FULL 是三件不同的事

JSONB 保存了结构，但不会替你决定数据模型

先看运算，再挑索引名字

GIN：从“某个属性值”找到包含它的文档

GiST：把“范围相交”交给理解范围的索引

BRIN：先排除整片物理区域，再检查区域内的行

扩展生态：能力很强，也意味着多一份依赖

这组实验能说明什么

参考资料

完整复现材料

postgresql.sql

run_single.py

monitor.sql