PostgreSQL 教程：从建表查询到全文搜索与向量检索

一、为什么是 PostgreSQL：先看清它和你熟悉的 MySQL 差在哪

先还原一个常见场景。你写了多年 Java，库一直是 MySQL，MyBatis 一把梭；新项目立起来了，需求栏里躺着三行字：商品搜索要支持联想与相关度排序、App 要找「3 公里内的门店」、客服知识库要做语义检索。用 MySQL，这三件事意味着引入三个独立组件（Elasticsearch、专门的地理服务、向量数据库）；用 PostgreSQL，答案全是同一句：装个扩展就行。

这也是很多团队切 PG 的真实动机——不是因为它「更强」，而是因为它能少养几个系统。但动手之前，有几处差异值得先钉在墙上：

这门课的路线图

第二到十二章是地基：安装连接 → 基本结构 → 建表 → 单表查询 → 函数 → 多表 → 索引 → 事务与锁 → 视图与 CTE → 权限 → 备份恢复。第十三到十五章是三个扩展专题（全文搜索 / 地理位置 / 向量），可以按需跳读，也可以顺着读——它们正好是第一章那张对比表里「扩展生态」的现场证明。第十六章是一份上线核对清单。

浏览器里跑不了 SQL，所以示例沿用 Linux / Docker 教程的做法：写死真实输出的模拟终端，点按钮逐条回放命令与 psql 的真实打印；三个扩展章节另配了可交互的小页面——数据是静态样本，但打分、测距、找近邻的计算是真算，不是录像。

⚠️ 两处最容易想当然的差异

上面那张表里，有两行值得在动手前先展开说透，它们会贯穿整门课。

每连接一个进程。 MySQL 里一个连接是一个线程，PG 里主进程 postmaster 会为每个连接 fork 一个 backend 进程，内存几 MB 起步。两个直接推论：

1. 连接是贵重资源，max_connections 别照 MySQL 的习惯开几千——100~300 之间，前面再顶一层 PgBouncer 连接池（对习惯 HikariCP 的 Java 同学来说：应用的池要收小，池外面还有一层池）；
2. 进程模型换来一套独特的崩溃语义：单条 SQL 报错只影响它自己；但如果某个 backend 真的段错误了，postmaster 的策略是把全部连接断开、重置共享内存再迎接新连接——宁可全体重启，也不带病运行。线上看到「一夜之间所有连接同时断开重连」，先去翻 PG 日志里的 server process was terminated by signal。

默认隔离级别不同。 MySQL InnoDB 默认 REPEATABLE READ，PG 默认 READ COMMITTED。看着是配置差异，实际是行为差异：同一个事务里两次执行相同的 SELECT，MySQL 默认读到同一个结果，PG 默认第二次能看到别人已提交的新数据。如果迁移过来的代码依赖「事务内复读」，第九章会给出三种处理姿势。

还有一条建表当天就会碰到的：不加引号的标识符一律转小写。写成 CREATE TABLE "Users" (...) 之后，每次查询都得 "users" 带引号伺候，漏一次就报 relation "users" does not exist。规矩很简单——建表 SQL 里对标识符一律不加双引号。

二、安装与第一次连接：三种姿势与 psql 的肌肉记忆

本章分三段：先一条命令把库起起来连上（第一步），再看三种安装姿势和 psql 的基本功（第二到四步），章末集中放这一章会撞的坑与命令速查表。

第一步：一条命令起一个能连的库

本地想有个能折腾的 PG，Docker 最省事：

docker run -d --name pg-lab \
  -e POSTGRES_PASSWORD=lab123456 \
  -e POSTGRES_DB=shop \
  -p 5432:5432 \
  -v pg-lab-data:/var/lib/postgresql/data \
  postgres:17

等两三秒，连上去：

psql -h 127.0.0.1 -p 5432 -U postgres -d shop

psql (17.x)
Type "help" for help.

shop=# SELECT version();
                          version
-----------------------------------------------------------
 PostgreSQL 17.x on x86_64-pc-linux-gnu, compiled by gcc...
(1 row)

看到提示符变成 shop=# 就连上了：# 表示超级用户，普通用户是 shop=>。\q 退出。四个参数的含义：POSTGRES_PASSWORD 必填（缺了容器直接起不来）、POSTGRES_DB 顺手建好第一个业务库、-p 把容器内 5432 映射到宿主、-v 挂的卷是数据本体。

第二步：三种安装方式，选一种就够

服务器上长期跑，用包管理器：

sudo apt install postgresql-17          # 先按官方文档加 PGDG 源
sudo systemctl status postgresql@17-main
sudo -u postgres psql                   # 本地 peer 认证，免密直接进

Debian 系装完会自动 initdb 一个「数据库簇」，并创建同名 OS 用户 postgres（数据库管理员同体），sudo -u postgres psql 相当于 MySQL 里的 mysql -u root 免密进。另外容器场景有个偷懒通道：docker exec -it pg-lab psql -U postgres -d shop，走容器内部 socket，不用输密码——只限实验环境。

第三步：psql 里先练这四个命令

连上之后，日常 90% 的操作就四个：\dt 看有哪些表、\d 表名 看单表结构、\l 看有哪些库、\timing on 打印每条 SQL 的耗时。

\d 是用得最多的一个：建完表不用猜索引建没建对，\d 表名 一屏把列、类型、索引、约束全看到。完整命令表（含 \x、\copy 等）在本章末尾的速查区。

第四步：不想每次输密码

不想每次交互输密码，两种姿势：临时用环境变量 PGPASSWORD=... psql ...；长期用 ~/.pgpass，格式一行一条 主机:端口:数据库:用户:密码。超级用户第一件事建议 \password 改掉弱密码——谁能连、用什么方式认证，由 pg_hba.conf 把关，第十一章展开。

⚠️ 本章的坑

1. 没挂数据卷 = docker rm 之后数据全没。 -v pg-lab-data:/var/lib/postgresql/data 这一行是数据本体，删容器不带 -v 参数的重建就是清库。

2. 只有卷为空时才会 initdb。 所以改了 POSTGRES_PASSWORD、POSTGRES_DB 这些环境变量，重启老容器不会生效——它们只在第一次初始化时起作用。想重来，得连卷一起删（docker volume rm pg-lab-data）。

3. 镜像 tag 写大版本号，别写 latest。 跨大版本（16 → 17）不是换个 tag 拉个新镜像就完事，要靠 pg_upgrade 迁移；写 latest 等于把版本交给别人决定。

4. SQL 必须以 ; 结尾才执行。 多行语句写到一半没分号，提示符会变成 shop- 静静等你，看着像「卡死」，其实是它以为你还没写完——补个分号回车就回来了。

5. .pgpass 权限不是 600 会被直接忽略。 PG 认为别人能读的密码文件不可信，报的还是「密码认证失败」这种不相干的错，很难往这上面想。chmod 600 ~/.pgpass。

6. Debian/Ubuntu 官方源的版本偏旧。 想跟这门课用 17，要按官方文档加 PGDG 源，否则 apt install postgresql 装出来的是十几年前语义的老版本。

7. docker exec 那条免密通道只限实验。 它走容器内部 socket，绕过了 pg_hba.conf 的密码认证；生产环境照抄等于给自己留了个免认证入口。

附：psql 反斜杠命令速查

三、基本结构：实例、数据库、schema 与表——多出来的那一层是什么

本章分三段：先看清 PG 比 MySQL 多出来的那层是什么（第一步），再动手建一个 schema 把表放进去（第二步），然后把建库时一次定死、事后改不了的几件事放到前面（第三、四步），章末集中收坑。

第一步：多出来的那一层是 schema

PG 的层级比 MySQL 多一层。一个实例（postmaster 进程 + 共享内存 + 一份数据目录，官方术语叫 cluster，数据库簇——和「分库分表集群」不是一个东西，别被名字吓到）下面挂多个 database，database 里再分 schema，schema 里才是表、序列、函数、视图：

实例（postmaster + 共享内存 + 数据目录，官方叫 cluster）
 ├── 数据库 postgres        —— 管理员的常驻地
 ├── 数据库 shop            —— 业务库
 │    ├── schema public     —— 默认命名空间
 │    └── schema business   —— 自定义命名空间：把「订单域」整组收拢
 │         ├── 表 orders
 │         └── 序列 / 函数 / 视图 / 索引……都挂在 schema 里
 ├── 数据库 template1       —— 建库的默认模板
 └── 数据库 template0       —— 纯净模板

多出这层 schema 不是官僚主义：一是跨 database 不能直接 JOIN（要上 postgres_fdw 这类外部包装器），但跨 schema 只要写前缀 business.orders 就行——所以「把 MySQL 的多个小库合成 PG 一个库的多个 schema」是很常见的迁移姿势，原本的跨库查询全部变通；二是权限可以按 schema 整组授予，第十一章的最小权限模板会用到。

第二步：建一个 schema，把表放进去

CREATE SCHEMA business;
CREATE TABLE business.orders (id bigint, note text);

不带前缀的表名去哪找？由 search_path 决定：

shop=# SHOW search_path;
   search_path
-----------------
 "$user", public
(1 row)

默认值是「与我同名的 schema，再 public」，第一个 "$user" 99% 的情况下不存在，实际命中的都是 public。把刚建的 schema 排到前面：

SET search_path = business, public;
SELECT * FROM orders;              -- 等价于 business.orders

系统表所在的 pg_catalog 永远隐含在最前面，所以 pg_class、pg_settings 这类系统表名不会和你业务表打架。想临时改一次用上面的 SET，想整个会话/账号长期生效用 ALTER ROLE ... SET search_path（第十一章的授权模板里就有这一句）。

第三步：建库时把编码和排序规则一次定死

