高级 SQL

中间件

PostgreSQL

高级 SQL

窗口函数、CTE、JSONB、全文检索、分区表——这些是把你从"会写 SQL"抬到"用 PostgreSQL 解决问题"的分水岭；本章按实战频率从高到低展开每一种，并顺带讲清各自的边界与代价。

一、窗口函数：把"分组后还要比较"变成一行 OVER

聚合函数配 GROUP BY 会把行折叠掉；窗口函数不折叠——每行保留，但让你看见同组的其他行。这是"每组 Top-N"“环比"“连续区间"三类需求的正解，比自连接干净一个数量级。

OVER 的三件套

<函数>() OVER (
    [PARTITION BY 分组列]      -- 划窗：和谁比
    [ORDER BY 排序列]          -- 定序：怎么排（决定 current row 在哪）
    [帧框架 ROWS/RANGE/GROUPS BETWEEN ... AND ...]  -- 定框：窗口的起止范围
)

帧框架最容易出错，先把三条规则记住：

只有聚合函数（sum/count/avg…）作窗口用时帧才有意义；排名族、lag/lead 不看帧。
写了 ORDER BY 而不写帧时，默认是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW——注意是 RANGE 不是 ROWS，排序值相同的"同辈行"会一起算进当前行，这是"累计和不等于逐行和"玄学的根源。
不写 ORDER BY 时整分区即整窗（累计和恒等于总和）。

-- 三种帧对并列金额的不同行为（PARTITION BY user_id ORDER BY total_amount）
sum(total_amount) OVER w_rows   -- ROWS：严格数行，同额只算先到的
       OVER (PARTITION BY user_id ORDER BY total_amount ROWS BETWEEN 1 PRECEDING AND CURRENT ROW),
sum(total_amount) OVER (PARTITION BY user_id ORDER BY total_amount
                  RANGE BETWEEN 100 PRECEDING AND CURRENT ROW)  -- RANGE：按值域，±100 内全算

排名族：去重、Top-N、分层

row_number（严格唯一序号）、rank（并列跳号 1,1,3）、dense_rank（并列不跳号 1,1,2）、percent_rank/cume_dist（分位）、ntile(n)（均分桶，做消费分层最顺手）。

-- 每个用户消费金额最高的 2 单（并列时按 id 打破平局，保证可重现）
SELECT * FROM (
  SELECT o.*,
         row_number() OVER (PARTITION BY user_id
                            ORDER BY total_amount DESC, id DESC) AS rn
  FROM orders o
) t
WHERE rn <= 2;

-- 消费金额四分位分层
SELECT user_id,
       ntile(4) OVER (ORDER BY total DESC) AS quartile
FROM (SELECT user_id, sum(total_amount) AS total FROM orders GROUP BY user_id) s;

高频需求”去重保留每组最新“就是 row_number 配 =1：按 created_at DESC, id DESC 排序取 rn=1。PG 方言里的 DISTINCT ON（见 03-SQL查询实战.md）也能取每组第一行且更简洁，但它强制改写 ORDER BY、拿不了"第二新”，窗口函数版本更适合多条件挑选。

偏移族 lag/lead：环比的完整写法

lag(expr, n, default) 取前 n 行，lead 取后 n 行；第三个默认参数用来兜住分区首行返回的 NULL——环比分母为 NULL 会让整个表达式变 NULL，NULLIF 防除零、lag(..., 0) 兜底，两个习惯都值一提。

WITH monthly AS (
  SELECT date_trunc('month', created_at) AS m, sum(total_amount) AS revenue
  FROM orders GROUP BY 1
)
SELECT m, revenue,
       lag(revenue)    OVER (ORDER BY m) AS prev_month,
       revenue - lag(revenue, 1, 0) OVER (ORDER BY m) AS mom_diff,
       round(100.0 * (revenue - lag(revenue, 1, 0) OVER (ORDER BY m))
                   / nullif(lag(revenue, 1, 0) OVER (ORDER BY m), 0), 1) AS mom_pct,
       lead(revenue)   OVER (ORDER BY m) AS next_month
FROM monthly
ORDER BY m;

聚合窗口：滑动平均与累计和

