PostgreSQL 的扩展能力:jsonb、咨询锁与向量检索
上一篇《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 | CREATE INDEX idx_jt_doc ON jt_jsonb USING gin (doc); -- 默认的 jsonb_ops |
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 | SELECT id FROM jt_jsonb |
->> 是「取值再转 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 | -- 会话级锁:放在事务里加,回滚后还在 |
所以:要跟着业务事务走,一律用 pg_advisory_xact_lock。用会话级锁套在 @Transactional 里,一旦业务回滚,锁还攥着,必须自己记得 unlock——而这正是「锁泄漏」的经典来源。
反过来,会话级锁也有它的用处:跨多个事务的「长活」(比如「这个数据迁移任务只允许一个实例跑」),事务级锁管不住这么长的边界,会话级锁可以。
顺便验证了一个好消息:会话被 kill 掉,锁自动释放。持锁会话被 pg_terminate_backend 干掉之后,pg_locks 里的 advisory 行数归零。不存在「进程崩了锁永久卡死」这种事(对比 Redis 就得靠 TTL 兜底)。
第 7 条:可重入是「记账」,在 pg_locks 里看不出来
同一个会话对同一把锁加两次不会自己卡死(可重入),但要解锁同样次数才真正释放:
1 | SELECT pg_advisory_lock(46); SELECT pg_advisory_lock(46); |
注意 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 | SELECT l.pid, a.usename, a.state, left(a.query, 40) AS query, l.mode, l.granted |
持有者(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 | master 上 SET lock:order:1 ownerA NX PX 30000 → OK |
Redis 的复制是异步的:如果此刻 master 宕机、从库被提升,这把锁就消失了——客户端 A 还在干活,客户端 B 又能拿到同一把锁,临界区同时进了两个人。这也是 Kleppmann 和 antirez 围绕 Redlock 争论的核心(Kleppmann、antirez):光有「我拿到了锁」这个布尔值,不足以保护外部资源;而咨询锁和业务数据同库同事务,反而容易做对。顺带一句:Redis 的 GETDEL 不能用来解锁——它不校验持有者,「比对 value 再删」只能靠 Lua 脚本。
第 10 条:什么时候该用咨询锁,什么时候别用
适合:
- 后台任务、定时任务单例(「这个清理任务同一时刻只跑一个」)
- 数据迁移、一次性脚本的互斥
- 临界区里要写业务库的场景——这是咨询锁真正无可替代的地方:锁和数据同库同事务,不需要「先拿锁再开事务」这种容易写错的顺序
- 需要「锁 + 等待队列 + 超时 + 死锁检测」这套成熟语义,而不是自己写轮询
不适合:
- 跨服务、跨语言、跨实例的协调(咨询锁只对连同一个库的进程有效)
- 热点极高的短临界区(所有锁竞争都压到 DB 上,且会占着连接)
- 需要「锁自动过期」的长任务兜底(咨询锁没有 TTL,只有连接断开这一种兜底)
- 用了 PgBouncer 事务模式还想要会话级锁的语义——连接会被复用和切换,会话级锁可能落在别的客户端手里,只能用事务级锁
用法上有一条铁律:必须在同一个连接、同一个事务里加锁。在 Spring 里就是:
1 |
|
如果你在事务外先 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 | CREATE EXTENSION vector; |
它带来的就是三种向量类型(vector / halfvec / bit)和两种索引方法(HNSW / IVFFlat),不动 PG 本体。
第 12 条:RAG 的标准用法:建表、写入、检索
建表没什么特别的,向量列就是一个普通列:
1 | CREATE TABLE chunks ( |
写入就是普通的 INSERT:应用侧调 embedding 模型(本地 BGE,或者 OpenAI 这类 API)拿到浮点数组,落库即可。各语言都有对应的客户端包(Java 是 pgvector-java,Python 是 pgvector),不引包就按字符串传也行:
1 | INSERT INTO chunks (doc_id, content, embedding) |
检索是唯一需要学的新语法——按距离排序取 top-k:
1 | -- 语义检索 top-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 天天变的日志海还是文档库。工具没有更好,只有边界更清楚。