\l 里永远能看到三个库：postgres、template1、template0。PG 的 CREATE DATABASE 本质是复制一份模板库——默认复制 template1；template0 则是出厂纯净版，还能在建库时指定编码和排序规则（这是 template1 做不到的）：

CREATE DATABASE shop
  TEMPLATE template0
  ENCODING 'UTF8'
  LC_COLLATE 'C.UTF-8'
  LC_CTYPE 'C.UTF-8';

建库这一步把编码和 collation 定死，是第十六章核对清单的第一行，原因见本章坑 2。

第四步：必调的四个参数

⚠️ 本章的坑

1. 往 template1 里建的东西，以后每个新库都会带着。 它是建库的默认模板，你在里面留了张测试表，之后 CREATE DATABASE 出来的每个库都有这张表。要改模板先想清楚这一点，需要干净起点时用 TEMPLATE template0。

2. 排序规则（collation）建库之后改不了，而字符串索引的顺序完全依赖它——升个 glibc 版本导致 collation 行为变化、索引悄悄损坏的事故是有真实案例的。所以第三步那次「建库就定死」不是形式主义；已经建错了的库只能 pg_dump 出来重建。

3. 跨 database 不能 JOIN。 想当然照 MySQL 的 db1.t1 JOIN db2.t2 写会直接报错。要么用 postgres_fdw 挂外部表，要么按第一步说的：一个库、多个 schema。

4. PG 15 起 public schema 默认收回普通用户的建表权。 普通用户 CREATE TABLE 会报 permission denied for schema public。这不是 bug，是安全默认值——正确姿势是给业务单独建 schema、单独授权，第十一章给出整套模板。

5. search_path 里排前面的 schema 会遮蔽后面的同名表。 如果 business 和 public 里都有 orders，SELECT * FROM orders 只会命中排在前面的那个。生产代码里跨 schema 的表写全前缀，或把 search_path 固定成一组明确的 schema，别让它随会话漂移。

6. work_mem 的账要会算：它不是「每个连接」的预算，而是「每个排序/哈希节点」。 100 个连接、每条查询里两个 64MB 的排序节点，理论峰值就是 100 × 128MB = 12.8GB。调它之前先用 \timing on + EXPLAIN 确认瓶颈真的是排序/哈希（第八章），而不是无脑加。

四、表结构定义：类型选对，索引的锅就少背一半

场景定型：这门课的示例业务是一个小电商——users（用户）、products（商品）、orders（订单）、order_items（订单明细）。

本章分三段读，第一遍只看第一段就够了：

1. 入门四步：建出能跑的表、类型怎么选、四张表怎么连起来、jsonb 与数组怎么查——全是可直接抄的最小示例；
2. ⚠️ 本章的坑：8 个真实报错和它们的成因，等表建起来了再回头看；
3. 附：类型速查表：字节长度、Java 类型、MySQL → PG 迁移对照，写 DDL 时查，不用通读。

第一步：先建出一张能跑的表

最小的 users 表，一行一个字段：

CREATE TABLE users (
  id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,  -- 主键，数据库自己递增
  name    text NOT NULL,                                    -- 用户名，必填
  email   text NOT NULL UNIQUE,                             -- 邮箱，必填且不重复
  memo    text,                                             -- 备注，可空（第五章拿它讲 NULL）
  created_at timestamptz NOT NULL DEFAULT now()              -- 注册时间，默认当前时刻
);

插一条、查一条，确认它真的活了：

INSERT INTO users (name, email) VALUES ('小明', 'xiaoming@example.com');
SELECT id, name, email, created_at FROM users;

 id | name | email                |       created_at
----+------+----------------------+-------------------------
  1 | 小明 | xiaoming@example.com | 2026-10-16 11:20:31+08

注意 INSERT 里没有 id——GENERATED ALWAYS AS IDENTITY 就是 PG 里「自增主键」的标准写法，值由数据库生成。Java 实体对应关系是 Long id、String name、OffsetDateTime createdAt，text 一律映射成 String。

第二步：类型怎么选，先记住这六个

再加两个「进阶但很常用」：订单的扩展属性（渠道、活动信息）用 jsonb，一条记录挂多个标签用 text[] 数组。它们都是 PG 的原生能力，不需要像 MySQL 那样把多个值拼成逗号字符串。

把商品表建出来，六个类型一次用全：

CREATE TABLE products (
  id       bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title    text NOT NULL,
  price    numeric(12, 2) NOT NULL CHECK (price >= 0),   -- 金额 + 非负校验
  stock    integer NOT NULL DEFAULT 0,                   -- 库存，默认 0
  on_sale  boolean NOT NULL DEFAULT true,                -- 是否上架
  tags     text[],                                       -- 标签数组，可空
  meta     jsonb,                                        -- 扩展属性
  created_at  timestamptz NOT NULL DEFAULT now()
);

INSERT INTO products (title, price, stock, tags, meta)
VALUES ('机械键盘', 399.00, 20, ARRAY['数码', '外设'], '{"channel": "app"}');

SELECT title, price, tags, meta ->> 'channel' AS channel   -- ->> 取 jsonb 里的文本值
FROM products;

  title   | price  | tags         | channel
----------+--------+--------------+---------
 机械键盘 | 399.00 | {数码,外设} | app

第三步：把四张表连起来（外键与约束）

订单和明细是一对多，REFERENCES 就是外键，ON DELETE CASCADE 表示「父行删了，子行跟着删」：

CREATE TABLE orders (
  id       bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id  bigint NOT NULL REFERENCES users(id),          -- 外键指向 users.id
  order_no text NOT NULL,                                 -- 订单号，第八章用它讲索引
  status   text NOT NULL DEFAULT 'created',
  amount   numeric(12, 2) NOT NULL DEFAULT 0,             -- 订单金额
  paid_at  timestamptz,                                   -- 支付时间，未支付为 NULL
  meta     jsonb,
  tags     text[],
  created_at  timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE order_items (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_id   bigint NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
  product_id bigint NOT NULL REFERENCES products(id),
  price      numeric(12, 2) NOT NULL CHECK (price >= 0),
  qty        integer NOT NULL CHECK (qty > 0),
  total      numeric(12, 2) GENERATED ALWAYS AS (price * qty) STORED   -- 生成列
);

total 是 PG 12 起的生成列：由同表其他列算出、物理存储，查的时候不用每次重算 price * qty。

外键的删除动作按业务逐个选，别无脑 CASCADE：

约束是应用层校验之外的最后防线：ORM 的校验能被绕过，CHECK 不会。四张表建完，\dt 能看到它们，\d orders 能看单表结构。最后造一条订单，给下一节的 jsonb / 数组查询当数据：

INSERT INTO orders (user_id, status, meta, tags)
VALUES (1, 'created', '{"channel": "app", "tags": ["vip"]}', ARRAY['vip']);

第四步：jsonb 取值与数组查询的三个写法

入门只需要三个操作符，先记住形状：

-- 1) ->> 取文本（最常用，Java 侧拿到的就是 String）
SELECT meta ->> 'channel' AS channel FROM orders;

-- 2) @> 包含查询：meta 里含这个键值对的就命中
SELECT id FROM orders WHERE meta @> '{"channel": "app"}';

-- 3) && 数组交集：tags 里含有 'vip' 的就命中
SELECT id FROM orders WHERE tags && ARRAY['vip'];

-> 和 ->> 的区别只在于返回 jsonb 还是 text；其余操作符（#>>、?、jsonb_set()、jsonb_array_elements()）见本章末尾的速查表。

⚠️ 本章的坑

表能跑起来之后，再回头看这 8 条——它们都是上面那些「看起来没问题」的写法在真实项目里会长出来的刺。

1. serial 不是类型，是宏，新表一律换 identity。 它会建一个序列并把默认值设成 nextval()，三宗罪迟早撞上：不是 SQL 标准，迁移工具对不上；权限要单独授，把表授给应用账号后 INSERT 仍会报 permission denied for sequence users_id_seq，得再 GRANT USAGE ON SEQUENCE 一次（第十一章模板里已包含）；序列和表归属松散，删来删去留孤儿。

2. ALWAYS 意味着你手工插 id 会被拒。 这是上面那条 DDL 的副作用，报错长这样：

shop=# INSERT INTO users (id, name) VALUES (99, 'Bob');
ERROR:  cannot insert a non-DEFAULT value into column "id"
DETAIL:  Column "id" is an identity column defined as GENERATED ALWAYS.
HINT:  Use OVERRIDING SYSTEM VALUE to override.

迁移历史数据时加 OVERRIDING SYSTEM VALUE；想平时也允许手工指定，建表时就用 BY DEFAULT。

3. 时间列写成 timestamp，换时区就漂。 timestamptz 存的是绝对时刻（内部按 UTC 记账），显示随会话时区变；timestamp 存的只是「墙上读数」。实验两分钟胜过背十遍：

CREATE TABLE clock_t (ts timestamptz);
SET TIME ZONE 'Asia/Shanghai';
INSERT INTO clock_t VALUES ('2026-10-10 09:30:00');
SELECT ts FROM clock_t;          -- 2026-10-10 09:30:00+08
SET TIME ZONE 'UTC';
SELECT ts FROM clock_t;          -- 2026-10-10 01:30:00+00

同一个绝对时刻，两个会话两个读数，都对。如果列建成 timestamp，这个值就只是一串「9 点半」，谁也不知道是哪个时区的 9 点半。所以：时间列一律 timestamptz，字面量尽量带偏移（'2026-10-10 09:30:00+08'），应用连接串里写明 TimeZone=Asia/Shanghai。

4. 金额用 float8，对账时对不平。 浮点存的不是十进制字面值：

