上一篇《MySQL 老手转 PostgreSQL》讲的是「照搬 MySQL 会踩什么坑」,全是减法。这篇反过来讲加法:PG 自带的几个能力,用好了能把「本来要上一个中间件、或者写一堆业务代码」的问题,在数据库内部解决。

三块内容,每块都对应一类具体的业务场景:

  • jsonb —— 业务里总有些结构不固定的数据(订单扩展字段、租户自定义字段、外部报文),MySQL 8 之前这类数据的查询要么在业务代码里做、要么维护辅助列;jsonb 把查询和索引放回数据库
  • 咨询锁(advisory lock) —— 多实例下的任务互斥、临界区里要写业务库的场景,一把不需要 Redis 的分布式锁,关键差异不是性能而是锁和业务数据在同一个事务里
  • pgvector —— RAG 和语义检索的向量召回放回业务库,和业务数据同库同事务,不用再维护一条向量同步链路

这篇的重点是使用场景和用法,实测数字只是为了给代价和收益一个量级——都是我在自己的 PG 18.7 + pgvector 0.8.7 / Redis 7.4.12 上跑出来的(10 核 / 7.8 GB Docker 容器,数据为脚本生成的合成数据),不是抄文档。召回率这类指标和你的数据分布强相关,绝对值只能当量级参考,相关倍数关系和哪里可能有问题才是可供大家参考的部分。

一、jsonb:结构不固定的那部分数据,也能在库里查

先说场景。需要存 JSON 的业务很常见,和用不用文档数据库没什么关系:

  • 订单/工单的扩展字段 —— 每接入一条业务线就多几个字段,主表结构改不动
  • 租户自定义字段 —— 每个租户的表单 schema 不一样,固定列建不出来
  • 外部系统的原始报文 —— 存一份原文,对账、排查、重放要用
  • 流程快照 —— 审批流、状态机在某一刻的完整上下文

这类数据在 MySQL 5.7 时代很尴尬:JSON 类型 5.7 就引入了,但没有能直接用的索引手段——按路径取值建生成列 + 普通 B-tree 是唯一路径,数组条件(「tags 里有 vip」)要到 8.0.17 的多值索引才有解。在此之前,「按 JSON 内容查」的需求要么在业务代码里过滤,要么双写辅助列。PG 这边 jsonb + GIN 索引把这一步放回了数据库(这套能力 9.x 时代就成熟了)。

第 1 条:选 jsonb,不选 json

两个 JSON 类型选起来没什么悬念:json 存的是原文文本,查询时现解析,没法建索引;jsonb 存的是解析后的二进制结构,可查可索引。代价是存储多两成多(同一批 50 万条订单文档实测 170 MB → 217 MB),换来的是可以建 GIN 索引、做包含查询、判断键存在。另外 jsonb 存数字走 numeric,金额不会出浮点误差。业务里几乎没有理由不用 jsonb,但仍然建议:能建列的就建列,jsonb 只放结构不固定的部分(第 5 条会展开为什么)。

第 2 条:日常写法速查,核心是 @> + GIN 索引

先速查一下常用写法:

需求 写法
取字段(结果还是 jsonb) doc->'buyer'、doc#>'{buyer,city}'
取字段转文本 doc->>'status'
含有这段结构 doc @> '{"tags":["vip"]}'
键 / 数组元素存在 doc ? 'amount'、doc ?| array['a','b']
更新一个字段 jsonb_set(doc, '{buyer,city}', '"北京"')

先回答一个基础问题:jsonb 列不会自动带索引,GIN 要手动建:

1
2
CREATE INDEX idx_jt_doc ON jt_jsonb USING gin (doc);                 -- 默认的 jsonb_ops
CREATE INDEX idx_jt_doc ON jt_jsonb USING gin (doc jsonb_path_ops); -- 只用 @> 一族时更省空间(见第 3 条)