WITH daily AS (
  SELECT created_at::date AS d, count(*) AS cnt
  FROM orders GROUP BY 1
)
SELECT d, cnt,
       sum(cnt) OVER (ORDER BY d) AS running_total,
       round(avg(cnt) OVER (ORDER BY d
             ROWS BETWEEN 6 PRECEDING AND CURRENT ROW), 1) AS ma_7d
FROM daily ORDER BY d;

按行取 ROWS 6 PRECEDING 是"近 7 个有数据的天”；日历意义上的"近 7 天"要用 RANGE CURRENT_DATE - INTERVAL '7 days' ... 风格的值域帧（时间戳列可用 RANGE 配 interval 偏移），断日场景下两者差别明显——做监控图先用 ROWS 没问题，做经营报表先想 RANGE。

命名 WINDOW 子句与 FILTER

同一查询里多处 OVER 内容相同时，抽到 WINDOW w AS (...) 里复用，可读性和执行计划都干净：

SELECT user_id, created_at, total_amount,
       sum(total_amount) OVER w  AS running_total,
       count(*)    OVER w  AS seq_no,
       row_number() OVER w2 AS rank_by_total
FROM orders
WINDOW w  AS (PARTITION BY user_id ORDER BY created_at),
       w2 AS (PARTITION BY user_id ORDER BY total_amount DESC);

FILTER (WHERE ...)（PG 9.4+）让"按条件聚合"不用满屏 CASE WHEN，对聚合窗口同样有效：

SELECT user_id, created_at,
       count(*) FILTER (WHERE status = 'paid')
           OVER (PARTITION BY user_id ORDER BY created_at) AS paid_so_far
FROM orders;

缝合空洞：gaps and islands 的 row_number 差值技巧

“最长连续活跃天数"这类连续区间问题，靠一个经典变换：日期减去它的行号，连续日期会得到同一个常数，再按这个常数分组即可：

WITH d AS (   -- 每用户去重后的活跃日
  SELECT user_id, created_at::date AS day
  FROM orders GROUP BY 1, 2
), g AS (
  SELECT user_id, day,
         day - (row_number() OVER (PARTITION BY user_id ORDER BY day))::int AS grp
  FROM d
)
SELECT user_id, min(day) AS streak_start, max(day) AS streak_end,
       count(*) AS days
FROM g GROUP BY user_id, grp
ORDER BY days DESC
LIMIT 10;

二、CTE 进阶：递归、物化开关与写数据

递归 CTE：组织树 + 防环

递归 CTE 由"种子 + 递归项"通过 UNION ALL 拼成，递归项只能引用 CTE 自身一次。层级数据最大的暗雷是数据成环（经理关系写反导致无限递归），防环的稳妥做法是带一个 path 数组：

-- 本节用独立的演示表，与示例底座无关
CREATE TABLE employees (
    id         int PRIMARY KEY,
    name       text NOT NULL,
    manager_id int REFERENCES employees(id)
);
INSERT INTO employees VALUES
    (1, 'CEO', NULL), (2, '后端组长', 1), (3, 'DBA', 2),
    (4, '前端组长', 1), (5, '前端', 4);

WITH RECURSIVE subtree AS (
  SELECT id, name, manager_id, 1 AS lvl, ARRAY[id] AS path
  FROM employees
  WHERE manager_id IS NULL                 -- 种子：根节点
  UNION ALL
  SELECT e.id, e.name, e.manager_id, s.lvl + 1,
         s.path || e.id                    -- 递归项：路径追加当前 id
  FROM employees e
  JOIN subtree s ON e.manager_id = s.id
  WHERE NOT e.id = ANY (s.path)            -- 防环：已在路径上就不再展开
)
SELECT repeat('  ', lvl - 1) || name AS org_chart, lvl, path
FROM subtree ORDER BY path;

path 数组还有副产物：可以直接当"层级字符串"做缩进排序。PG 14 起可用 SQL 标准的 CYCLE 子句让引擎替你记账，更省事：

WITH RECURSIVE subtree AS (
  SELECT id, name, manager_id, 1 AS lvl FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, s.lvl + 1 FROM employees e
  JOIN subtree s ON e.manager_id = s.id
  CYCLE id SET is_cycle USING trail        -- 自动检测 id 成环，命中即停
)
SELECT name, lvl FROM subtree WHERE NOT is_cycle;

