PostgreSQL 索引与 MVCC
PostgreSQL 系统讲解第四篇：性能两根支柱——索引家族与 MVCC/VACUUM。MVCC 部分和 MySQL 的 InnoDB 对照着看，理解差异比背结论有用。
索引家族：按场景选类型
MySQL 主要就是 B-tree（+FULLTEXT），PG 的索引类型多得多：
| 类型 | 适用 | 场景 |
|---|---|---|
| B-tree（默认） | 可排序标量 | 等值/范围/排序，绝大多数查询 |
| GIN（倒排） | 复合值内部元素 | jsonb / 数组 / 全文检索——02 篇的三件套全靠它 |
| GiST | 广义搜索树 | 地理（PostGIS）、范围类型、kNN 排序 |
| BRIN（块区间） | 物理顺序相关的列 | 时序大表（按时间追加）的超小索引 |
| Hash | 等值 | 较少用，B-tree 覆盖大部分需求 |
CREATE INDEX idx_users_tags ON users USING GIN (tags);
CREATE INDEX idx_events_data ON events USING GIN (data jsonb_path_ops); -- 更小的 GIN 变体
CREATE INDEX idx_logs_ts ON logs USING BRIN (created_at);
CREATE UNIQUE INDEX ON users (lower(email)); -- 表达式索引：按小写邮箱去重
CREATE INDEX ON orders (user_id, created_at DESC); -- 多列 + 方向
CREATE INDEX idx_active ON users (email) WHERE deleted_at IS NULL; -- 部分索引
对照 MySQL 的收获：部分索引和表达式索引是 MySQL 做不到（或很别扭）的日常工具；EXPLAIN ANALYZE 对应 MySQL 的 EXPLAIN ANALYZE（8.0+），看真实执行统计。
EXPLAIN ANALYZE 怎么读
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 1 ORDER BY created_at DESC;
读的顺序：树状计划从内往外；重点看三样——
Seq Scan（顺序扫全表）出现在大表上 = 索引没走上的警报rows估算 vsactual rows：差几个数量级 = 统计信息过时 →ANALYZE 表名Sort Method: external merge（外排溢出到磁盘）= 排序没被索引吃掉 + work_mem 不够
MVCC：PG 与 InnoDB 的根本分歧
两边都是 MVCC（多版本并发控制，读不阻塞写），但旧版本存放的位置不同：
| MySQL InnoDB | PostgreSQL | |
|---|---|---|
| 旧版本存哪 | undo log（回滚段） | 表本身（每行带 xmin/xmax 系统列） |
| 读旧版本 | 从 undo 构建回来 | 直接找可见的旧版本行 |
| 后果 | 回滚段维护成本 | 表里堆积死元组（dead tuples），要靠 VACUUM 回收 |
xmin=创建该行的事务号，xmax=删除/更新它的事务号——PG 的 UPDATE = 插入新版本行 + 旧版本打上 xmax，没有原地更新。
VACUUM：PG 独有的日常
死元组不清理，表和索引会膨胀（表比"活数据"大好几倍），查询变慢。VACUUM 就是扫表回收死元组空间的维护操作：
- autovacuum 默认开启，自动后台做，一般不用管——但要知道它存在
VACUUM FULL才会真正归还磁盘空间（锁表重写，大表慎用；autovacuum 普通模式不锁读写）- 长事务是死元组的头号制造者：一个久不提交的事务会让 VACUUM "够不着"它之后的死元组——别挂着事务不提交（对照 SQLite 篇 WAL 节的事务纪律，是同一个道理）
- 事务号回卷（wraparound）是 PG 特有的深度话题，autovacuum 会强制冻结——知道有这回事即可
💡 我的对照记忆钩子：InnoDB 把"清理"藏在 undo log 的生命周期里，PG 把它摆上台面叫 VACUUM——两种 MVCC 实现，一个主动回收一个后台回收，代价模型不同而已。
事务与隔离级别
BEGIN;
-- ISOLATION LEVEL: READ COMMITTED（默认）/ REPEATABLE READ / SERIALIZABLE
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;
- 默认 READ COMMITTED 与 MySQL 一致
- PG 的 SERIALIZABLE 是真·可串行化（SSI 实现，检测读写冲突自动回滚），MySQL 的可串行化靠锁——严格场景 PG 这边更现代
- 遇到
ERROR: could not serialize重试即可（串行化冲突是设计使然，不是 bug）
💬 评论