SELECT 0.1::float8 + 0.2::float8;   -- 0.30000000000000004

金额一律 numeric(p, s)，它按十进制精确存。

5. char(n) 会补空格，= 比较会骗人。 char(10) 存 'abc' 读出来是 'abc '；varchar(n) 的 n 也不带来任何性能差异，只是长度约束。要约束长度，用 text + CHECK (char_length(x) <= n) 更明白。

6. PG 不给外键自动建索引——MySQL 老手最容易栽的一条。 InnoDB 建外键时会自动在子表列上建索引，PG 什么都不做。于是删父表一行时，PG 要顺序扫整个子表检查有没有引用，行数一大就是秒级起步的锁等待（第九章的排队事故里有它的戏份）。解法很朴素：

CREATE INDEX idx_order_items_order_id ON order_items (order_id);

建完之后 \d order_items，索引区和约束区各看到一条——「外键约束」和「外键列索引」是两码事，谁也不替谁负责。

7. bigint 传到前端会被改掉末几位。 Java 侧 Long 是 64 位，JS 的 Number 只有 53 位安全整数，雪花 ID 序列化过去末几位悄悄变了。所以 ID 出参统一转字符串（Jackson 的 ToStringSerializer，或全局配 Long → String），前端全程按 string 传。RuoYi 这类用雪花算法生成主键的框架，实体里是 Long userId，建表就必须是 bigint。

8. json 和 jsonb 别选错，uuid 主键别乱用。 json 只是原样存文本，每次读都要重新解析、不能建索引；jsonb 是解析后的二进制，能建 GIN 索引能查询——99% 的场景选 jsonb。另外 uuid 做主键虽然方便（PG 13 起 gen_random_uuid() 内置，不用装 pgcrypto），但随机值会让 B-tree 索引写入分散、页分裂变多，大表主键仍推荐 bigint（第八章有账可算）。

附：类型速查表

写 DDL 时查这几张表。先说一个命名陷阱：int8 里的 8 不是 8 位，是 8 字节（byte）。PG 的 int2 / int4 / int8、float4 / float8 前缀数字一律指字节数，跟 MySQL 的 int(11)（那是显示宽度，和取值范围毫无关系）是两套语义。所以 int8、bigint 是同一个东西的两种写法：64 位长整型，对应 Java 实体里的 Long。

数值类型：

字符串、布尔与二进制：

时间、日期与其他：

jsonb 操作符全表（meta = {"channel":"app","tags":["vip","new"]}）：

实战里最值钱的是 @> 包含查询配合 GIN 索引（第八章会讲两种索引 opclass 的取舍）：

SELECT id, meta->>'channel' AS channel
FROM orders
WHERE meta @> '{"tags": ["vip"]}';

数组类型的日常是交集查询：WHERE tags && ARRAY['vip'] 表示「标签里含 vip」，配 GIN 索引同样走索引。

MySQL → PG 类型迁移对照：

从 MySQL 迁表时别一一对着名字猜，尤其注意 PG 没有 unsigned、没有 1 字节整型、datetime 不带时区这三件事：

五、单表查询：WHERE、排序、分页与几件 PG 独门兵器

单表查询是日常 80% 的 SQL。本章先给最常用的形状（第一步到第三步），把最容易出错的几件事——NULL 比较、NULL 排序、深分页、count 的两种数法——集中放到章末的坑区。

第一步：五个关键词，一条语句

SELECT status, count(*) AS cnt
FROM orders
WHERE created_at >= '2026-10-01'
GROUP BY status
ORDER BY cnt DESC
LIMIT 10;

WHERE 过滤行、GROUP BY 分组、HAVING 过滤组、ORDER BY 排序、LIMIT 取前 N 条——五个关键词按这个顺序写，执行顺序是 WHERE → GROUP BY → HAVING → ORDER BY → LIMIT。判断空值用 IS NULL / IS NOT NULL，不要用 = NULL（原因见坑 1）。

第二步：分页的两种写法

-- 写法一：按页码跳页
SELECT id FROM orders ORDER BY id LIMIT 20 OFFSET 100;

-- 写法二：键集分页（keyset / seek），上一页最后一条 id 作为起点
SELECT id FROM orders WHERE id > 500000 ORDER BY id LIMIT 20;

第一种能「跳转到第 N 页」，是给用户翻页用的；第二种代价恒定，适合信息流式的「下一页」。为什么深页要换写法，本章坑 3 有实测数字。

第三步：三件 PG 顺手兵器

每组第一条（「每个用户最近的一笔订单」），MySQL 得上窗口函数，PG 一句话：

SELECT DISTINCT ON (user_id) user_id, id, created_at
FROM orders
ORDER BY user_id, created_at DESC;

条件聚合，比 CASE WHEN 干净：

SELECT user_id,
       count(*)                                     AS total_cnt,
       count(*) FILTER (WHERE status = 'paid')      AS paid_cnt,
       sum(amount) FILTER (WHERE status = 'paid')   AS paid_amt
FROM orders
GROUP BY user_id
HAVING count(*) FILTER (WHERE status = 'paid') >= 3;

改完顺手把结果带回来（RETURNING），以及幂等写入（ON CONFLICT）：

UPDATE orders SET status = 'paid', paid_at = now()
WHERE id = 42
RETURNING id, status, paid_at;

INSERT INTO stats (user_id, day, pv) VALUES (42, '2026-10-10', 1)
ON CONFLICT (user_id, day)
DO UPDATE SET pv = stats.pv + 1
RETURNING pv;

DO UPDATE 换成 DO NOTHING 就是「有就跳过」。统计累加这类需求，一条 ON CONFLICT ... DO UPDATE 抵掉应用层整套「先查后插」的代码。

⚠️ 本章的坑

1. = NULL 永远选不出东西。 SQL 里 NULL 不是值，是「未知」，任何比较碰上它结果都是「未知」（不成立）：

SELECT count(*) FROM users WHERE memo = NULL;   -- 0，一个都选不出来
SELECT count(*) FROM users WHERE memo IS NULL;  -- 417，正确写法

由此派生两件常用兵器：IS DISTINCT FROM 把 NULL 当成一个具体值来比较（a IS DISTINCT FROM b 两侧都是 NULL 时为 false，正好补上 <> 的洞）；coalesce(x, 0) 给 NULL 兜底。除零保护留到第六章和 nullif 一起讲。

2. 排序时 NULL 的位置和你想的相反。 PG 的默认规则是：升序时 NULL 沉底，降序时 NULL 置顶。于是「按支付时间倒序取最近 5 单」，第一行居然是个没支付的空时间：

SELECT id, paid_at FROM orders ORDER BY paid_at DESC LIMIT 5;         -- 第一行 paid_at 是空的
SELECT id, paid_at FROM orders ORDER BY paid_at DESC NULLS LAST LIMIT 5;  -- 老老实实沉底

3. OFFSET 是给用户翻页用的，不是给深页用的。

SELECT id FROM orders ORDER BY id LIMIT 20 OFFSET 500000;  -- 要先算过前 50 万行再扔掉

它是 O(offset)：翻到越深的页越慢，第 25000 页就是全表扫的价钱。换成第二步的键集分页，前提是排序列有索引且唯一，代价恒定；代价是不支持「跳转到第 N 页」。示例 05 里 \timing on 的两个数字对比，一眼就能看到量级差异。

4. DISTINCT ON 只取一条，且 ORDER BY 最左边必须同列。 规则说人话：DISTINCT ON (列) 指定「每组」，ORDER BY 的最左边必须是同样的列，然后每组按剩余排序条件取第一条。要每组前三条就得回窗口函数（第六章）。它是 PG 对 SQL 标准的扩展，别的库没有。

5. count(*) 和 count(列) 数的不是一回事。 前者数所有行，后者只数该列非 NULL 的行——「转化率」类指标两个 count 相除时务必想清楚用哪个。同理，FILTER 虽然是 PG 扩展语法（等价于 count(CASE WHEN status='paid' THEN 1 END)，但少敲一半字符），换库时要记得它不通用。

6. HAVING 和 WHERE 的先后位置别放反。 WHERE 过滤聚合前的行，HAVING 过滤聚合后的组。把聚合条件写进 WHERE 会直接报 aggregate functions are not allowed in WHERE。

7. ON CONFLICT 的推断列必须有唯一约束。 ON CONFLICT (user_id, day) 成立的前提是 (user_id, day) 上真有唯一索引，否则报 there is no unique or exclusion constraint matching the ON CONFLICT specification。

8. 「先查后插」在并发下会丢更新。 应用层写「查一下有没有，没有就插」，两个线程同时查到「没有」就出事了。ON CONFLICT ... DO UPDATE 把这件事交给数据库原子完成；同一类问题在事务层面还有第三种解法，第九章细算这笔账。

六、常用函数：字符串、时间、JSONB 与窗口

函数不背清单。本章按「改字符串、算时间、掏 JSON、开窗」四组各给一个最小用法，速查表和几个容易翻车的细节放到章末。

第一步：字符串——拼接、兜底、切分

SELECT concat('a', NULL);                  -- 'a'，concat 把 NULL 当空串
SELECT coalesce(memo, name, '（没填）');     -- 从左到右取第一个非 NULL
SELECT split_part(email, '@', 2);           -- 取域名
SELECT format('%s-%s', 'order', id);        -- 拼展示文案，%s 自动转文本
SELECT substring(email FROM 1 FOR 3);       -- 取子串（FROM 起点 FOR 长度）
SELECT btrim(name, ' ');                    -- 去掉两侧空格，导入脏数据常用

批量拼文案优先用 concat / format，别用 ||——|| 遇到 NULL 会把整个结果传染成 NULL（详见坑 1）。

第二步：时间——截断、加减、取部位