配套的 SEARCH ... SET order_col USING ...（同为 PG 14+）可以自动做深度优先/广度优先输出排序，不用手拼 path。

MATERIALIZED / NOT MATERIALIZED：优化器的开关在 PG 12 反转了

PG 12 之前 CTE 永远是"优化防火墙”——先物化成临时结果再走主查询；PG 12 起非递归、无副作用、只引用一次的 CTE 会被自动内联进主查询，优化器可以把它和外面的条件一起推下。这大多数时候是好事，但两类场景要手动管：

想利用 CTE 当"漏斗"先压掉 99% 的行再做大 JOIN（旧版靠物化防火墙保顺序）：加 AS MATERIALIZED 找回旧行为；
被引用多次、想内联换更好计划：加 NOT MATERIALIZED。

改完务必用 EXPLAIN (ANALYZE) 对比（见 09-查询优化与EXPLAIN.md）——这个开关是"同一句 SQL 前后大版本性能对不上"的头号嫌疑人之一。

数据修改型 WITH：方便，但要懂它的快照

CTE 里可以写 INSERT/UPDATE/DELETE 并 RETURNING 接出来继续用，做"先删再归档"一步到位：

WITH moved AS (
  DELETE FROM orders
  WHERE status = 'cancelled' AND created_at < now() - interval '90 days'
  RETURNING *
)
INSERT INTO orders_archive SELECT * FROM moved;

注意整条语句（包括主查询和所有 CTE）看到的是同一个语句级快照：CTE 里刚删掉的行，主查询里 FROM orders 依然看得见；写操作的顺序也不保证与书写顺序一致。凡"改完再基于同一张表统计"的意图，都改成从 RETURNING 取数，别回读表。并发写同一行时的等待与报错行为，见 05-事务与锁.md；大批量 DELETE 的回绕与膨胀代价，见 06-MVCC与Vacuum.md。

三、JSONB 实战：弹性 schema 的正用与滥用

操作符矩阵

JSONB 是二进制存储、键去重、支持索引的 JSON；取值类操作符长得很像，混用是新手最大痛点：

操作符	作用	例（attrs = `{"spec":{"color":"red"}}`）	结果
`->`	取子对象/元素，返回 jsonb	`attrs -> 'spec'`	`{"color":"red"}`
`->>`	同上但转 text	`attrs ->> 'spec'` 后再 `->> 'color'`	`red`
`#>` / `#>>`	路径取值（text[] 或返回）	`attrs #>> '{spec,color}'`	`red`
`@>`	包含（可走 GIN 索引）	`attrs @> '{"spec":{"color":"red"}}'`	true
`?` / `?	`/`?&`	键存在 / 任一键 / 所有键	`attrs ? 'spec'`
`		`	合并（同键后者胜）
`-` / `#-`	删键 / 按路径删	`attrs - 'spec'`	去掉 spec

写查询时的第一原则：过滤尽量用 @>（有索引可走），取值用 #>> 一步到位；-> 链式取到一半忘了转 text，就会拿 jsonb 和字符串比较，出现"明明相等就是查不到"——'red'::jsonb = 'red'::text 是类型错误不是值错误。

索引姿势：选对 opclass 才不白花空间

CREATE INDEX idx_products_attrs ON products USING gin (attrs jsonb_path_ops);
SELECT * FROM products WHERE attrs @> '{"spec":{"color":"red"}}';   -- 走索引

