第4章 关系型存储（PostgreSQL）：JSONB字段、全文检索、分区表

前面咱们聊了文件型存储和键值存储，说实话，生产环境里真正扛大头的还是关系型数据库。我这些年经手的元数据项目，八成以上最后都落在了 PostgreSQL 上。为什么？因为你既要结构化的查询，又要灵活的 schema，还想要搜索能力——PG 刚好把这几样都捏在一起了。

这一章我们只聚焦三个核心特性：JSONB、全文检索、分区表。这三个东西拼起来，基本能覆盖 90% 的元数据存储检索场景。嗯，咱们直接上手。

4.1 JSONB：刚性表里的柔性字段

元数据的痛点在于字段不确定。今天要存“拍摄设备型号”，明天可能就多了“白平衡参数”。传统关系表加字段要改 DDL、做迁移，麻烦得很。

JSONB 就是 PostgreSQL 给的解决方案。它把 JSON 存成二进制格式，支持索引，能走查询优化。我在项目中见过有人把系统配置全塞进一个 JSONB 字段里，结构随时变，查询直接走 GIN 索引，性能居然还不错。

先看怎么用：

-- 创建一张元数据表
CREATE TABLE metadata (
    id          BIGSERIAL PRIMARY KEY,
    object_id   UUID NOT NULL,
    attributes  JSONB NOT NULL DEFAULT '{}'::jsonb,
    created_at  TIMESTAMPTZ DEFAULT now()
);

-- 插入带嵌套的元数据
INSERT INTO metadata (object_id, attributes)
VALUES (
    'a1b2c3d4-...',
    '{
        "camera": "Sony A7R4",
        "lens": "FE 24-70mm",
        "settings": {
            "iso": 800,
            "aperture": 2.8,
            "shutter": "1/250"
        }
    }'::jsonb
);

这里有个坑。我刚开始用 JSONB 的时候，以为它跟字符串存 JSON 没区别。直到压测发现慢了十倍，才意识到没建索引。

避坑指南：JSONB 字段如果不建索引，查询就是全表扫描。你猜怎么着？等数据到了百万级再来建索引，锁表时间能让你哭。建议表建好就规划好索引，至少把常用的查询路径覆盖掉。

常用索引方式有两种：

-- 方式一：GIN 通用索引（适合各种包含、存在查询）
CREATE INDEX idx_metadata_attributes_gin
ON metadata USING GIN (attributes);

-- 方式二：BTREE 表达式索引（针对特定字段）
CREATE INDEX idx_metadata_camera
ON metadata ((attributes ->> 'camera'));

查询举例：

-- 找所有用 Sony 相机的元数据
SELECT * FROM metadata
WHERE attributes @> '{"camera": "Sony A7R4"}'::jsonb;

-- 找 ISO 大于等于 1600 的
SELECT * FROM metadata
WHERE (attributes -> 'settings' ->> 'iso')::int >= 1600;

我个人习惯把经常查询的结构化字段单独拎出来当普通列，剩下的变化部分用 JSONB。这样既保住了强类型的查询性能，又留了扩展空间。

4.2 全文检索：给元数据装上搜索引擎

元数据里经常有描述性文本，比如“这张照片是在黄山山顶拍的，雾气很大”。你要搜“黄山 雾气”，普通 LIKE 查询走不了索引，而且不能做分词、词根、同义词。PostgreSQL 内置了 tsvector / tsquery，性能比 Like 好一个数量级。

看一个典型的场景：

-- 增加一个文本描述列
ALTER TABLE metadata ADD COLUMN description TEXT;

-- 生成 tsvector 向量
UPDATE metadata
SET tsvec = to_tsvector('simple', description);

-- 注意：实际中最好用触发器或者生成列自动维护
-- 这里为演示手动更新

创建全文检索索引：

-- GIN 索引 + tsvector
CREATE INDEX idx_metadata_tsvec
ON metadata USING GIN (tsvec);

查询：

-- 搜索包含 "clouds" 和 "sunset" 的元数据
SELECT object_id, description
FROM metadata
WHERE tsvec @@ to_tsquery('simple', 'clouds & sunset');

-- 带排名
SELECT object_id, description,
       ts_rank(tsvec, to_tsquery('simple', 'clouds & sunset')) AS rank
FROM metadata
WHERE tsvec @@ to_tsquery('simple', 'clouds & sunset')
ORDER BY rank DESC
LIMIT 20;

我记得有一次做图库元数据检索，用户搜索“黄浦江夜景”，要求把“外滩”、“浦东”、“陆家嘴”、“夜景”这些相关记录也排到前面。普通 LIKE 根本做不到，全文检索加上同义词词典后，效果直接翻倍。

小技巧：你可以给每个文档建不同的权重字段。比如标题权重 A，描述权重 B。用 setweight() 函数，查询时按权重算分，确保标题匹配的排在前面。

4.3 分区表：让大数据不再拖垮查询