SELECT date_trunc('month', created_at) AS mon, count(*)
FROM orders
GROUP BY mon;                              -- 月报分组键

SELECT created_at + interval '30 days' AS deadline,   -- 加减
       extract(epoch FROM created_at)    AS unix_sec,  -- 转 Unix 秒
       age(now(), created_at)            AS how_long   -- 两个时刻差，输出「1 year 2 mons」这种人话
FROM orders;

更多写法（to_char 格式化、AT TIME ZONE 换时区、now() 三兄弟的区别）见章末速查表。

第三步：从 jsonb 里掏数据

SELECT meta->>'channel' AS channel, count(*)   -- ->> 取文本
FROM orders
GROUP BY channel;

要展开数组逐行处理（比如统计订单里每个标签的出现次数）：

SELECT tag, count(*)
FROM orders,
     jsonb_array_elements(meta->'tags') AS tag
GROUP BY tag;

FROM 表, jsonb_array_elements(...) AS 别名 这个写法等价于 LATERAL（第七章），把每个 jsonb 数组元素炸成一行。

第四步：窗口函数——不动行数地「看邻居」

GROUP BY 会把 N 行折叠成 1 行，窗口函数是每一行都保留、额外加一列聚合视野。两个最高频场景：

-- 每个用户最近 3 单（DISTINCT ON 只能取第一条，这里要前三条）
SELECT * FROM (
  SELECT o.*,
         row_number() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
  FROM orders o
) t
WHERE rn <= 3;

-- 环比：拿上一行的值；累计：组起点到当前行
SELECT user_id, created_at, amount,
       lag(amount, 1) OVER (PARTITION BY user_id ORDER BY created_at) AS prev_amt,
       sum(amount) OVER (PARTITION BY user_id ORDER BY created_at
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_amt
FROM orders;

ROWS BETWEEN ... 子句叫窗口帧，不写默认是「组起点到当前行」，累计值正是靠它。row_number() / rank() / dense_rank() 三个序号函数的区别见章末速查表。

⚠️ 本章的坑

1. || 遇到 NULL，整个结果变 NULL。 「无中生有」是 SQL 的规矩：

SELECT 'a' || NULL;            -- NULL
SELECT concat('a', NULL);      -- 'a'，concat 把 NULL 当空串

批量拼展示文案时，要么先 coalesce，要么直接用 concat / format。

2. 除法不保护就是报错。 nullif(a, b) 在 a=b 时返回 NULL，否则返回 a，它最大的用场是除零保护——第四章埋的账在这里还：

SELECT 100 / 0;                          -- ERROR: division by zero
SELECT 100 / nullif(qty, 0);             -- qty 为 0 时得到 NULL，语句不炸
SELECT coalesce(100 / nullif(qty, 0), 0);-- 再兜个底，空值按 0 算

3. 三个长度函数对中文的结果不一样。 length('中文') 是 2（字符），octet_length('中文') 是 6（UTF-8 字节）——限长、算存储时想清楚自己要的是哪个。

4. 窗口函数不能出现在 WHERE 里。 必须包一层子查询再过滤，直接写 WHERE rn = 1 会收到 ERROR: window functions are not allowed in WHERE——PG 没有 DuckDB/Snowflake 那种 QUALIFY 短路，子查询包装就是标准答案。

5. now() 不等于「此刻」。 函数三兄弟：now() = transaction_timestamp()（事务内恒定）、statement_timestamp()（每条语句刷新）、clock_timestamp()（真实钟，同一行里两次调用都不同）。做「同一事务内 created 和 updated 相同」要 now()，做监控计时才用 clock_timestamp()。

6. jsonb_set() 的第三个参数必须是 jsonb 字面量。 jsonb_set(meta, '{vip}', '"gold"'::jsonb) 里的引号不是装饰——少写 ::jsonb 或写成裸字符串，会得到一个带多余引号的值或直接报类型错。

7. 包含查询 @> 不建索引就是全表扫。 第四章提过，jsonb 的 @> 要走 GIN 索引才划算（第八章讲两种 opclass 的取舍），否则每次包含查询都是顺序扫整列 jsonb。

附：时间与序号函数速查

七、多表关系：JOIN 家族、EXISTS 与外键的连锁行为

模型定型：users 1:N orders 1:N order_items N:1 products；商品与标签多对多走中间表 product_tags。

第一步：JOIN 家族

最常用的就是前两种，一条语句记住形状：

SELECT u.name, count(o.id) AS order_cnt
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.name;

第二步：EXISTS 与 LATERAL

「买了东西的用户」两种写法，优化器基本会改写成同一个计划，选哪个看可读性：

SELECT name FROM users u WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id
);

「每组取 N 条」还有个比窗口函数更直接的 LATERAL：右边的子查询可以引用左边表的列，每行参数化执行一次：

SELECT u.name, o.id, o.amount
FROM users u
LEFT JOIN LATERAL (
  SELECT * FROM orders o
  WHERE o.user_id = u.id
  ORDER BY created_at DESC LIMIT 3
) o ON true;

第三步：外键动作的现场

第四章给过 CASCADE / SET NULL / RESTRICT 的选型表，这里看现场。给 orders.user_id 配 ON DELETE CASCADE：

shop=# DELETE FROM users WHERE id = 7;
DELETE 1
shop=# SELECT count(*) FROM orders WHERE user_id = 7;
 count
-------
     0
(1 row)      -- 订单整整齐齐跟着没了，删库跑门槛都没有

如果当初配的是 SET NULL，同一组操作后 orders 里 user_id=7 的行还在、外键变成 NULL——「关联断了但数据还在」。两个动作都对，取决于业务上订单是「用户的附属品」还是「独立凭证」。

⚠️ 本章的坑

1. LEFT JOIN 的过滤条件放错地方，语义直接退化。 两个查询只差一个从句：

-- 正确：条件写在 ON 里，LEFT 语义完整保留——所有用户都出现，paid 单才有值
SELECT u.name, o.id
FROM users u
LEFT JOIN orders o ON o.user_id = u.id AND o.status = 'paid';

-- 翻车：条件写在 WHERE 里——join 完补出来的 NULL 行被过滤掉，实际退化成 INNER JOIN
SELECT u.name, o.id
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.status = 'paid';

规则一句话：对右表（被 JOIN 表）的过滤放 ON，对左表主数据的过滤放 WHERE。示例 07 里有这两个查询并排的输出对比。

2. NOT IN 遇上子查询里的 NULL 会全军覆没。

-- product_tags.product_id 里只要有一行 NULL，这条永远返回 0 行
SELECT name FROM products
WHERE id NOT IN (SELECT product_id FROM product_tags);

-- NOT EXISTS 不受影响，语义正确
SELECT name FROM products p
WHERE NOT EXISTS (SELECT 1 FROM product_tags t WHERE t.product_id = p.id);

原因是 NOT IN 展开成「与所有元素都不等」，而任何值与 NULL 比较都是「未知」，整个条件永远不成立。否定语义一律写 NOT EXISTS，这条规则能免掉一类极难排查的线上问题（数据一变 NULL，查询悄悄变空）。

3. PG 不给外键自动建索引，删除时会现场爆发。 第四章埋的雷在这里显形：EXPLAIN ANALYZE DELETE FROM users WHERE id = 7 的输出里有一行 Trigger for constraint orders_user_id_fkey: time=143.2 ms——父表按主键索引秒删，但 PG 检查引用约束时要顺序扫一遍 orders 全表。给 orders(user_id) 建上索引再删，同一个 DELETE 的触发器耗时掉到 0.1ms 量级。建外键的下一行就建索引，让这两行在迁移脚本里永远是邻居。

4. LATERAL 子查询后面必须跟 ON 条件，没有条件就写 ON true，直接省掉会报语法错误。它比窗口函数直观，但每行都要执行一次子查询，驱动集很大时先看执行计划（第八章）。

八、索引：B-tree 走得快，也要养得起

本章先给「建一个索引并确认它生效」这条最小路径（第一步到第三步），把读执行计划的方法、六种索引类型和索引的成本账放到章末——第一遍只需要会前三步。

第一步：建一个索引，确认它生效

CREATE INDEX idx_orders_order_no ON orders (order_no);

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE order_no = 'SO-2026-000123';

计划里出现 Index Scan using idx_orders_order_no 就说明走上了；还是 Seq Scan 就是没走上（列上有隐式转换、条件写成函数包裹、或者命中行太多优化器主动放弃）。EXPLAIN 必须加 ANALYZE 才会真的执行并给出 actual 数字，详见坑 1。

第二步：复合索引的列序

CREATE INDEX idx_orders_user_created ON orders (user_id, created_at);

最左前缀规则决定它能服务谁：WHERE user_id = 42 能用（最左列独立命中）；WHERE user_id = 42 AND created_at > now() - interval '7 days' 能用得很好；WHERE created_at > ... 单独用不能——最左列缺席，索引整体作废。通用配方：等值条件列在前，范围条件列在后；再把 ORDER BY 的列对齐进去，WHERE user_id = 42 ORDER BY created_at DESC LIMIT 5 可以完全在索引里倒着走五步就停，计划里连 Sort 节点都没有（示例 08 有这个计划的对照）。

第三步：三种「救场」索引

覆盖索引（INCLUDE）——查询需要的列全在索引里，就不用回表。PG 11 起 CREATE INDEX ... INCLUDE (extra_col) 把额外列挂进索引叶子页（不参与排序）：

CREATE INDEX idx_orders_user_cover ON orders (user_id) INCLUDE (status, amount);

部分索引——只索引满足条件的行，待办列表场景极爽，百万订单里 pending 状态的就几千行：

CREATE INDEX idx_orders_pending ON orders (created_at)
WHERE status = 'pending';