GIN 默认 jsonb_ops 支持 @>、?、?|、?&；jsonb_path_ops 只支持 @>，但索引体积更小、包含查询更快。绝大多数业务只做属性包含查询，直接选 path_ops；确需"键存在性"再换回默认。高频查询的少数键还可以抽成表达式索引（如 (attrs #>> '{spec,color}')）走 btree。索引原理与取舍见 04-索引实战.md。

局部更新与合并：

UPDATE products
SET attrs = jsonb_set(attrs, '{spec,color}', '"blue"', create_missing := true)
WHERE id = 42;

聚合与输出 JSON：给 API 的一行式结果

jsonb_agg/jsonb_object_agg 在 SQL 里组装数组/对象，配合 row_to_json/json_build_object 能省掉应用层大量拼装：

SELECT jsonb_build_object(
         'name', u.nickname,
         'orders', coalesce(jsonb_agg(jsonb_build_object('id', o.id, 'total', o.total_amount))
                            FILTER (WHERE o.id IS NOT NULL), '[]'::jsonb)
       ) AS profile
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.nickname;

FILTER (WHERE o.id IS NOT NULL) 用来把 LEFT JOIN 产生的 NULL 行从聚合里剔掉，否则无订单用户会得到 [null] 而不是 []——这是 SQL 组装 JSON 的经典小坑。

SQL/JSON 标准函数（PG 17+）

PG 17 引入 SQL 标准的 JSON 查询族：JSON_TABLE() 把 JSON 文档直接展开成表（等价于其他库的 unnest/LATERAL 组合，但写法标准），JSON_VALUE 取标量、JSON_QUERY 取结构化片段、JSON_EXISTS 判存在：

SELECT jt.*
FROM json_table(
  '[{"id":1,"sku":"A"},{"id":2,"sku":"B"}]'::jsonb,
  '$[*]' COLUMNS (id int PATH '$.id', sku text PATH '$.sku')
) AS jt;

SELECT json_value(attrs, '$.spec.color' RETURNING text DEFAULT 'unknown' ON EMPTY)
FROM products;

迁移自 Oracle/MySQL 8 的工程会受益；纯 PG 项目里它和 #>> + 展开 function 的旧写法长期并存，别强推。

什么时候别用 JSONB

三类信号出现就回头建列：需要跨字段约束（CHECK、排他约束管不到 jsonb 内部）、需要外键（jsonb 里存 user_id 没人帮你验存在性）、高频局部更新大对象——jsonb 没有行内部分更新，一次 jsonb_set 都是整个值重写（大值还落 TOAST），写放大 + 膨胀（见 06-MVCC与Vacuum.md）双杀。经验线：“稀疏、只增不改、按 key 整取/包含查"的属性放 JSONB；“核心指标、要聚合要约束"的字段放列；两边混合（列 + 兜底 attrs 扩展）是常见生产形态。

四、全文检索：tsvector/tsquery 的正确工程姿势

两个类型加一条流水线

全文检索的两块基石：tsvector 是把文本切词、归一化（转小写、去词尾）后的"词位 + 权重"集合；tsquery 是查询表达式，支持 &（与）、|（或）、!（非）、<->（相邻短语）。to_tsvector('english', ...) / plainto_tsquery 完成转换，匹配用 @@，排序用 ts_rank：

-- 前提：products 上已有 search_tsv 生成列（建法见下一小节）
SELECT title, ts_rank(search_tsv, q) AS rank
FROM products, websearch_to_tsquery('english', 'red shoes -boots') AS q
WHERE search_tsv @@ q
ORDER BY rank DESC
LIMIT 20;

websearch_to_tsquery（PG 11+）是给用户输入框用的那一个：双引号当短语、裸词自动 AND、-词 排除，且从不因用户乱输语法而报错——to_tsquery 只配程序生成，plainto_tsquery 只会全部 AND，这三个的分工是入门最常踩的坑。

存储侧：生成列 + GIN，别再写触发器

检索列必须提前物化（实时 to_tsvector 用不上索引）。PG 12 起用生成列把它变成表定义的一部分，比触发器流派少一整套维护代码：

ALTER TABLE products ADD COLUMN descr text;   -- 演示用商品描述列；底座已有 title

ALTER TABLE products
  ADD COLUMN search_tsv tsvector
  GENERATED ALWAYS AS (
    setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(descr, '')), 'B')
  ) STORED;                                  -- 12~17 只有 STORED；18 起默认 VIRTUAL（读时算，别指望 VIRTUAL 配索引）

CREATE INDEX idx_products_search ON products USING gin (search_tsv);

多列加权（A/B/C/D）让标题命中天然压过正文命中。18 的 VIRTUAL 生成列不落盘、无法索引，这里仍然要显式 STORED。