GIN 服务的是查询条件,不是查询结果:它回答「哪些行的 JSON 里含有这段内容、有这个键」,让 WHERE doc @> ...、WHERE doc ? 'amount' 从全表扫描变成索引查找。至于 SELECT 出哪些列、ORDER BY 怎么排,GIN 一概不管——那是 btree 的事(见第 4 条)。反过来,一个 GIN 索引可以同时服务 @>、? 这些不同的条件。

其中包含查询 @> 是「按 JSON 内容查」的标准写法,配上 GIN 索引:WHERE doc @> '{"buyer":{"id":12345}}' 能命中任何 buyer.id 等于 12345 的文档——@> 是包含语义,不管它还有多少别的字段。实测 50 万行:无索引 65 ms,GIN 后 0.2 ms,300 倍,这就是 jsonb 值得用的核心理由。

第 3 条:jsonb_path_ops 省 26% 体积,但会让 ? 查询静默退化

两个 opclass 的区别:

opclass 索引大小(500k 行) 支持 @> / @? / @@ 支持 ? / ?| / ?&(键存在性)
jsonb_ops(默认) 113 MB 支持 支持
jsonb_path_ops 84 MB(省 26%) 支持 不支持,索引用不上

坑在于「用不上」不会报错:只建了 jsonb_path_ops 时,doc ? 'amount' 结果照样对,只是悄悄退化成全表扫描——结果对了,性能差三个数量级,线上只会表现为「这个接口偶尔慢」。

选择规则很简单:只用 @> 一族就用 jsonb_path_ops;要判断键存不存在(「有没有填这个字段」这类需求很常见)就老老实实用默认的 jsonb_ops。

第 4 条:->> 取出来的标量,索引管不到

最常见的写法其实是这种:

1
2
SELECT id FROM jt_jsonb
WHERE doc->>'status' = 'paid' AND doc->'buyer'->>'city' = '杭州';

->> 是「取值再转 text」,取完就是一个普通字符串,和索引没有任何关系。要索引就得建表达式索引:CREATE INDEX idx_status ON jt_jsonb ((doc->>'status'));

但表达式索引只适合高选择性字段(订单号、外部 ID、租户内的唯一键)。低基数维度字段建了反而更慢——status 只有 4 个取值时,实测走索引回表 61 ms,还不如顺序扫的 27 ms。判断标准:命中行数超过全表的百分之几,就别指望 btree 索引了。

第 5 条:写 jsonb 是要付利息的,别把整张表塞进一个字段

GIN 索引不是免费的:实测同样写 20 万行,带 GIN 的批量 INSERT 慢 2.8 倍、全表 UPDATE 慢 2 倍。另外大 JSON 会进 TOAST,实际体积取决于内容重复度,不好预估。

所以边界很明确:能建列的字段就建列(查询、统计、约束都强一个量级),jsonb 留给「每个租户结构不一样」的那部分——别把整行数据塞进一个字段当表用。

二、咨询锁:一把不需要 Redis 的分布式锁

先说场景。咨询锁解决的是几类很具体的互斥问题:

  • 后台任务单例 —— 多实例部署下,「这个清理任务同一时刻只允许跑一个」
  • 一次性操作互斥 —— 数据迁移、刷数脚本、定时对账,只允许一个实例执行
  • 临界区里要写业务库 —— 锁和数据在同一个事务里,这是它真正无可替代的地方,下面会展开

有人用 SELECT ... FOR UPDATE 做互斥,但行锁要求「那行得先存在」,还会和正常业务写入互相排队;咨询锁的 key 是任意整数,不碰业务表,粒度完全自由。

结论先行:咨询锁不是「Redis 锁的更快版本」,它解决的是另一个问题——锁的生命周期和业务事务边界对齐,和业务数据同生共死。

第 6 条:会话级和事务级是两套锁,命运完全不同

PG 的 advisory lock 有两族函数,签名一样,生命周期完全不同:

函数族 何时释放 能显式解锁吗
pg_advisory_lock(key) 显式 pg_advisory_unlock 或会话断开 能
pg_advisory_xact_lock(key) 事务结束(提交或回滚) 不能,也不用

实测了两个最容易记错的点:

1
2
3
4
5
6
7
8
9
-- 会话级锁:放在事务里加,回滚后还在
BEGIN; SELECT pg_advisory_lock(44); ROLLBACK;
回滚后仍持有 = 1 ← 事务回滚不影响它

-- 事务级锁:事务结束自动没了
BEGIN; SELECT pg_advisory_xact_lock(45);
事务内 = 1
COMMIT;
提交后 = 0

所以:要跟着业务事务走,一律用 pg_advisory_xact_lock。用会话级锁套在 @Transactional 里,一旦业务回滚,锁还攥着,必须自己记得 unlock——而这正是「锁泄漏」的经典来源。

反过来,会话级锁也有它的用处:跨多个事务的「长活」(比如「这个数据迁移任务只允许一个实例跑」),事务级锁管不住这么长的边界,会话级锁可以。

顺便验证了一个好消息:会话被 kill 掉,锁自动释放。持锁会话被 pg_terminate_backend 干掉之后,pg_locks 里的 advisory 行数归零。不存在「进程崩了锁永久卡死」这种事(对比 Redis 就得靠 TTL 兜底)。

第 7 条:可重入是「记账」,在 pg_locks 里看不出来

同一个会话对同一把锁加两次不会自己卡死(可重入),但要解锁同样次数才真正释放:

1
2
3
SELECT pg_advisory_lock(46); SELECT pg_advisory_lock(46);
SELECT pg_advisory_unlock(46); -- t,但锁还在
SELECT pg_advisory_unlock(46); -- t,这次才真释放

注意 pg_locks 里始终只有一行:它显示的是「持有状态」,不是「加锁次数」——排查「谁把锁占了」时看到一行,不代表只加过一次。所以用会话级锁必须把 lock/unlock 成对写在一个 try/finally 里。

第 8 条:锁是「库级」的,key 空间别混用

作用域这个问题很容易想歪:咨询锁的作用域是单个数据库——实测一个库持有 key=77,另一个库(同一个实例)pg_try_advisory_lock(77) 照样返回 true,不冲突;它也不是表级别的东西。

另一个容易踩的点:pg_advisory_lock(123)(bigint 形式)和 pg_advisory_lock(0,123)(两个 int 形式)是两个不重叠的 key 空间,pg_advisory_lock(1,1) 和 pg_advisory_lock(4294967297) 同理。混用不会报错,只会两拨人各自「拿到锁」却互相不知道——写业务时选定一种形式就别再换。

排查「谁持有、谁在等」用这个查法。pg_locks 不是业务表,是锁状态的实时系统视图:所有锁(表锁、行锁、咨询锁)当前被持有或被等待就占一行,释放即消失、不留历史:

1
2
3
SELECT l.pid, a.usename, a.state, left(a.query, 40) AS query, l.mode, l.granted
FROM pg_locks l JOIN pg_stat_activity a USING (pid)
WHERE l.locktype = 'advisory';

持有者(granted = t)和排队者(granted = f)都在结果里,排队者还能看到它正在跑哪条 SQL——这一点比 Redis 强。配套的控制手段也都覆盖咨询锁:lock_timeout 对锁等待有效、不会无限挂住,死锁检测同样生效——毕竟「阻塞等待 + 没有超时」是分布式锁最经典的线上事故形态。

第 9 条:和 Redis 分布式锁的对比,差别不在性能

先说性能,因为最容易误判:实测两边都在 0.1 ms 以内(PG 锁加解锁 0.055 ms,Redis SET NX 0.039 ms,差距 1.4 倍)——如果你的临界区本身要执行几条 SQL,这点差异根本测不出来。「PG 锁太慢所以必须用 Redis」在你已经有 PG 的场景里通常不成立。