索引体积是全量索引的零头，写入时多数行根本不进索引，代价几乎为零。

表达式索引——第四章埋过的雷在这里拆。WHERE lower(email) = 'ada@example.com' 用普通 btree 索引是命中不了的，建表达式索引：

CREATE INDEX idx_users_email_lc ON users (lower(email));

⚠️ 本章的坑

1. 不加 ANALYZE 的 EXPLAIN 是「打算」，不是「结果」。 不加只显示优化器计划怎么执行，加上才真的执行并显示 actual 行数与耗时。拿计划里的估算行数去汇报，等于没跑。

2. 估算行数和实际行数差一个数量级，先怀疑统计信息。 读输出抓三样：rows=（估算行数）与 actual 行数差多少——差一个数量级说明统计信息过期，跑一把 ANALYZE orders 让 autovacuum 重新采样；Buffers: shared read=（从磁盘读了多少页）——hit 是缓存命中，read 是真实 IO；Execution Time——唯一能写进汇报的数字。

3. 表达式索引是「文本级一致」才匹配。 建索引用 lower(email)，查询就必须写一模一样的 lower(email)，写成 lower(email, 'C') 都会对不上——这也是全文搜索第十三章要用「同一表达式」的原因。另一种解法更省事：应用侧保证存入时已小写，查询写 WHERE email = lower('Ada@Example.com')。

4. %关键词% 这种中缀 LIKE 用不上 btree。 解法是 pg_trgm 扩展 + GIN 索引：

CREATE EXTENSION pg_trgm;
CREATE INDEX idx_products_name_trgm ON products USING gin (name gin_trgm_ops);

它把字符串拆成三元组做倒排，ILIKE '%键盘%' 也能走索引——很多「搜索框」需求到这一步就够了，撑不到需要第十三章全文搜索的程度。

5. 索引不是免费的：写入要付利息，膨胀要还本金。 每多一个索引，每次 INSERT/UPDATE 都多一次维护写入；UPDATE 只要碰了任何一个被索引的列，新版本就要在所有相关索引里各插一份（旧版本变死元组，第九章细说）。所以「先建 20 个索引再倒数据」不如反过来：大批量导入前删掉非约束索引，导完 ANALYZE 再重建，快一个数量级很正常。

6. 表和索引都会膨胀，要会看数字。 PG 里有 pgstattuple 扩展可以看表和索引的死元组比例（膨胀率），阈值到 20%~30% 就值得处理：常规 VACUUM 只回收不还磁盘，VACUUM FULL 还磁盘但拿全表排他锁，线上用 pg_repack 平替。这一账记进第十六章核对清单。

7. Index Only Scan 吃不吃得到，取决于 vacuum。 覆盖索引配合「visibility map 足够干净」才会出现 Index Only Scan，连堆表都不碰；长期不 vacuum 的表会退化成带 heap fetch 的普通扫描（第九章 MVCC 讲为什么）。

附：EXPLAIN 节点与索引类型速查

九、事务与锁：MVCC、隔离级别与排队事故

PG 的并发控制全建立在 MVCC（多版本并发控制）上：写不阻塞读，读不阻塞写。第一遍只需要掌握第一步那三种写法（不丢更新的套路），隔离级别的差异、锁排队和死锁都收在章末。

第一步：事务的三种安全写法

「SELECT 检查库存 → 应用判断 → UPDATE 扣减」这套流程在并发下必然丢更新：两个事务同时读到库存 1、都判断通过、都写成功，库存变 -1。三种修法按保守到乐观排：

-- ① 悲观：先把行锁住再查（行内排队）
BEGIN;
SELECT stock FROM products WHERE id = 1 FOR UPDATE;  -- 锁住这行
UPDATE products SET stock = stock - 1 WHERE id = 1;  -- 安全
COMMIT;

-- ② 乐观：把判断压进 UPDATE 的 WHERE，看影响行数
UPDATE products SET stock = stock - 1
WHERE id = 1 AND stock >= 1;
-- 应用侧检查 affected rows：0 就说明被抢了，走「已售罄」分支

-- ③ 原子：第五章的 upsert，配「不存在则插入」的幂等语义
INSERT INTO stock_log (product_id, day, sold) VALUES (1, '2026-10-10', 1)
ON CONFLICT (product_id, day) DO UPDATE SET sold = stock_log.sold + 1;

日常优先 ②——它不持锁等待、天然并发安全，业务分支也清晰；热点行抢购场景才需要 ① 或引入队列削峰。

第二步：知道「读不阻塞写」就够了

每行都有几个系统隐藏列：xmin（创建它的事务号）、xmax（删除/失效它的事务号）。UPDATE 也不存在原地修改——它是「插一行新版本 + 给旧版本打墓碑」：

SELECT xmin, xmax, status FROM orders WHERE id = 42;   --  258312 |      0 | pending
UPDATE orders SET status = 'paid' WHERE id = 42;
SELECT xmin, xmax, status FROM orders WHERE id = 42;   --  258421 |      0 | paid

第二次查询看到的 xmin 已经是新事务号——那是一个新元组。旧版本（xmin=258312）被标上 xmax=258421，成为死元组；但只要还有别的事务的快照能看到它，它就必须留着。这一条推出本章所有的怪现象，也推出第九章坑 1 那条雪崩。

第三步：多 worker 抢任务，用 SKIP LOCKED

SELECT ... FOR UPDATE 拿行级排他锁，两个修饰词改变冲突时的行为：NOWAIT 拿不到立即报错；SKIP LOCKED 拿不到就跳过去看下一行。后者是「多 worker 抢任务」的标准解：

UPDATE jobs SET status = 'running', worker = 'w1'
WHERE id = (
  SELECT id FROM jobs
  WHERE status = 'pending'
  ORDER BY id
  LIMIT 1
  FOR UPDATE SKIP LOCKED
)
RETURNING id, payload;

两个 worker 同时执行：w1 锁住 101 号，w2 在子查询里发现 101 被锁、自动跳到 102——互不等待、永不撞车。拿消息队列（Kafka/Redis）之前，很多轻量任务场景到 SKIP LOCKED 就够用了，又少养一个组件。

⚠️ 本章的坑

1. 一个「idle in transaction」能让全库膨胀。 承第二步的推论：只要还有事务的快照能看到旧版本，死元组就不能回收。所以一个挂着不提交的长事务，会让 vacuum 无法回收全库的死元组，表和索引一路膨胀，全库变慢——这是 PG 独有的雪崩形态，第十六章清单里「监控 idle in transaction」专门冲它去的。

2. 默认隔离级别 READ COMMITTED 下，事务内两次 SELECT 结果可以不同。

-- 会话 A                                    -- 会话 B
BEGIN;
SELECT count(*) FROM orders;  -- 1000
                                             BEGIN;
                                             UPDATE orders SET status='paid' WHERE id<5;
                                             COMMIT;   -- B 已提交
SELECT count(*) FROM orders;  -- 还是 1000？不，这次是 1005
COMMIT;

A 的两条 SELECT 之间没有任何写入，但第二次看到了 B 已提交的数据——因为 RC 下每条语句都换新快照。从 MySQL（默认 RR）迁过来、依赖「事务内复读」的代码要改写。

3. 升到 RR / SERIALIZABLE，就要写重试循环。 InnoDB 的 RR 靠间隙锁挡幻读，PG 的 RR 靠快照——读不加锁，写冲突靠报错重试：并发改同一行时冲突方会报 could not serialize access due to concurrent update，SERIALIZABLE 还会预测危险结构并报 serial failure。应用必须捕获 40001 SQLSTATE 重来一次，否则就是偶发 500。

4. ALTER TABLE 能把全站堵死。 行锁之上还有表级锁，八级里只需要记住两端：普通 SELECT/UPDATE 拿的都是弱锁，互不冲突；而 ALTER TABLE 要拿 ACCESS EXCLUSIVE——与一切读写互斥。事故链通常是这么发生的：

14:00:03  会话 A：一个长查询/未提交事务还挂着（拿住弱锁）
14:00:05  DBA：ALTER TABLE orders ADD COLUMN memo text;  → 排队等 A
14:00:06  之后的所有 SELECT ... FROM orders → 全部排在 DDL 后面（FIFO 队列）
14:00:30  全站接口超时，报警炸了

锁队列是先进先出的，DDL 一旦排进去，后面所有请求不管读写都得等它。防御两件套：会话级先 SET lock_timeout = '3s'; 再执行 DDL，拿不到锁立刻退出（而不是堵门）；大表建索引一律 CREATE INDEX CONCURRENTLY——不挡写入（代价：慢一些，失败会留下 INVALID 索引需要 drop 重来）。

5. 死锁不靠调参解决，靠统一加锁顺序。 A 锁 42 再要 43，B 锁 43 再要 42，互相等到天荒地老——PG 的死锁检测器（默认 deadlock_timeout 1 秒）会杀掉其一：

ERROR:  deadlock detected
DETAIL:  Process 2847 waits for ShareLock on transaction 258330;
         blocked by transaction 258325.
         Process 2831 waits for ShareLock on transaction 258325;
         blocked by transaction 258330.
HINT:  ... server log 有两个进程正在执行的 SQL

解法不是调参，是改业务加锁顺序：所有代码路径都按固定顺序（比如按主键升序）加锁，环路就消失了。

6. 外键列没索引，删除会放大成锁等待。 第七章那条「PG 不给外键自动建索引」的坑，在事务里更致命：删父表一行要顺序扫子表检查引用，扫描期间两边都持锁，直接变成排队事故。

附：隔离级别速查

十、视图与 CTE：把长查询变成复用单元

第一步：视图——给长查询起个名字