中文检索的现实

PG 内置分词器覆盖不了中文——没有词典切不出词位，tsvector 退化成整句一个词。可行路线是装带分词词典的扩展（zhparser、pg_jieba 等，或新一代向量检索生态自带的方案），部署与词典运维都有额外成本；这部分不在手册展开。判断标准很简单：数据量小、能接受"按拼音/前缀/pg_trgm 模糊匹配"的中文搜索，用 PG 足够；正经中文全文检索需求，先评估外部分词链路再决定。

与 Elasticsearch 的分工

量级和功能是两条硬线：PG 全文检索单机在千万级文档、中等相关性要求的场景里完全胜任，且赢在事务一致（数据一变索引即变，没有同步链路）与零额外组件；ES 赢在分布式水平扩展、聚合分析、插件化打分、向量混合检索与近实时大规模索引。选型时想想"能不能忍受一条 Canal/双写同步链路的复杂度”——能忍则各司其职（PG 存真相、ES 管搜索），不忍就先用 PG 的 tsvector 扛到该换的那天。同步架构可参考本知识库 elasticsearch/ 手册中的相关章节。

五、pg_trgm：给模糊匹配和拼写纠错一个索引

ILIKE '%shoes%'、LIKE '%中文%' 这类"包含匹配"走不了普通 btree——前导通配符让索引无从谈起（原因见 04-索引实战.md）。pg_trgm 把字符串拆成三元组（“shoes” → sho, hoe, oes…），用相似度代替前缀：

CREATE EXTENSION pg_trgm;
CREATE INDEX idx_products_title_trgm ON products USING gin (title gin_trgm_ops);

-- 1) 包含查询提速：计划里 WHERE title ILIKE '%shoes%' 现在走 bitmap index scan
SELECT id, title FROM products WHERE title ILIKE '%shoes%';

-- 2) 模糊匹配 + 相似度排序（默认阈值 0.3，session 可调）
SELECT title, similarity(title, 'shose') AS s
FROM products
WHERE title % 'shose'                     -- % = 相似度超过 set_limit 的阈值
ORDER BY s DESC LIMIT 10;

-- 3) 拼写纠错："你是不是想搜…"
SELECT DISTINCT word FROM products, unnest(string_to_array(title, ' ')) word
WHERE word % 'shose' AND similarity(word, 'shose') > 0.4
ORDER BY similarity(word, 'shose') DESC LIMIT 3;

注意两点：% 操作符依赖 pg_trgm.similarity_threshold（SELECT set_limit(0.2) 可放宽）；索引用 GIN（查得多、写较少）还是 GiST（写多、相似度排序弱一些）按写放大权衡。pg_trgm 也能加速正则匹配，但别把它当全文检索用——它不懂词形，只懂字符形状。

六、声明式分区：给亿级日志表一个生命周期

PG 10 起的声明式分区把"按时间切表"变成一等公民：父表定 schema，子表各管一段，查询自动裁剪。三种策略：RANGE（时间/数值区间，最常用）、LIST（枚举，如地区/租户）、HASH（均匀打散无范围语义的大表）。

按月分区的完整配方：

CREATE TABLE events (
  id         bigserial,
  type       text,
  payload    jsonb,
  created_at timestamptz NOT NULL,
  PRIMARY KEY (id, created_at)              -- 注意：分区键必须进主键/唯一约束
) PARTITION BY RANGE (created_at);

CREATE TABLE events_202610 PARTITION OF events
  FOR VALUES FROM ('2026-10-01') TO ('2026-11-01');
CREATE TABLE events_202611 PARTITION OF events
  FOR VALUES FROM ('2026-11-01') TO ('2026-12-01');

CREATE TABLE events_default PARTITION OF events DEFAULT;  -- 兜底，防漏建分区炸写入

分区裁剪：性能来源，也是验证重点

查询带 WHERE created_at >= '2026-10-05' 时，规划器直接只扫 events_202610（启动裁剪）；参数化/JOIN 键动态值则可能推迟到执行期逐行决定（运行时裁剪）。裁剪是否生效必须用 EXPLAIN 看"实际扫了几个分区”，没裁剪的分区表比不分区还慢——方法见 09-查询优化与EXPLAIN.md。