真正的差别在这几处:

维度 PG 咨询锁 Redis 锁
与业务数据的原子性 同一个事务:锁和数据要么一起成功、要么一起回滚 两个系统,只能靠代码顺序保证
释放 事务结束自动释放;进程被杀也会释放 只能等 TTL 过期(进程挂了要等几十秒)
可重入 原生支持,加几次解几次 不支持,要自己在 value 里计数
阻塞等待 支持阻塞等待,有 lock_timeout、参与死锁检测 只能自己轮询,或用 pub/sub 通知
可观测 pg_locks 能看到持有者、等待队列、正在跑的 SQL 只能看到 key 和 TTL
跨服务/跨语言 只对连同一个库的进程有效 天然跨进程跨语言
主从切换丢锁 不存在这个问题(锁不参与复制) 会丢,见下

最后一行不是理论。实测(Redis 7.4.12 + 一个 replica):

1
2
master 上 SET lock:order:1 ownerA NX PX 30000  →  OK
replica 上 EXISTS lock:order:1 → 0 ← 复制还没到

Redis 的复制是异步的:如果此刻 master 宕机、从库被提升,这把锁就消失了——客户端 A 还在干活,客户端 B 又能拿到同一把锁,临界区同时进了两个人。这也是 Kleppmann 和 antirez 围绕 Redlock 争论的核心(Kleppmann、antirez):光有「我拿到了锁」这个布尔值,不足以保护外部资源;而咨询锁和业务数据同库同事务,反而容易做对。顺带一句:Redis 的 GETDEL 不能用来解锁——它不校验持有者,「比对 value 再删」只能靠 Lua 脚本。

第 10 条:什么时候该用咨询锁,什么时候别用

适合:

  • 后台任务、定时任务单例(「这个清理任务同一时刻只跑一个」)
  • 数据迁移、一次性脚本的互斥
  • 临界区里要写业务库的场景——这是咨询锁真正无可替代的地方:锁和数据同库同事务,不需要「先拿锁再开事务」这种容易写错的顺序
  • 需要「锁 + 等待队列 + 超时 + 死锁检测」这套成熟语义,而不是自己写轮询

不适合:

  • 跨服务、跨语言、跨实例的协调(咨询锁只对连同一个库的进程有效)
  • 热点极高的短临界区(所有锁竞争都压到 DB 上,且会占着连接)
  • 需要「锁自动过期」的长任务兜底(咨询锁没有 TTL,只有连接断开这一种兜底)
  • 用了 PgBouncer 事务模式还想要会话级锁的语义——连接会被复用和切换,会话级锁可能落在别的客户端手里,只能用事务级锁

用法上有一条铁律:必须在同一个连接、同一个事务里加锁。在 Spring 里就是:

1
2
3
4
5
6
@Transactional
public void settle(String tenantId) {
// 必须在事务内、用事务级锁:锁随事务一起释放,不会泄漏
jdbcTemplate.queryForObject("SELECT pg_advisory_xact_lock(?)", Void.class, hash(tenantId));
// ... 业务逻辑,和锁在同一个事务里
}

如果你在事务外先 pg_advisory_lock 再开事务,那个连接和事务的连接不是同一个(连接池场景),锁保护的就不是你以为的那段代码。

三、pgvector:把向量检索放回业务库

先说场景:RAG 的文档召回、相似案例推荐、语义搜索——本质都是「按向量相似度找 top-k」,而且往往还要带上业务条件(租户、类目、权限、时间范围)一起查。如果向量放在独立的向量库里,就得额外养一条同步链路,还要处理「业务数据刚变、向量还是旧的」这个窗口期。放进业务库,检索可以直接 join 业务表、跟着业务事务走——这是 pgvector 最大的价值。