CREATE VIEW v_paid_orders AS
SELECT id, user_id, amount, paid_at
FROM orders
WHERE status = 'paid';

视图的两个常规用途：给前端/BI 一个稳定的「表样子」，和给低权限账号一个只露部分列的口子（配合 GRANT，第十一章）。PG 的特色是简单视图自动可更新——只要视图是单表直出（没有 GROUP BY/聚合/DISTINCT/窗口/集合操作），对它的 INSERT/UPDATE/DELETE 会自动改到原表上：

UPDATE v_paid_orders SET amount = 99.00 WHERE id = 42;   -- 直接生效

再加 WITH CHECK OPTION，连「改完就掉出视图可见范围」的行都会被拒绝，防止通过视图越权改数据。

第二步：物化视图——昂贵报表落盘

普通视图每次查询都现算；物化视图把结果落盘：

CREATE MATERIALIZED VIEW mv_daily_sales AS
SELECT date_trunc('day', paid_at) AS day, sum(amount) AS amt
FROM orders WHERE status = 'paid' GROUP BY 1;

REFRESH MATERIALIZED VIEW mv_daily_sales;                 -- 全量刷，期间查询被阻塞
CREATE UNIQUE INDEX ON mv_daily_sales (day);              -- 前置条件
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_sales;    -- 不阻塞读

定个任务凌晨全量刷，白天报表直接查物化视图，一笔流水一笔账。CONCURRENTLY 的两个前提见坑 2。

第三步：CTE——拆步骤与遍历树

「起名字、拆步骤」，多个 CTE 依次引用：

WITH recent AS (
  SELECT * FROM orders WHERE created_at > now() - interval '7 days'
),
big AS (
  SELECT * FROM recent WHERE amount > 1000
)
SELECT count(*) FROM big;

树形结构（部门、评论楼层、物料清单）的遍历神器是递归 CTE：

WITH RECURSIVE sub AS (
  -- 种子：起点
  SELECT id, name, parent_id, 1 AS depth FROM depts WHERE id = 2
  UNION ALL
  -- 递归：从上一轮结果往下走一层
  SELECT d.id, d.name, d.parent_id, s.depth + 1
  FROM depts d JOIN sub s ON d.parent_id = s.id
)
SELECT * FROM sub ORDER BY depth;

 id |  name   | parent_id | depth
----+---------+-----------+-------
  2 | 技术部  |         1 |     1
  5 | 后端组  |         2 |     2
  6 | 前端组  |         2 |     2
  9 | 平台小队 |         5 |     3
(4 rows)

还有一个冷门但极顺手的用法：数据修改 CTE，一条语句完成「删 A 表搬进 B 表」，还天然原子：

WITH moved AS (
  DELETE FROM jobs_done WHERE created_at < now() - interval '30 days'
  RETURNING *
)
INSERT INTO jobs_archive SELECT * FROM moved;

⚠️ 本章的坑

1. 视图不物化，就是每次都现算。 普通视图只是一段被起名的 SQL，SELECT * FROM v_paid_orders 展开后该扫多少行还是多少行；报表慢的时候别怪视图，要么加索引，要么按第二步物化。

2. REFRESH MATERIALIZED VIEW 全量刷期间查询被阻塞，想不阻塞读必须用 CONCURRENTLY，而它的前提是物化视图上有一个唯一索引（上面示例里那行 CREATE UNIQUE INDEX 就是前置条件）。另外 CONCURRENTLY 只更新变化的行，代价是更依赖 diff 键，唯一索引选错列会刷得很慢。

3. 「CTE 会慢」是老版本的结论。 PG 12 之前 CTE 是优化围栏（外面怎么查都不下推进去），PG 12 起默认内联优化，想强制物化要写 AS MATERIALIZED。所以看资料要看版本：新集群里把子查询改写成 CTE 通常只是可读性收益，不背性能锅。

4. 递归 CTE 忘写终止条件会无限循环。 UNION 会对结果去重、还能兜住意外死循环；UNION ALL 不去重更快，但忘写递归终止条件（JOIN 不再产新行）就会无限循环——调试期先加 LIMIT 100 再跑，是保命习惯。数据里存在环（A 的父是 B、B 的父是 A）时同样会转不出来，PG 14 起可用 CYCLE ... SET 子句标记环路行。

5. 数据修改 CTE 的「原子」是语句级的。 上面那段「删 + 搬」在同一条语句里，但 CTE 内的可见性遵循所在事务的隔离级别：READ COMMITTED 下，moved 里看不到并发刚提交的其它行变更，别把它当成「读己之写」的万能方案。

十一、用户和角色：权限落到位

本章第一步讲权限的标准落法——授给组、人挂组；第二步把它用在应用账号上；第三步是多租户的 RLS。认证方式表和权限相关的坑放在章末。

第一步：权限给组，人挂组

PG 里只有一种对象叫 role：「用户」就是带 LOGIN 属性能连库的 role，「用户组」就是不带 LOGIN 的 role。官方文档的说法是「一个 role 可以当用户、也可以当用户组，取决于你怎么配它」，而主流做法就是把权限授给组，再把需要的人加进组——而不是逐个用户去 GRANT：

-- 1) 建一个「只读组」：它自己连不了库，只是权限容器
CREATE ROLE read_only NOLOGIN;
GRANT USAGE ON SCHEMA business TO read_only;
GRANT SELECT ON ALL TABLES IN SCHEMA business TO read_only;
ALTER DEFAULT PRIVILEGES IN SCHEMA business GRANT SELECT ON TABLES TO read_only;

-- 2) 建用户并挂进组
CREATE ROLE zhangsan LOGIN PASSWORD '...';
GRANT read_only TO zhangsan;

-- 3) 以后新同事也只看数据？一行就够
CREATE ROLE lisi LOGIN PASSWORD '...';
GRANT read_only TO lisi;

这样管的好处很实在：权限的定义只有一份（在组上），进人出人只是成员关系的变化；哪天要收权，REVOKE ... FROM read_only 一次生效，不用去回忆「当初到底给哪几个人单独授过」；审计时 \du 或 information_schema.role_table_grants 一眼能看清「谁能看什么」。CREATE USER 只是 CREATE ROLE ... LOGIN 的别名，见到别慌。

成员默认会自动继承组的权限（role 属性 INHERIT 是默认值），所以 zhangsan 挂进 read_only 之后直接 SELECT 就能用，不需要 SET ROLE。反过来说，如果建用户时写了 NOINHERIT，成员关系还在、权限却拿不到，得显式 SET ROLE read_only; 才生效——见本章坑 3。

第二步：应用账号的最小权限模板

应用账号不该是 owner、更不该是超级用户，照抄这套（它正是第一步那套思路用在服务账号上：应用账号只拿增删改查，建表归迁移账号）：

-- 1. 收口：public schema 不再放业务表
CREATE SCHEMA business AUTHORIZATION migrator;
REVOKE CREATE ON SCHEMA public FROM PUBLIC;

-- 2. 应用账号：只用不建
CREATE ROLE app LOGIN PASSWORD '...';
ALTER ROLE app SET search_path = business, public;         -- 第三章说过：账号级固定查找路径，免得到处写前缀
GRANT USAGE ON SCHEMA business TO app;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA business TO app;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA business TO app;   -- 第四章说过：identity/serial 的序列必须授，否则 INSERT 报错
ALTER DEFAULT PRIVILEGES IN SCHEMA business
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app;   -- 开发明天建的新表自动生效
ALTER DEFAULT PRIVILEGES IN SCHEMA business
  GRANT USAGE ON SEQUENCES TO app;

ALTER DEFAULT PRIVILEGES 解决的是「今天授了 ALL TABLES，明天新建的表又没权限」的补漏问题——它登记的是「以后谁建的表自动授给谁」。

服务账号要不要也套一层组？两种都算主流：讲究一点的做法是再建一个 rw_app 组拿权限、app 只做 LOGIN 并挂进组，好处是将来换连接账号（改密码、换应用）不用重授一遍权；小团队直接授给 app 也很常见。选定一种写进规范，别一半走组、一半直接授。

第三步：多租户再加一道 RLS

「每个租户只能看见自己的行」如果只靠应用层 WHERE，一个忘写就跨租户泄漏。PG 的 RLS 把这条规则焊在表上：

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON orders
  USING (tenant_id = current_setting('app.tenant_id')::bigint)
  WITH CHECK (tenant_id = current_setting('app.tenant_id')::bigint);

应用连接后执行 SET app.tenant_id = '42';，之后这张表上所有查询自动带上租户过滤（USING），所有写入自动校验租户归属（WITH CHECK）。

⚠️ 本章的坑

1. 表 owner 和超级用户默认绕过 RLS。 开了 ENABLE ROW LEVEL SECURITY 却用 owner 账号连，策略一条都不生效——测试时「明明配了策略怎么还能看见别人的数据」多半是这个。想连自己也拦，要 ALTER TABLE orders FORCE ROW LEVEL SECURITY。

2. 连接池 + RLS = 串数据风险。 SET app.tenant_id 的作用域是会话级，连接归还进池子时值还在。下个租户复用这条连接，就看到上一个租户的数据。必须归还前 RESET app.tenant_id，或者用事务级的 SET LOCAL（事务结束自动失效）。

3. 组权限「给了却没生效」，先看成员是不是 NOINHERIT。 权限授给了组、人也挂进了组，查询仍报 permission denied for table orders——先用 \du 看那个用户的属性列。成员角色默认带 INHERIT，会自动继承组权限；但如果建号时写了 NOINHERIT，成员关系只等于「有资格切换」，必须显式 SET ROLE read_only; 才拿得到权限。新版本还能在单次授予上单独控制：GRANT read_only TO zhangsan WITH INHERIT FALSE;。团队里规范只选一个：要么一路用默认的 INHERIT，要么写明「必须 SET ROLE」，两种混用最难排查。顺带一条：组角色是 NOLOGIN 的，拿 read_only 直接连库必然失败，那不是权限没配好。