归档生命周期：DETACH 代替 DELETE

老分区整月下线：ALTER TABLE events DETACH PARTITION events_202610; 再把摘下来的普通表备份/转冷/删除（呼应 11-备份恢复与运维.md 的数据生命周期）。PG 14+ 支持 DETACH ... CONCURRENTLY（必要时再 ... FINALIZE），避免摘除动作短暂锁表打断业务。对比 DELETE 两千万行再等 vacuum 收尸（见 06），DETACH 是秒级且不产生膨胀的删除。PG 18 还允许 VACUUM ONLY events / ANALYZE ONLY 只处理父表元数据不逐个扫子表（18 release notes），autovacuum 本就只清子表。

绕不过的限制：唯一约束必须含分区键

这是分区表最重要的陷阱：“全局唯一"在分区表上不存在——唯一索引/主键只在每个分区内部独立生效，而分区又必须各自完整。所以 PRIMARY KEY (id)（不含分区键）直接报 unique constraint on partitioned table must include all partitioning columns。业务唯一性（比如 id 全局不重）要么接受把 created_at 拼进主键（如上例），要么靠 bigserial/uuid 的生成顺序保证 + 应用层校验，要么别用分区表。没有全局索引是 PG 分区和 Oracle/MySQL 分区习惯差异最大的一条。

日常自动建分区/定期摘分区可交给 pg_partman 扩展管理（见 15-扩展与生态.md），或自己写维护函数（见 13-函数与存储过程.md），不展开。

适合分区的场景高度一致：亿级日志/事件/审计表 + 明确时间冷热维度 + 按老数据整段清理。OLTP 小表硬上分区只会加锁路径和心智负担。

七、视图与物化视图：一份查询，两种命运

普通视图是宏。CREATE VIEW 只是给一段查询起名字：规划器在查询视图时把视图定义就地展开、和外层条件合并优化——没有物化，也没有缓存：

CREATE VIEW active_orders AS
SELECT id, user_id, total_amount, created_at
FROM orders
WHERE status IN ('paid','shipped');

所以视图的价值在抽象与权限收敛（封装复杂 JOIN、配合 GRANT 只暴露安全列，呼应 12-安全与权限管理.md），与性能无关。视图定义多重，每次查都要付全额——想省这份钱，才轮到物化视图出场。

同一份重聚合天天在跑，就把它存下来。物化视图（PG 9.3+）把查询结果作为真实数据落表，代价是需要自己管新鲜度：

CREATE MATERIALIZED VIEW mv_daily_sales AS
SELECT date_trunc('day', created_at) AS day,
       count(*)                      AS order_count,
       sum(total_amount)             AS gmv
FROM orders
GROUP BY 1;

CREATE UNIQUE INDEX ON mv_daily_sales (day);   -- 增量刷新的入场券

刷新只有两种姿势，锁语义天差地别：

REFRESH MATERIALIZED VIEW mv_daily_sales;——全量重跑查询并整体替换，官方文档明说这会把并发读者挡在门外；大表上几秒到几分钟，这几秒里所有报表都卡住。
REFRESH ... CONCURRENTLY——按唯一索引匹配新旧行、只增删改差异，不挡并发读。但它有三个硬前提：MV 上至少有一个只用列名、含全部行的 UNIQUE 索引（表达式索引、带 WHERE 的部分索引都不算）；MV 必须已经填充过数据（WITH NO DATA 建出来的第一次不能用 CONCURRENTLY）；同一张 MV 同时只允许一个 REFRESH 在跑。

两个容易踩的细节：PG 17+ 执行 REFRESH 需要 MV 上的 MAINTAIN 权限（17 之前只能靠属主身份，见 12-安全与权限管理.md）；REFRESH 期间 search_path 被临时强制为 pg_catalog, pg_temp——视图定义里写了不带 schema 限定的函数名时，刷新时的解析未必和你平时手测一致，> 注意把定义里的对象全写上 schema。

