PostgreSQL jsonb + GIN 索引：把日志 / 配置 / 半结构化数据放进 SQL

jsonb 是 PostgreSQL 9.4+ 的内部二进制 JSON 类型。比 json（纯文本存）
快得多，且可以建 GIN 索引做高速字段查询。

典型场景：审计日志、用户配置、设备上报数据、事件流。

建表

CREATE TABLE events (
  id BIGSERIAL PRIMARY KEY,
  type TEXT NOT NULL,
  occurred_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  payload JSONB NOT NULL
);

插入

INSERT INTO events (type, payload) VALUES
  ('login',  '{"user_id": 42, "ip": "1.2.3.4", "ua": "Mozilla/5.0..."}'),
  ('purchase', '{"user_id": 42, "amount": 99.5, "items": ["a", "b"]}');

常用查询操作符

-- 取字段
SELECT payload->>'user_id' AS uid FROM events;     -- 文本
SELECT (payload->>'amount')::numeric FROM events;  -- 转型
SELECT payload->'items'->0 FROM events;            -- 数组取第 0 个

-- 包含查询（最常用）
SELECT * FROM events WHERE payload @> '{"user_id": 42}';

-- 键存在
SELECT * FROM events WHERE payload ? 'amount';
SELECT * FROM events WHERE payload ?& array['user_id', 'ip'];  -- 全部存在
SELECT * FROM events WHERE payload ?| array['user_id', 'guest_id'];  -- 任一存在

GIN 索引：让上面查询变快

-- 全字段 GIN：所有 key/value 路径都索引（大）
CREATE INDEX events_payload_gin ON events USING GIN (payload);

-- jsonb_path_ops GIN：只索引 @>，体积小 30-50%
CREATE INDEX events_payload_pgin ON events USING GIN (payload jsonb_path_ops);

-- 部分索引：只有特定 type 的事件才索引
CREATE INDEX events_login_payload ON events USING GIN (payload)
  WHERE type = 'login';

jsonb_path_ops 是大多数 @> 查询的最优选择，体积小但只支持 @>。

函数式索引（针对单字段）

如果你只查 payload 里某一个字段：

-- 在 (payload->>'user_id') 上建普通 btree
CREATE INDEX events_user_id
  ON events (((payload->>'user_id')::bigint));

-- 查询
EXPLAIN ANALYZE
SELECT * FROM events
WHERE (payload->>'user_id')::bigint = 42;

比 GIN 还快，但只能用于这一种查询。

更新

-- 替换整个 payload
UPDATE events SET payload = '{"...":...}' WHERE id = 1;

-- 修改单字段
UPDATE events SET payload = payload || '{"status": "done"}' WHERE id = 1;

-- 删字段
UPDATE events SET payload = payload - 'status' WHERE id = 1;

-- 嵌套路径设置
UPDATE events
  SET payload = jsonb_set(payload, '{user, name}', '"alice"', true)
  WHERE id = 1;

聚合 / 展开

-- 取所有不同的 type 值
SELECT DISTINCT payload->>'level' FROM events;

-- 展开 JSON 数组为多行
SELECT id, jsonb_array_elements_text(payload->'items') AS item FROM events;

-- 按 JSON 字段聚合
SELECT payload->>'country' AS country, count(*)
FROM events
GROUP BY payload->>'country'
ORDER BY count(*) DESC;

校验 / Schema 约束（jsonb_schema）

PG 17 之前没原生 JSON Schema 校验。可以用 CHECK 约束做基础校验：

ALTER TABLE events
  ADD CONSTRAINT payload_has_user_id
  CHECK (payload ? 'user_id' AND jsonb_typeof(payload->'user_id') = 'number');

更复杂的校验建议在应用层做（Pydantic / JSON Schema 库）。

性能边界

行级 payload 字段 > 8KB 会被 TOAST（外部存储），读取需要解压
单值超过 1MB 性能急剧下降
经常修改的字段不要塞 jsonb：每次 UPDATE 整列重写
数组 push 没有"in-place"，每次 append 都是整列重写

如果数据本质就是结构化的，建关系表，别用 jsonb 偷懒。

EXPLAIN 验证索引被用上

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM events WHERE payload @> '{"user_id": 42}';

-- Bitmap Index Scan on events_payload_pgin
--   Recheck Cond: (payload @> '{"user_id": 42}')
--   ...

Seq Scan 出现说明索引没生效 —— 检查统计信息是否够新（ANALYZE events）
或者索引类型是否匹配查询。

Django ORM 用法

from django.db import models
from django.contrib.postgres.indexes import GinIndex
from django.contrib.postgres.fields import JSONField  # PG-specific, 旧
# 现代 Django 用通用的 models.JSONField，PG 后端自动 jsonb

class Event(models.Model):
    type = models.CharField(max_length=50)
    occurred_at = models.DateTimeField(auto_now_add=True)
    payload = models.JSONField()

    class Meta:
        indexes = [
            GinIndex(fields=['payload'], opclasses=['jsonb_path_ops'],
                     name='events_payload_pgin'),
        ]

# 查询：
Event.objects.filter(payload__contains={'user_id': 42})
Event.objects.filter(payload__user_id=42)  # 自动 ->>
Event.objects.filter(payload__has_key='ip')

踩过的坑

字段路径里 -> 是 jsonb，->> 是 text。->> 后才能比较字符串；
(payload->>'amount')::numeric 才能数值比较。
GIN 索引建索引非常慢且占用空间大（可能比表本身还大）。生产建议
CREATE INDEX CONCURRENTLY 在线建，不锁表。
jsonb_path_ops 不支持 ?、?|、?& 操作符；如果要用这几个，
必须用普通 GIN。
在 PG 16 之前 jsonb_set 修改不存在的路径 + create_missing=true
时行为有 quirk；升 PG 16+ 之后行为更一致。