4. GRANT ... ON ALL TABLES 只管当下。 它不会覆盖以后新建的表，忘了 ALTER DEFAULT PRIVILEGES 就会出现「昨天还好好的，今天上线新表应用就报 permission denied for table ...」。另外它登记的是「谁建的表」，用 A 账号建的表、给 B 账号登记过默认权限，也不会自动生效。

5. 表 owner 天生全权，GRANT 收不走。 owner 随时能 DROP 自己的表，任何授权都限制不了它。所以生产环境里「迁移专用账号」当 owner、应用账号只拿增删改查，两层分开。

6. trust 出现在公网段等于开源。 认证方式一行写错，等于没密码。trust 只允许出现在本机测试；对外一律 scram-sha-256。

7. 改完 pg_hba.conf 不重启也要 reload。 SELECT pg_reload_conf(); 就够（连接数、内存类参数才要重启）。忘了 reload 会白排查半小时。

8. 老 md5 密码要重设一次才升级。 PG 14 起默认加密方式是 SCRAM，但库里存量的 md5 密码不会自动变，得用 \password 重设一次。两端不一致时报的是 password authentication failed 或 server does not support SCRAM，看不出根因。

9. 排查「怎么连都失败」先看 pg_hba.conf。 规则从上往下第一条命中即生效，尤其 docker exec 走的本机 socket 默认是 trust，而 TCP 进来要密码，两条路行为不同属正常（第二章坑 7 的另一面）。

附：角色与权限排查速查

「到底谁有什么权限」这类问题，靠这几条命令一次看清：

附：pg_hba 认证方式速查

连接认证由数据目录里的 pg_hba.conf 决定，五行一列（类型 数据库 用户 地址 认证方式），从上往下第一条命中即生效：

十二、备份与恢复：pg_dump、物理备份与 PITR

先立总原则：没恢复过的备份不算备份。本章的每个命令，都值得在实验容器里跑一遍。第一遍只需要会第一步的逻辑备份与恢复。

第一步：一次完整的逻辑备份与恢复

pg_dump -U postgres -Fc -f shop-20261010.dump shop    # -Fc 自定义压缩格式
ls -lh shop-20261010.dump                              # 214M
pg_restore -l shop-20261010.dump | head -12            # 先看目录再动手
pg_restore -j 4 --clean --if-exists -d shop shop-20261010.dump

-Fc 自定义格式支持并行恢复（-j 4）、选择性恢复（--table=orders 只捞一张表）、压缩，比裸 SQL 灵活得多。数据搬家用 \copy（客户端侧 CSV，权限跟随当前用户）：

\copy products TO 'products.csv' WITH (FORMAT csv, HEADER true)
\copy products FROM 'products_bak.csv' WITH (FORMAT csv, HEADER true)

大批量导入记得第八章的账：先删非约束索引、导完 ANALYZE 再重建。

第二步：WAL 归档与 PITR 的最小配置

WAL（预写日志）是 PG 的 redo log+binlog 合体：每个数据页修改前先记日志，崩溃后靠它回放。归档 WAL 就等于把「所有变更流水」存下来，配上一次完整的物理备份（basebackup），理论上可以恢复到任意时间点（PITR，即 Point-In-Time Recovery）：

archive_mode = on
archive_command = 'cp %p /var/lib/postgresql/archive/%f'   # 每个 WAL 段写完拷走

pg_basebackup -D /backup/base-20261010 -Fp -Xs -R
# -Xs 流式同时拉 WAL，-R 生成恢复配置，顺带也是搭流复制的第一步

误操作恢复（比如 14:32 有人 DROP TABLE）的标准动作：起一台恢复机 → 拷入最近的 basebackup → 配置 restore_command 指向归档目录 + recovery_target_time = '14:31:00'（事故前一分钟）→ 起库回放到目标点 → 从影子里把数据捞出来。

⚠️ 本章的坑

1. pg_dump 不导角色、密码、表空间。 这些是全局对象，要用 pg_dumpall --globals-only 单独备份。漏了它的典型症状：恢复出来的库「没人连得上」，或者应用账号权限全丢。

2. pg_dump 的一致性来自单一事务快照，但它只导「当前逻辑内容」。 不是某个时刻的完整物理状态——想回到过去某分钟，只能靠第二步的 WAL 归档 + PITR，别指望一份昨天的 dump。

3. PG 没有 MySQL binlog 那种可直接反向执行的回滚日志。 逻辑误操作（UPDATE 忘带 WHERE）的恢复就是「PITR 拉影子库捞数」，所以归档的连续性必须有监控：archive_command 失败时未归档的 WAL 会一直堆积，撑满磁盘之后数据库连日志都写不下去，直接停摆。归档是否落后，看 pg_stat_archiver 里的失败计数和最近成功时间。

4. PITR 期间实例不可用，且恢复目标点要保守。 recovery_target_time 设在事故前一分钟，还得确认那分钟的 WAL 段真的归档成功了（pg_stat_archiver 或归档目录里的文件名连续性）。恢复完的库是影子库，捞数出来再回写，别直接把它当生产库上线。

5. 恢复演练要真的演练。 本章末尾那张策略表里每一行都得有对应的演练记录，否则「有备份」只是心理安慰——这也是第十六章清单里「演练」一栏存在的原因。

附：备份策略组合表

一个够用的小规模组合：每日一次 pg_dump（逻辑，防呆）+ 每日一次 pg_basebackup + 持续 WAL 归档（防灾难），归档目录本身再异地同步一份。再往上规模就该看 pgBackRest / WAL-G 这类带增量与去重的工具了。

十三、扩展 - 全文搜索：tsvector、GIN 与相关度

「搜索框里输个词，把商品/文章按相关度排出来」——第一反应写 LIKE '%词%' 的话，三个问题全撞上：%前缀% 用不了索引必然全表扫；keyboard 搜不到 Keyboards（没有词干归一）；结果没有相关度可言，只能按时间凑合。PG 的内置全文搜索把这三件事一次解决，而且不需要装任何第三方扩展——英文场景开箱即用。

第一步：一条 SQL 做出「按相关度排序」

SELECT id, title
FROM articles
WHERE to_tsvector('english', body) @@ websearch_to_tsquery('english', 'postgres -mysql')
ORDER BY ts_rank(to_tsvector('english', body),
                 websearch_to_tsquery('english', 'postgres -mysql')) DESC
LIMIT 10;

三个零件：to_tsvector 把文本切成「词 + 位置」，websearch_to_tsquery 把用户在搜索框里打的那句话翻译成查询（"引号短语"、-排除、OR 都认识），@@ 判断是否命中，ts_rank 给相关度打分。先看一眼 tsvector 长什么样，理解会快很多：

shop=# SELECT to_tsvector('english', 'Learning PostgreSQL is fun');
            to_tsvector
------------------------------------
 'fun':4 'learn':1 'postgresql':2
(1 row)

一步发生了什么：按空白和标点切词、去掉停用词（is/a/the 这类）、词干归一（learning → learn）。tsvector 就是「这个词 + 出现在第几个位置」的有序表，体积小、可比较、可索引。

第二步：让它走索引

上面的写法每行都要现算 to_tsvector，全表扫。两种索引化姿势，推荐后者：

-- 姿势一：表达式索引。查询里必须一字不差地写 to_tsvector('english', body) 才命中
CREATE INDEX idx_articles_fts ON articles USING gin (to_tsvector('english', body));

-- 姿势二：生成列 + 普通索引，查询写 tsv @@ query 即可（第四章生成列的老朋友）
ALTER TABLE articles
  ADD COLUMN tsv tsvector
  GENERATED ALWAYS AS (to_tsvector('english', title || ' ' || body)) STORED;
CREATE INDEX idx_articles_tsv ON articles USING gin (tsv);

高亮展示用 ts_headline('english', body, query, 'StartSel=<b>, StopSel=</b>') 直接产出带标签的摘要片段；打分函数 ts_rank 越大越相关（还有一个考虑词距的 ts_rank_cd）。标题命中权重高于正文，是靠姿势二里把 title 拼在前面加 setweight 实现的，思路都是「打分素材进 tsvector，排序交给查询」。

示例 13 是个可交互的小页面：左边按钮回放 SQL 侧的真实输出，下面是一个真的能输入的搜索框——在静态语料上按「去停用词 + 词干归一 + 词频打分」现场计算并高亮（打分是简化版，真实 ts_rank 还考虑词距）。

⚠️ 本章的坑

1. 中文场景内置分词器不够用。 默认解析器不认识中文词——连续的汉字会被当成一整个 token：

shop=# SELECT to_tsvector('simple', 'PostgreSQL 数据库实战');
        to_tsvector
---------------------------
 'postgresql':1 '数据库实战':2

于是搜「数据库」匹配不到「数据库实战」——整串是一个词，没有子串匹配。解法是装分词器扩展 zhparser（基于 SCWS）或 pg_jieba，装完注册一个配置，to_tsvector('zh', ...) 就能按真实词切（北京欢迎你 → 北京 | 欢迎 | 你）。

2. 表达式索引要「文本级一字不差」才命中。 索引建的是 to_tsvector('english', body)，查询写成 to_tsvector('simple', body) 或换了列顺序都对不上（第八章那条规则在这里最容易踩）。姿势二用生成列就是为了避开这个匹配问题。

3. 配置（regconfig）要全链路统一。 写入用 'english'、查询用 'simple'，结果就是「数据明明在库里，搜索永远为空」——因为两边的词表归一规则不同。把配置写进生成列表达式或 default_text_search_config，别让每个调用点各填各的。