选不选物化视图，问一句"能容忍多旧”：日报汇总、COUNT(*) 近似加速（呼应 09-查询优化与EXPLAIN.md 的 reltuples 方案）、搜索宽表聚合层——合适；要求交易强一致的数据——不合适，它没有任何自动同步机制，把刷新挂到触发器或 pg_cron（见 14-触发器.md、15-扩展与生态.md）是常见做法，但那本质上是在接受"异步副本"的身份。

八、杂项但高频

NULLS FIRST / NULLS LAST：PG 默认升序 NULL 排最后、降序 NULL 排最前（DESC NULLS FIRST），与多数人的"NULL 当最小"直觉相反，排行类查询显式写出更稳。

行比较 + keyset 分页：(a, b) < (x, y) 按字典序逐列比较，正好配合 (created_at, id) 复合游标做无限下拉——比 OFFSET 深翻高效得多（OFFSET 的线性代价见 09-查询优化与EXPLAIN.md）：

SELECT id, created_at, total_amount
FROM orders
WHERE (created_at, id) < (:last_ts, :last_id)     -- 上一页末行
ORDER BY created_at DESC, id DESC
LIMIT 50;

方向要与 ORDER BY 一致（两列都 DESC）。行比较能否走好索引视版本与形态而异，拿不准就 EXPLAIN，必要时展开成等价 a < x OR (a = x AND b < y)。

TABLESAMPLE：FROM orders TABLESAMPLE BERNOULLI (5) 按行随机抽样 5%，SYSTEM (5) 按页抽样——快得多，但受物理聚簇影响有偏。估算总量、抽查质量用它，别用 ORDER BY random() LIMIT n 全表排序。

generate_series：造数据与日历表。测试数据生成、报表补零（把没有订单的日期用日历左连接补齐）全靠它：

-- 一年日历（配窗口函数补断点）
SELECT d::date FROM generate_series('2026-01-01'::date, '2026-12-31', '1 day') AS d;

-- 可复现的百万行测试数据（setseed 固定随机种子）
-- 用独立负载表，绕开底座 orders 对 users/products 的外键（底座父表只有几行种子）
CREATE TABLE load_test (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id    int NOT NULL,
    amount     numeric(12,2),
    created_at timestamptz NOT NULL,
    status     text
);
SELECT setseed(0.42);
INSERT INTO load_test (user_id, amount, created_at, status)
SELECT 1 + (random()*9999)::int,
       round((random()*500)::numeric, 2),
       now() - (random()*365)::int * interval '1 day',
       (ARRAY['paid','pending','cancelled'])[1 + (random()*3)::int]
FROM generate_series(1, 1000000);

函数、存储过程与触发器：PG 11+ 的 CREATE PROCEDURE/CALL 支持过程内 COMMIT/ROLLBACK（“分批迁移每批提交"的官方容器），触发器响应 DML、事件触发器响应 DDL——这一整套服务端编程在 13-函数与存储过程.md 和 14-触发器.md 有专章，这里不再展开。

实战建议

判断"该用哪个高级特性而不是加表/加服务"的几条线，按出现频率排：

需求是"组内比较/排名/前后行” → 窗口函数，先写一版自连接对照 EXPLAIN 也行，但最终代码该是 OVER；
需求是"层级递归" → 递归 CTE + CYCLE/path 防环，超过万级节点再考虑闭包表或 ltree；
字段"稀疏、加一个属性不想 ALTER 线上表" → JSONB + @> 走 GIN(path_ops)，一旦需要聚合、约束、外键、高频局部更新，立刻迁回普通列；
搜索框 → 英文/多语言商品名先 tsvector 生成列 + GIN；中文/模糊/错字容忍靠 pg_trgm 兜住中小数据量，正经中文分词或亿级文档再谈 ES；
时间序列大表膨胀、清理靠 DELETE → 声明式分区 RANGE，按月 DETACH，接受唯一约束必须含分区键；
CTE 跨大版本行为变了（PG 12 内联）→ 调优时显式 MATERIALIZED/NOT MATERIALIZED 锁定计划。

还有一条总原则：高级特性解决的是表达方式与运维形态问题，不改变数据建模的第一性——先有正确的表、索引、约束，再叠窗口、JSONB 和分区；顺序反了，任何高级特性都救不了查询。

MVCC 与 Vacuum Go 客户端 pgx