在 RAG 流程里它只负责「存」和「取」两步:文档切块 → 调 embedding 模型拿到向量 → 连同原文一起写进 chunks 表;用户提问时把问题也向量化,按相似度捞 top-k 几个 chunk,拼进 prompt 给 LLM。切块、提示词、重排都是应用层的事,pgvector 只管向量这一层。

第 11 条:安装:装的就是一个扩展

pgvector 不是独立的数据库,是 PG 的一个扩展,三种常见装法:

装法 怎么做 适合
Docker(最省事) 直接用 pgvector/pgvector:pg18 这类官方镜像(标签跟 PG 大版本走),里面已经编译好 新项目、本地开发
发行版包 PGDG 源里的预编译包(apt:postgresql-18-pgvector,yum 类似,包名跟 PG 大版本走) 已有自建 PG
源码编译 make && make install(要装对应大版本的 server dev 包) 包跟不上的平台

装完在库里启用,一行的事:

1
2
3
4
CREATE EXTENSION vector;

-- 确认版本:不同版本的索引能力不一样,排查问题先看它
SELECT extversion FROM pg_extension WHERE extname = 'vector';

它带来的就是三种向量类型(vector / halfvec / bit)和两种索引方法(HNSW / IVFFlat),不动 PG 本体。

第 12 条:RAG 的标准用法:建表、写入、检索

建表没什么特别的,向量列就是一个普通列:

1
2
3
4
5
6
7
8
CREATE TABLE chunks (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
doc_id bigint NOT NULL, -- 挂回业务表,方便回查
content text NOT NULL, -- 切出来的文本块
embedding vector(768) NOT NULL -- 维度必须和 embedding 模型的输出一致
);

CREATE INDEX ON chunks USING hnsw (embedding vector_cosine_ops);

写入就是普通的 INSERT:应用侧调 embedding 模型(本地 BGE,或者 OpenAI 这类 API)拿到浮点数组,落库即可。各语言都有对应的客户端包(Java 是 pgvector-java,Python 是 pgvector),不引包就按字符串传也行:

1
2
INSERT INTO chunks (doc_id, content, embedding)
VALUES (1, '文本块……', '[0.12,-0.03,…]'::vector);

检索是唯一需要学的新语法——按距离排序取 top-k:

1
2
3
4
5
-- 语义检索 top-10,距离越小越相似
SELECT id, content
FROM chunks
ORDER BY embedding <=> $1::vector
LIMIT 10;

四个距离运算符里最常用的是 <=> 余弦距离(文本检索基本都用它),另有 <-> L2 欧氏距离、<#> 负内积、<~> 汉明距离(bit 类型)。索引的 opclass(vector_cosine_ops / vector_l2_ops / vector_ip_ops)要和查询里的运算符对上,对不上就静默退化成顺序扫描——又是一个不报错、只会慢的坑。

维度只有一个要求:和模型输出一致。HNSW 索引的维度上限是 2000(halfvec 是 4000),768/1024 维的模型(BGE、GTE 这类)什么都不用管;输出 1536/3072 维的模型要么把列建成 halfvec 类型,要么在调模型时降维——text-embedding-3 系列可以直接用 dimensions 参数把输出降到 1024 以内。

第 13 条:带业务条件的检索,是 RAG 里最容易崩的地方

业务里的检索几乎都要带条件——「只搜这个租户的」「只搜这个类目」。这类查询会以「功能突然不好使」的形式出现在上线之后,值得单独说。

pgvector 的过滤是在索引扫描之后做的,过滤条件越严格、剩下的越少(ef_search=40、过滤只命中 5% 的行时,期望结果只剩 2 条)。实测(20 个类目,查其中一个 5000 行的小类目,LIMIT 10):

配置 平均返回条数 最少 最多
默认(iterative_scan = off) 2.38 0 6
iterative_scan = relaxed_order 10.00 10 10
iterative_scan = strict_order 10.00 10 10