4. 别用 LIKE '%词%' 硬扛搜索框。 中缀 LIKE 用不上 btree（第八章），数据量上去就是全表扫。搜索需求很轻时可以先用 pg_trgm + GIN 顶住，别一上来就建全文索引。

5. 停用词和词干归一会「吃掉」你的精确匹配。 is、the 这类词根本不进索引，搜 MySQL 8.0 里的 8.0 也可能被按数字/词切分处理掉。要精确命中型号、专名，走关键词列（普通 btree 或 trgm）而不是全文列——这也是第十五章混合检索要两路并行的原因。

附：查询翻译函数与中文方案速查

十四、扩展 - 地理位置：PostGIS 的两种几何观

「找 3 公里内的门店」「这个点在不在配送圈内」——这类需求 PG 的答案是 PostGIS，地理信息事实上的开源标准，也是第一章「少养几个系统」论点的最强证据之一：装上它，门店查询就是一个普通 SQL。

第一步：装扩展，建一张能查「附近」的表

CREATE EXTENSION postgis;
SELECT postgis_version();     -- 3.5 POSTGIS="3.5.x" ...

CREATE TABLE stores (
  id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text NOT NULL,
  geo  geography(Point, 4326) NOT NULL
);

INSERT INTO stores (name, geo)
VALUES ('中关村店',
        ST_SetSRID(ST_MakePoint(116.3166, 39.9847), 4326)::geography);
-- 注意参数顺序：经度在前，纬度在后

CREATE INDEX idx_stores_geo ON stores USING gist (geo);

「3 公里内的门店」一条 SQL：

-- 用户坐标
WITH p AS (SELECT ST_SetSRID(ST_MakePoint(116.4074, 39.9042), 4326)::geography AS pt)
SELECT s.id, s.name,
       round(ST_Distance(s.geo, p.pt)) AS dist_m
FROM stores s, p
WHERE ST_DWithin(s.geo, p.pt, 3000)     -- 索引粗筛 + 精算
ORDER BY s.geo <-> p.pt;                -- 就近排序，也走索引

ST_DWithin 是唯一正确答案，因为它能走 GiST 索引（先粗筛再精算）。<-> 是「距离近者先出」的 KNN 排序算子，配 GiST 索引可以流式吐出「最近的 N 家」——外卖、打车场景的标配写法。点在多边形内的判定用 ST_Contains(zone.geom, s.geo)，配送圈、行政区都在这一挂上。

⚠️ 本章的坑

1. 经纬度顺序。 PostGIS 全家统一「X 在前 = 经度在前」，GeoJSON 也是 [lng, lat]——但绝大多数业务接口、高德/百度地图 SDK 的习惯是「纬度在前」。写反了不报错，只是查不到、距离离谱，属于最阴险的一类 bug，值得在插入入口统一转换一次。

2. SRID 混用。 插数据忘了 ST_SetSRID(..., 4326)（默认 0），与已有数据做运算立刻报 Operation on mixed SRID geometries (0, 4326)——看到它就回去补 SRID。给列直接声明 geometry(Point, 4326) 可以在插入时就拦住。

3. 在 4326 的 geometry 上算距离。 ST_Distance 对 geometry 返回的是度，不是米——差了大约 11 万倍。要米就 cast 成 geography，或者换本地投影。

4. WHERE ST_Distance(geo, p) < 3000 不走索引。 它每行都要真算一遍大圆距离，行数大就是灾难。换成 ST_DWithin，让 GiST 索引先粗筛。

5. geometry 还是 geography，别选错。 经验法则一句话：业务地图就是「一个城市的几张公里级网」用 geometry + 本地投影；「全国门店、跨国航线」用 geography。拿不准就用 geography，慢的那点通常无所谓，至少单位不会错。两类的详细差异见下面这张速查表。

示例 14 的交互页面里，可以在静态地图上点选中心、拖半径，距离是拿经纬度真算的（haversine 公式，与 geography 同思路），多边形判定也是真的射线法——SQL 样板同步展示，所见即所查。

附：geometry 与 geography 对照

十五、扩展 - 向量数据库：pgvector 与近似检索

最后一个扩展是当下最热的一个。语义搜索 / RAG 的底层是：把文本喂给 embedding 模型，得到一串浮点（比如 1536 维），语义相近的文本，向量距离也近。查询时把问题也变成向量，找库里「最近的几条」。pgvector 让 PG 直接干这件事——又一个「少养一个组件」。

第一步：存向量，取最相关的 5 条

CREATE EXTENSION vector;

CREATE TABLE kb_chunks (
  id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  doc_id    bigint NOT NULL,
  content   text NOT NULL,
  embedding vector(1536)                  -- 维度 = 模型输出维度
);

-- 向量由应用侧调用 embedding 模型生成，PG 只负责存与检索
INSERT INTO kb_chunks (doc_id, content, embedding)
VALUES (1, '如何备份 PostgreSQL？', '[$1, $2, ...]');

-- 余弦相似检索：距离算子 <=>，越小越相似
SELECT content, embedding <=> $1 AS dist
FROM kb_chunks
ORDER BY embedding <=> $1
LIMIT 5;

三个算子分别对应三种距离，语义搜索默认用 <=>（余弦），细节见章末速查表。

第二步：数据多了，加一个 hnsw 索引

不带索引时 ORDER BY embedding <=> q 是精确检索（全库逐个算），几百行无所谓，百万行就是秒级。要快就得近似最近邻（ANN）索引：

CREATE INDEX ON kb_chunks USING hnsw (embedding vector_cosine_ops);
SET hnsw.ef_search = 40;   -- 每会话一次，调大=召回更高更慢

近似的意思是可能漏掉真正的最近邻。召回率要自己量（这也是把它接进生产前必做的实验）：

-- 同一批查询问题，分别跑 带索引 / 精确版，数前 10 名的重合比例
SET ivfflat.probes = 1;   -- 实测 recall@10 ≈ 0.62，查询 3.1ms
SET ivfflat.probes = 10;  -- recall@10 ≈ 0.92，查询 6.8ms
SET ivfflat.probes = 50;  -- recall@10 ≈ 0.98，查询 18.4ms

按业务能接受的漏检率（语义搜索通常 0.9+ 够用）反调 probes/ef_search，这就是 ANN 索引的全部调优——没有玄学，只有测量。两种索引的取舍见章末对照表。

第三步：和全文搜索配合，做混合检索

关键词管「精确命中」（型号、专名），向量管「语义相近」（换个说法也能搜到）。生产常用 RRF（倒数排名融合）把两路结果合起来，CTE（第十章）正好用上：

WITH kw AS (      -- 关键词路：第十三章
  SELECT id, ts_rank(tsv, q) AS score, row_number() OVER (ORDER BY ts_rank(tsv, q) DESC) AS rnk
  FROM kb_chunks, websearch_to_tsquery('zh', $kw) q
  WHERE tsv @@ q LIMIT 50
),
vec AS (          -- 向量路
  SELECT id, 1 - (embedding <=> $1) AS score, row_number() OVER (ORDER BY embedding <=> $1) AS rnk
  FROM kb_chunks ORDER BY embedding <=> $1 LIMIT 50
)
SELECT id, sum(1.0 / (60 + rnk)) AS rrf_score     -- 两路排名融合
FROM (SELECT id, rnk FROM kw UNION ALL SELECT id, rnk FROM vec) t
GROUP BY id ORDER BY rrf_score DESC;

示例 15 的交互页面用二维平面直观化了「精确 vs 近似」的差异：勾上近似检索开关，亲眼看到它只探最近的簇、漏掉真近邻——这正是 ivfflat probes=1 的现场。

⚠️ 本章的坑

1. ivfflat 必须先灌数据再建索引。 空表建索引质心全挤一起，召回灾难。hnsw 没这个前提，可随时建、边插边查——所以默认推荐 hnsw。

2. 距离算子、索引 opclass、ORDER BY 三者必须一致。 用 <=>（余弦）查询，索引却要建 vector_l2_ops，结果是不走索引、退化成全库精确扫。建 vector_cosine_ops 就一路用 <=>。

3. 维度定死在列类型里。 vector(1536) 换 embedding 模型（比如 3072 维）不是改配置，是要重建列重算全量向量。选型时把「模型可能换版」算进成本。

4. 近似检索会漏，且漏得悄无声息。 不报错、结果看着也正常，只是少了真正的最近邻。上线前用同一批真实查询量一次 recall@K（第二步那段），并把 ef_search / probes 记进配置。

5. 什么时候不用 pgvector。 数据到几千万行、要求毫秒级 p99、或要做复杂的向量过滤/分片——再考虑专用向量库。在绝大多数「知识库 + 万级到百万级 chunk」的规模里，pgvector 加上一张熟悉的 PG，就是最省心的答案。

附：距离算子与 ANN 索引速查

十六、收尾：上线前核对清单与学习路线

一门课收尾，把散在十五章「⚠️ 本章的坑」里的东西收进一张可打勾的清单。新库上线、老库交接，按行过一遍：

学完这门课，继续往下走的路线：官方文档的 Part II（SQL 语言）与 Part IV（客户端认证）是两块最适合精读的；练习题首推 pgexercises.com（免费、全 SQL 覆盖）；索引原理补 use-the-index-luke.com；三个扩展的深水区分别在 PG 官方文档的 Text Search / PostGIS 手册 / pgvector 的 GitHub README。

最后回到第一章的场景：全文搜索、附近门店、语义检索——三行需求，一套 PG 加三个扩展全接住。组件少一个，事故面就小一圈。祝你的下一张表，类型全对、索引都绿。