PostgreSQL 进阶实践：JSONB、GIN 索引与 VACUUM

前言

把 PG 当 MySQL 用（全是 int + varchar + B-Tree）会浪费它最好的能力；反过来把一切都塞进 JSONB 也会翻车。本文讲清楚"什么该用列、什么该用 jsonb"，以及 PG 最容易被误解的 VACUUM。

一、选型第一原则：结构化用列，半结构化用 jsonb

判断标准：这个字段会被用来过滤、排序或做唯一约束吗？ 会，就给它一列。

二、json 与 jsonb

结论：除了一眼要原样回显的日志字段，一律用 jsonb。

三、GIN 索引：让 jsonb 真正可查

CREATE TABLE products (
  id    bigserial PRIMARY KEY,
  props jsonb NOT NULL
);

-- 包含查询
SELECT * FROM products WHERE props @> '{"color":"red","size":"L"}';

GIN 有两种操作符类，选错体积会差两三倍：

-- 默认 jsonb_ops：支持 @>、?、?&、?|，索引更大
CREATE INDEX idx_props ON products USING GIN (props);

-- jsonb_path_ops：只支持 @>，索引小 2~3 倍、查询更快
CREATE INDEX idx_props_path ON products USING GIN (props jsonb_path_ops);

只做包含查询（绝大多数埋点、标签、属性过滤场景）时，jsonb_path_ops 是更优选择。

取单个字段做过滤，用表达式索引，注意括号不能省：

CREATE INDEX idx_order_status ON orders ((data->>'status'));

SELECT * FROM orders WHERE data->>'status' = 'paid';  -- 能命中

-- 需要数值比较时做类型转换（表达式必须 IMMUTABLE）
CREATE INDEX idx_order_amount ON orders (((data->>'amount')::numeric));

四、索引家族怎么选

BRIN 值得单独一提：它只记录每个数据块的 min/max，索引体积与表大小几乎无关。前提是列值与物理存储顺序高度相关（自增时间戳天然满足），否则退化成全表扫描。对千万级日志表，BRIN 往往比 B-Tree 划算得多。

五、MVCC 与 VACUUM：PG 最容易被误解的部分

MySQL 把旧版本放在 undo log 里；PG 则直接在表里写新元组，旧元组留在原地变成 dead tuple，靠 xmin/xmax 判断可见性。

后果很直接：

• 更新频繁的表会膨胀（表和索引都在膨胀），扫描要读更多数据页；
• 死元组必须由 VACUUM 回收，否则空间永远不释放给新数据（只复用，不归还操作系统，除非 VACUUM FULL / pg_repack）。

-- 先看谁在膨胀
SELECT relname, n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

VACUUM (VERBOSE, ANALYZE) orders;

调优要点：

1. 大表必须单独调 autovacuum：默认 autovacuum_vacuum_scale_factor = 0.2，一亿行的表要死两千万行才触发，基本等于不触发；按表降到 0.01~0.05；
2. 长事务是死元组的保护伞：最老事务之前的死元组都无法清理，监控 pg_stat_activity 里 xact_start 超过 30 分钟的会话；
3. VACUUM FULL 会锁表，生产上用 pg_repack 在线重建；
4. 膨胀已发生但没时间处理时，pg_stat_user_tables 的 n_dead_tup / n_live_tup 就是告警指标。

六、执行计划与诊断

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE user_id = 1001 ORDER BY created_at DESC LIMIT 20;

• BUFFERS 必开：shared hit 表示命中缓存，read 才是真读盘，调优看这个；
• rows 估算与实际差几个数量级 → 统计信息过期，跑 ANALYZE；
• Seq Scan 不一定是坏计划：小表或高选择率时顺序扫描本来就更快；
• 找总耗时最高的语句靠 pg_stat_statements（需 shared_preload_libraries = 'pg_stat_statements'）。

连接数也要注意：PG 每个连接一个进程，几百连接就会上下文切换爆炸。生产必备连接池（pgbouncer，transaction 模式最常用）。

七、分区表：删数据用 DROP，不用 DELETE

CREATE TABLE events (
  id bigserial, user_id bigint, created_at timestamptz
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2026_11 PARTITION OF events
  FOR VALUES FROM ('2026-11-01') TO ('2026-12-01');

• 分区裁剪只在查询条件包含分区键时生效，否则扫全部分区；
• 分区数控制在几百个以内，每个分区都要维护自己的统计信息；
• 清理历史数据用 DROP PARTITION，毫秒级完成；DELETE FROM events WHERE created_at < ... 会留下海量死元组，反手就是一次膨胀事故。

总结

PG 的正确姿势可以浓缩成一张对照表：

结构化字段 → 普通列 + B-Tree；半结构化 → jsonb + GIN（jsonb_path_ops）；时序大表 → BRIN；区间/几何 → GiST。

然后盯住两件事：死元组比例和长事务。前者决定表会不会膨胀，后者决定膨胀能不能被清理。把这两项纳入监控，PG 用起来会比想象中稳。