LIMIT 10 的查询平均只返回 2 条多,最差一条都没有。这不是 bug,是近似索引 + 后置过滤的必然结果——用户看到的是「按类目筛选后什么都搜不到」,而同一个查询不筛类目一切正常。

0.8.0 之后有现成的开关,取值只有三个:

1
SET hnsw.iterative_scan = relaxed_order;   -- 结果有序性略松,召回更好;或者 strict_order 严格有序

按会话 SET、或 ALTER DATABASE ... SET 全库生效都行。开启后会继续扫索引直到凑够结果或触到 hnsw.max_scan_tuples(默认 20000),代价是这类查询变慢——过滤条件越严,扫得越久。

更彻底的做法,按过滤列的选择性来选:

过滤条件的选择性 做法
高(能筛掉 90%+) 给过滤列建 B-tree,让规划器走「索引过滤 → 精确排序」,可能比 HNSW 还快
中 开 iterative_scan,或把过滤列做进复合索引
低基数字段(类目、租户) 部分索引(WHERE category = 3)或按租户分区,每类一个索引
多租户混跑 分区;共用一把 HNSW 会让不同租户互相挤占候选位,召回和质量都受影响

第 14 条:上线前的三件小事

一、建 HNSW 前把 maintenance_work_mem 调大。 粗估 4×维度×行数:10 万行 × 768 维就是 300 MB 起步,给 1 GB 没错。不调也不报错,只是一句「图装不下内存」的 NOTICE,然后慢得没法用(图在内存和磁盘之间来回倒);实测给够 1 GB,10 万行 75.8 秒建完。

二、容量按「向量本体 + 同样大的索引」来估。 100k × 768 维实测:向量本体 391 MB(在 TOAST 里)+ HNSW 索引 391 MB。所以看体积要用 pg_total_relation_size——vector 列是变长类型、内容进 TOAST 旁表,只看 pg_relation_size 会以为「向量几乎不占空间」。索引超过 shared_buffers(默认 128 MB)后,同一条查询冷 19.9 ms、热 0.6 ms:看到「向量检索忽快忽慢」,先查内存命中率,不要急着调参。

三、ef_search 默认就是对的。 HNSW 是近似检索,hnsw.ef_search(默认 40)决定一次查询考察多少候选。实测 10 万行 top-10:默认值召回 0.972、单条 5.7 ms,而精确的顺序扫描要 243 ms——这就是上近似索引的全部理由。要 LIMIT 更大或要重排时再把它调到 100~200(召回 0.992,耗时几乎不变);提到 400 收益只剩 0.004,纯属为用不上的召回率付延迟。

四、这三件事各自解决了什么

能力 解决的问题 付出的代价
jsonb + GIN 半结构化字段的查询与索引(MySQL 8 之前基本只能在业务代码里做) 存储 +28%;写入 2~3 倍;能被索引的查询形态有限
咨询锁 同库场景的任务互斥与事务内互斥(省掉一个 Redis) 只对连同一个库的进程有效;锁竞争压在 DB 上;没有 TTL
pgvector RAG 的向量召回(省掉一个独立向量库) 索引比向量本体还大;过滤场景要额外处理;调参要自己量召回

三件事有一个共同点:把一类原本要出库解决的问题放回数据库,省掉的都是跨系统、跨层的一致性协调:

  • jsonb 省掉的是「结构化查询」和「灵活字段」之间的取舍
  • 咨询锁省掉的是「业务事务」和「分布式锁」之间那段永远对不齐的时序
  • pgvector 省掉的是「向量库 → 业务库」的同步链路和它的延迟窗口

反过来说,一旦你不需要这种「同库一致性」,这三件东西也就没有优势了:跨服务协调还是 Redis 顺手,十亿级向量还是专用引擎合适,schema 天天变的日志海还是文档库。工具没有更好,只有边界更清楚。