PostgreSQL 核心使用

SQL 不只是「能用」

PostgreSQL 的 SQL 方言是它最容易被低估的部分。同样一个需求，MySQL 里可能要写临时表加应用层拼装，PostgreSQL 一条语句就能表达完。这带来两个好处：网络往返少、执行计划由数据库统一优化。

本篇不铺语法手册，只挑运维和开发都用得上的几块：日常高频 SQL、JSONB、窗口函数、索引选型，以及怎么用 EXPLAIN 看懂一条语句到底慢在哪。示例基于 16 版，建一张演示表：

CREATE TABLE orders (
    id          bigserial PRIMARY KEY,
    user_id     bigint NOT NULL,
    status      text NOT NULL DEFAULT 'pending',
    amount      numeric(12,2) NOT NULL,
    payload     jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at  timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX idx_orders_user_created ON orders (user_id, created_at DESC);
CREATE INDEX idx_orders_payload ON orders USING gin (payload jsonb_path_ops);

高频 SQL 写法

UPSERT：有则更新，无则插入

INSERT INTO orders (id, user_id, status, amount)
VALUES (1001, 42, 'paid', 199.00)
ON CONFLICT (id) DO UPDATE
SET status = EXCLUDED.status,
    amount = EXCLUDED.amount
RETURNING id, status;

EXCLUDED 代表「本次想插入的那行」。配合 RETURNING 可以一次拿到写入结果，省掉一次查询。注意 ON CONFLICT 的冲突目标必须有唯一索引或主键，否则语句直接报错。

RETURNING：写入顺手拿结果

UPDATE orders
SET status = 'shipped'
WHERE status = 'paid' AND created_at < now() - interval '1 day'
RETURNING id, user_id, amount;

批量更新时用 RETURNING 拿到受影响的行，比「先 SELECT 再 UPDATE」少一次往返，也避免并发下的竞态。

DISTINCT ON：取每组第一条

-- 每个用户最近的一笔订单
SELECT DISTINCT ON (user_id) user_id, id, amount, created_at
FROM orders
ORDER BY user_id, created_at DESC;

这是 PostgreSQL 独有的写法，等价于窗口函数 row_number() = 1，但更短。ORDER BY 必须以 DISTINCT ON 的列开头，否则报错。

生成序列与 CTE

-- 补全缺失的日期，做报表时很常用
WITH days AS (
    SELECT generate_series(
        date_trunc('day', now()) - interval '6 days',
        date_trunc('day', now()),
        interval '1 day'
    ) AS day
)
SELECT days.day, count(o.id) AS order_count
FROM days
LEFT JOIN orders o
       ON o.created_at >= days.day
      AND o.created_at < days.day + interval '1 day'
GROUP BY days.day
ORDER BY days.day;

CTE 在 12 之后默认可以被内联（除非写了 MATERIALIZED），不再像老版本那样总是生成临时结果集，性能上不用担心。

JSONB：把半结构化数据当回事

json 和 jsonb 的区别只有一句话：json 存原始文本，保留空格和键顺序；jsonb 存解析后的二进制，支持索引、支持高效查询。业务字段一律用 jsonb。

-- 插入一条带 JSON 的数据
INSERT INTO orders (user_id, amount, payload)
VALUES (
    42,
    299.00,
    '{"channel": "web", "coupon": "NEW20", "amount": 299.00, "items": [{"sku": "A1", "qty": 2}], "tags": ["promo", "cn"]}'
);

-- 取出 JSON 字段：-> 返回 jsonb，->> 返回 text
SELECT
    payload ->> 'channel'        AS channel,
    payload -> 'items' -> 0 ->> 'sku' AS first_sku,
    (payload ->> 'amount')::numeric   AS amount_in_payload
FROM orders
WHERE user_id = 42;

-- 包含判断：payload 是否包含指定的键值对
SELECT id FROM orders
WHERE payload @> '{"channel": "web"}';

-- 键是否存在
SELECT id FROM orders WHERE payload ? 'coupon';

-- 数组展开：把 items 变成多行
SELECT o.id, item ->> 'sku' AS sku, (item ->> 'qty')::int AS qty
FROM orders o
CROSS JOIN LATERAL jsonb_array_elements(o.payload -> 'items') AS item;

常用的 JSONB 操作符整理成一张表：

操作符	返回类型	含义
->	jsonb	按 key 取对象字段，或按下标取数组元素
->>	text	同上，但结果转成文本
#>	jsonb	按路径数组取值，如 '{a,b}'
#>>	text	按路径数组取值并转文本
@>	boolean	左侧是否包含右侧（最常用，能走 GIN 索引）
<@	boolean	左侧是否被右侧包含
?	boolean	顶层 key 或数组元素是否存在
?|	boolean	是否存在任意一个指定 key
?&	boolean	是否同时存在所有指定 key
-	jsonb	删除 key 或数组元素
||	jsonb	合并两个 jsonb，同名 key 右侧覆盖左侧

JSONB 索引有讲究

默认的 gin (payload) 使用 jsonb_ops，支持 ?、?|、?& 和 @>；gin (payload jsonb_path_ops) 体积更小、@> 更快，但不支持存在性判断。选哪个取决于查询模式，别建了索引发现用不上。另外 ->> 后的比较（比如 payload->>'status' = 'paid'）默认不走 GIN，要用表达式索引。

窗口函数：分组排名与累计

窗口函数不折叠行，而是在每组内附加计算结果。相比 GROUP BY，它能保留明细。

-- 每个部门按薪资排名，并算部门总额与累计占比
SELECT
    dept,
    name,
    salary,
    row_number() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn,
    rank()       OVER (PARTITION BY dept ORDER BY salary DESC) AS rnk,
    sum(salary)  OVER (PARTITION BY dept)                      AS dept_total,
    round(
        100.0 * sum(salary) OVER (
            PARTITION BY dept ORDER BY salary DESC
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) / sum(salary) OVER (PARTITION BY dept),
        2
    ) AS cum_pct
FROM employees
ORDER BY dept, rn;

要点：

• PARTITION BY 分组，ORDER BY 决定组内顺序；不写 PARTITION BY 就是全表一个窗口。
• row_number() 严格递增不重复，rank() 并列同名次且跳号，dense_rank() 并列不跳号。
• 带 ORDER BY 的聚合窗口默认帧是「从第一行到当前行」，算累计值正好；要算全组总额就别加 ORDER BY。
• 窗口函数不能直接写在 WHERE 里，要套一层子查询或 CTE 再过滤。

索引类型与选型

PostgreSQL 的索引类型比 MySQL 丰富，选对了性能差一个数量级。先看全景：

索引类型	结构	擅长	典型场景	不擅长
B-tree	平衡树	等值、范围、排序、前缀匹配	主键、时间范围、ORDER BY	数组、JSON、全文
Hash	哈希表	纯等值查询	超大表的单值查找	范围、排序
GIN	倒排索引	多值包含、数组、JSONB、全文检索	@>、&&、tsvector	高频写入（更新代价高）
GiST	通用搜索树	几何、范围、最近邻	PostGIS 地理查询、<-> 距离排序	精确等值
BRIN	块范围摘要	物理有序的大表	按时间顺序写入的日志表	随机分布的数据
SP-GiST	空间分区树	非平衡空间结构	电话号码、IP 段	通用场景

几个实战判断：

• 默认 B-tree。组合索引注意列顺序：等值条件列在前，范围/排序列在后。(user_id, created_at DESC) 能同时服务 WHERE user_id = ? ORDER BY created_at DESC。
• GIN 用于「一行里包含多个值」。JSONB、数组、全文检索都靠它。写入放大明显，更新频繁的字段慎用。
• BRIN 用于「物理顺序和查询条件相关」。一张按时间追加的日志表，BRIN (created_at) 的体积可能只有 B-tree 的百分之一，扫几十万行也不慢。
• GiST 用于距离和范围。ORDER BY geom <-> point LIMIT 10 这种最近邻查询，B-tree 帮不上忙。

进阶用法：

-- 部分索引：只索引关心的子集，体积小、写入便宜
CREATE INDEX idx_orders_pending ON orders (created_at)
WHERE status = 'pending';

-- 表达式索引：让函数查询也能走索引
CREATE INDEX idx_orders_month ON orders (date_trunc('month', created_at));

-- 覆盖索引：把需要的列带上，避免回表
CREATE INDEX idx_orders_cover ON orders (user_id) INCLUDE (amount, status);

索引用不用得上，看执行计划

建完索引别猜，直接 EXPLAIN 看有没有 Index Scan。常见「建了索引却不走」的原因：列上用了函数或隐式类型转换、选择性太差（返回大部分行）、统计信息过期（跑一下 ANALYZE）、或者查询写的是 LIKE '%xxx'（前后都有通配符，B-tree 无能为力）。

EXPLAIN 解读

EXPLAIN 只估算不执行，EXPLAIN ANALYZE 真执行并给出实际耗时，BUFFERS 额外显示缓存命中。生产上分析慢查询用后者，但要注意它会真的执行语句——UPDATE/DELETE 记得包在事务里回滚。

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, amount, created_at
FROM orders
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 20;

输出大致长这样：

Limit  (cost=0.43..8.21 rows=20 width=28) (actual time=0.031..0.052 rows=20 loops=1)
  Buffers: shared hit=6
  ->  Index Scan Backward using idx_orders_user_created on orders
        (cost=0.43..8210.00 rows=20000 width=28) (actual time=0.029..0.047 rows=20 loops=1)
        Index Cond: (user_id = 42)
        Buffers: shared hit=6
Planning Time: 0.112 ms
Execution Time: 0.071 ms

逐段读：

• 节点类型：Seq Scan 全表扫、Index Scan 走索引回表、Index Only Scan 只读索引、Bitmap Heap Scan 先建位图再回表、Nested Loop/Hash Join/Merge Join 是连接方式。
• cost：启动代价..总代价，是优化器定义的抽象代价单位（含页读取和 CPU 估算），不是毫秒。只用来横向比较不同计划，不能当时间看。
• rows：优化器估算的行数。和 actual rows 差得远，说明统计信息不准，跑 ANALYZE 表名。
• width：每行平均字节数，影响内存和网络传输。
• actual time：真实耗时（毫秒），rows 是实际行数，loops 是该节点被执行了几次。每个节点的 actual time 要乘以 loops 才是总耗时。
• Buffers：shared hit 是缓存命中，read 是读磁盘，dirtied 是弄脏的页。read 很大说明缓存不够或索引没覆盖。
• Planning Time / Execution Time：解析计划和实际执行耗时，分开看，别把计划时间算到 SQL 头上。

常见信号对照：

现象	可能原因	处理方向
Seq Scan 大表 + rows 很大	缺索引，或条件选择性差	补索引，或接受全表扫
rows 估算与实际差几个数量级	统计信息过期/采样不足	ANALYZE，必要时调 default_statistics_target
Sort Method: external merge Disk: ...	work_mem 不够，排序落盘	调大 work_mem 或减少排序数据量
Rows Removed by Filter 很大	索引过滤后大量回表再丢弃	建更贴合条件的组合/部分索引
Nested Loop 且内层 loops 巨大	连接顺序或缺少内层索引	补内层索引，或调整连接方式
Buffers: read 远大于 hit	缓存未命中，数据不在内存	检查 shared_buffers、是否冷数据

EXPLAIN 的三个坑

1. EXPLAIN ANALYZE 会真执行，写操作务必 BEGIN; ... ROLLBACK;。
2. 加 BUFFERS 才有缓存命中信息，只看 cost 判断不了 IO 瓶颈。
3. 小表上优化器可能故意选 Seq Scan，这是对的，不要为了「计划好看」强制走索引。

事务隔离与 MVCC 快照

第 1 篇讲过原理，这里看实际行为。开两个 psql 会话验证 Read Committed 和 Repeatable Read 的差别：

-- 会话 A：Read Committed（默认）
BEGIN;
SELECT count(*) FROM orders WHERE status = 'paid';   -- 假设 100

-- 会话 B：插入一条并提交
INSERT INTO orders (user_id, status, amount) VALUES (1, 'paid', 10);
COMMIT;

-- 会话 A：再查一次，能看到 101 —— 每条语句取新快照
SELECT count(*) FROM orders WHERE status = 'paid';
COMMIT;

同样的操作换成 Repeatable Read，会话 A 第二次查询仍是 100，因为整个事务共用一个快照。这就是「可重复读」的字面含义。

查看当前快照和事务 ID：

SELECT txid_current_if_assigned() AS xid,
       pg_current_snapshot()       AS snapshot,
       pg_snapshot_xmin(pg_current_snapshot()) AS xmin;

pg_current_snapshot() 的输出里，xmin 之前的活儿 VACUUM 都能清，xmax 之后的事务对当前快照不可见。长事务会拖住 xmin，导致死元组清不掉、表膨胀——这是第 6 篇的重点之一，这里先埋个伏笔。

写冲突的处理方式也值得知道：

• 两个事务改同一行，后到的会等前一个提交；前一个回滚了，后一个继续。
• Repeatable Read 下，如果后到的事务要改的行在前一个事务提交后已经变了，会直接报 could not serialize access due to concurrent update，应用需要重试。
• Serializable 下冲突检测更严格，报 40001 时必须重试整个事务。

不要用「查询一下再更新」代替原子操作

「先 SELECT 判断余额，再 UPDATE 扣减」在并发下必然出问题，不管隔离级别是哪个。正确做法是把判断写进 UPDATE ... WHERE balance >= 100 的条件里，看影响行数；或者用 SELECT ... FOR UPDATE 显式加锁。隔离级别保证的是一致性，不保证你的业务逻辑没有竞态。

小结

• ON CONFLICT、RETURNING、DISTINCT ON、generate_series 这些写法能显著减少应用层拼装，值得熟练。
• JSONB 优先于 JSON，@> 配合 GIN 是最常用的组合；jsonb_path_ops 更省空间但能力有取舍。
• 窗口函数保留明细做组内排名/累计，过滤要套子查询。
• 索引选型：B-tree 打底，GIN 管多值，GiST 管距离，BRIN 管有序大表；部分索引、表达式索引、覆盖索引是提效利器。
• EXPLAIN (ANALYZE, BUFFERS) 重点看估算与实际行数偏差、Buffers 命中、Sort 是否落盘、loops，别只盯 cost。
• 隔离级别决定快照粒度，长事务会拖住清理；并发正确性靠原子 SQL 和显式锁，不能指望隔离级别兜底。

SQL 和索引心里有数了，下一篇进入部署层面：流复制怎么做、备份怎么恢复、高可用选谁。