元数据会随着业务增长越堆越多。一张表几亿条记录，就算索引建得再好，维护成本也受不了。分区表能按时间、按地域把数据切到不同的物理文件里，查询时只扫描相关分区。

PostgreSQL 的分区支持声明式分区（Declarative Partitioning），从 10 版本开始就很稳了。我一般按时间分区，因为元数据的查询天然有时间范围。

-- 创建分区主表
CREATE TABLE metadata_partitioned (
    id          BIGSERIAL,
    object_id   UUID NOT NULL,
    attributes  JSONB,
    created_at  TIMESTAMPTZ NOT NULL,
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

-- 创建分区
CREATE TABLE metadata_2024_q1 PARTITION OF metadata_partitioned
    FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');

CREATE TABLE metadata_2024_q2 PARTITION OF metadata_partitioned
    FOR VALUES FROM ('2024-04-01') TO ('2024-07-01');

-- 再建索引，每个分区可以独立管理
CREATE INDEX idx_metadata_2024_q1_attr
ON metadata_2024_q1 USING GIN (attributes);

查询的时候完全透明：

-- 查询 2024年1月到3月的数据
-- PostgreSQL 会自动只扫描 metadata_2024_q1 分区
SELECT * FROM metadata_partitioned
WHERE created_at >= '2024-01-01'
  AND created_at < '2024-04-01'
  AND attributes @> '{"camera": "Nikon Z8"}'::jsonb;
核心收益：分区后，DROP 整个分区比 DELETE 百万行快几十倍。而且每个分区可以单独做 VACUUM、REINDEX，不影响其他分区。我的项目里原先清理旧数据要锁表 10 分钟，分区后一秒都不用锁。

分区时要注意几个点：

  • 主键必须包含分区键。上面例子中 PRIMARY KEY (id, created_at) 就是这个原因。
  • 分区数量别太多，几百个以内还行，几千个会让优化器变慢。
  • 跨分区查询如果没带分区键条件，会扫描所有分区，性能反而更差。所以设计查询时要保证 WHERE 条件有分区键。

4.4 组合起来：典型元数据存储架构

三个特性不是孤立的。我们把它拼成一张图看看整体结构。

PostgreSQL 元数据存储检索核心架构 元数据写入/查询 分区表 (RANGE) JSONB 字段 柔性结构 + GIN 索引 全文检索 tsvector + tsquery 分区管理 按时间分区 + 分区裁剪 查询结果：结构 + 文本 + 分区裁剪 实战中：JSONB 存灵活属性，全文检索搜描述文本，分区表保证大表性能——三者通过索引和查询优化器无缝协作。

上面这张图把数据流走了一遍。应用层写入或查询时，分区表根据时间把数据路由到对应分区；分区内的每条记录用 JSONB 存非结构化元数据，用 tsvector 字段存全文检索向量；查询时分区裁剪 + GIN 索引 + 全文检索三管齐下。

4.5 一个完整的建表脚本

最后，给你一个可以直接拿去用的脚本。我把它放在生产环境跑了快两年，基本没出过问题。

-- 主表（分区表）
CREATE TABLE metadata (
    id          BIGSERIAL,
    object_id   UUID NOT NULL,
    title       TEXT,
    description TEXT,
    tags        JSONB DEFAULT '[]'::jsonb,
    attributes  JSONB DEFAULT '{}'::jsonb,
    tsvec       TSVECTOR,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

-- 分区：按季度
CREATE TABLE metadata_2024_q1 PARTITION OF metadata
    FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');
CREATE TABLE metadata_2024_q2 PARTITION OF metadata
    FOR VALUES FROM ('2024-04-01') TO ('2024-07-01');

-- 索引
CREATE INDEX idx_metadata_attributes_gin
    ON metadata USING GIN (attributes);
CREATE INDEX idx_metadata_tags_gin
    ON metadata USING GIN (tags);
CREATE INDEX idx_metadata_tsvec
    ON metadata USING GIN (tsvec);

-- 触发器：插入/更新时自动生成 tsvector
CREATE OR REPLACE FUNCTION metadata_tsvec_trigger()
RETURNS TRIGGER AS $$
BEGIN
    NEW.tsvec :=
        setweight(to_tsvector('simple', coalesce(NEW.title, '')), 'A') ||
        setweight(to_tsvector('simple', coalesce(NEW.description, '')), 'B');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_metadata_tsvec
    BEFORE INSERT OR UPDATE OF title, description
    ON metadata
    FOR EACH ROW
    EXECUTE FUNCTION metadata_tsvec_trigger();

嗯，到这里你基本能架起一套能应对百万级元数据的 PostgreSQL 存储了。我之前接手过一个遗留系统，它们把所有元数据都丢在一张大表里，没有任何分区，也没用 JSONB。每天凌晨跑个定时任务去清理过期数据，经常把生产库拖死。后来我按上面的方案重构，三个月下来，查询响应从秒级降到毫秒级，半夜再也没接到过告警电话。

← 上一章 📖 返回目录 下一章 →