PostgreSQL 的 JSONB 和 GIN 索引：什么时候值得用

MySQL 5.7 也有 JSON 类型，但 PostgreSQL 的 JSONB 更成熟：支持索引、有丰富的操作符、能部分更新。这篇记一下实际用法和边界。

JSON vs JSONB

json 保存原始文本，保留空格和键顺序；jsonb 保存解析后的二进制，写入稍慢但查询快得多，还能建索引。

除非要保留原始格式做审计，一律用 jsonb。

建表和写入

CREATE TABLE events (
    id          bigserial PRIMARY KEY,
    kind        text NOT NULL,
    payload     jsonb NOT NULL,
    created_at  timestamptz DEFAULT now()
);

INSERT INTO events (kind, payload) VALUES
('order', '{"order_id": 1001, "amount": 299, "tags": ["vip", "promo"],
             "user": {"id": 7, "city": "杭州"}}');

常用操作符

-- 取一层字段（-> 返回 jsonb，->> 返回 text）
SELECT payload->>'amount' FROM events;

-- 嵌套字段
SELECT payload->'user'->>'city' FROM events;

-- 包含判断：payload 里是否含有这组键值
SELECT * FROM events WHERE payload @> '{"tags": ["vip"]}';

-- 键是否存在
SELECT * FROM events WHERE payload ? 'coupon';

-- 按数值过滤（注意要转类型）
SELECT * FROM events WHERE (payload->>'amount')::numeric > 200;

@> 是最有用的一个，可以匹配嵌套结构，配合 GIN 索引效率很高。

索引

-- 整体包含查询
CREATE INDEX idx_events_payload ON events USING gin (payload);

-- 只索引某个字段（更小更快）
CREATE INDEX idx_events_city ON events USING gin ((payload->'user'->'city'));

-- 等值查询用 btree 就够
CREATE INDEX idx_events_order ON events ((payload->>'order_id'));

经验：查询条件固定、字段明确，用 btree 表达式索引，体积小、写入快；要支持任意键的包含查询，才用 GIN。GIN 索引写入放大明显，高频写入的表要谨慎。

部分更新

UPDATE events
SET payload = jsonb_set(payload, '{user,city}', '"上海"')
WHERE id = 1;

jsonb_set 只改指定路径。注意原值的类型要写对：字符串要带引号写成 '"上海"'，数字直接 '30'。

什么时候不该用

• 字段结构固定、每个字段都要查询和排序 → 老老实实建列，JSONB 会让查询变复杂、索引效率变低
• 需要外键约束 → JSONB 里做不了
• 数据量大且要做聚合分析 → 考虑宽表或列存

我的使用边界

用 JSONB 存“结构不固定、整体读写、偶尔按几个键查”的数据：事件日志的附加属性、第三方回调的原始报文、用户的个性化配置。

核心业务字段一律建列。JSONB 是灵活性的补充，不是 schema 设计的替代品——把所有东西塞进一个 JSON 字段，最后会既查不动也改不动。