PostgreSQL 核心全过程原理

PostgreSQL 不能只学成“会写 SQL 的另一个 MySQL”。它真正难的地方在于：同一条 SQL 背后会经过进程、解析、重写、优化器、执行器、索引、heap tuple、MVCC 快照、WAL、VACUUM、统计信息和锁。

如果只背“PostgreSQL 有 MVCC、WAL、VACUUM”，面试能说两句，但项目里遇到慢查询、表膨胀、长事务、执行计划估算错误、写后读不一致，就不知道从哪里下手。

这一页把 PostgreSQL 从零到核心原理串起来：

1. 一个连接怎么进入 PostgreSQL。
2. 一条 select 怎么执行。
3. 一条 update 为什么会生成新 tuple。
4. MVCC 为什么依赖 xmin/xmax 和快照。
5. WAL 为什么能保证崩溃恢复。
6. VACUUM 为什么会影响性能。
7. B-tree、GIN、BRIN 到底适合什么查询。
8. EXPLAIN ANALYZE 怎么读。
9. 商业项目如何建模、加索引、排查问题。

学习目标

问题	学完后要能回答
PostgreSQL 和 MySQL 最大差异是什么	MVCC 实现、WAL、VACUUM、类型系统、扩展能力不同
SQL 执行链路是什么	Parser、Rewrite、Planner、Executor、Access Method、Heap
更新一行为什么不是原地覆盖	为了 MVCC 一致性读、回滚、并发读写
VACUUM 为什么重要	清理 dead tuple，避免表和索引膨胀
WAL 为什么先写	数据页刷盘前先写日志，宕机后可以重放恢复
索引怎么选	B-tree、GIN、GiST、BRIN 根据查询类型和数据分布选择
慢 SQL 怎么排查	EXPLAIN ANALYZE、估算行数、真实行数、Buffers、锁、膨胀、统计信息

PostgreSQL 总体架构

PostgreSQL 常见是多进程模型。每个客户端连接通常由一个后端进程处理，后台还有 WAL、checkpoint、autovacuum 等进程一起工作。

flowchart TD
    A["客户端连接"] --> B["Postmaster 主进程"]
    B --> C["Backend 后端进程"]
    C --> D["Parser / Rewriter / Planner / Executor"]
    D --> E["Buffer Manager"]
    E --> F["共享缓冲区 Shared Buffers"]
    F --> G["数据文件 Heap / Index"]
    C --> H["WAL 记录"]
    H --> I["WAL 文件"]
    B --> J["Autovacuum"]
    B --> K["Checkpointer / Background Writer"]

组件	作用	学不会会怎样
Backend 进程	处理一个客户端会话的 SQL	连接数过高时不知道为什么内存和进程压力大
Shared Buffers	缓存数据页和索引页	只会说“数据库慢”，不知道是否 IO 命中差
WAL	预写日志，支持恢复和复制	无法解释提交、恢复、主从复制
Autovacuum	自动清理 dead tuple 和更新统计	不知道表为什么越来越大
Planner	基于统计信息选择计划	不知道为什么有索引但不用
Executor	按计划执行算子	看不懂执行计划中的 Scan、Join、Sort

一条 SELECT 的全过程

示例 SQL：

select id, asset_no
from asset
where owner_id = 1001
order by created_at desc
limit 20;

执行链路：

flowchart TD
    A["客户端发送 SQL"] --> B["Backend 接收"]
    B --> C["Parser 解析语法"]
    C --> D["Rewriter 规则重写"]
    D --> E["Planner 生成候选计划"]
    E --> F["根据统计信息估算成本"]
    F --> G["选择执行计划"]
    G --> H["Executor 执行"]
    H --> I["Index Scan 或 Seq Scan"]
    I --> J["检查 tuple 可见性"]
    J --> K["排序 / Limit"]
    K --> L["返回结果"]

每一步的意义：

步骤	做什么	常见问题
Parser	词法、语法分析	SQL 语法错、表别名错
Rewriter	视图、规则重写	视图背后 SQL 复杂，实际执行比看起来重
Planner	枚举索引扫描、顺序扫描、Join 顺序	统计信息不准导致计划错误
Executor	执行节点树	Sort、Hash Join、Nested Loop 可能消耗大
Access Method	通过 B-tree、GIN 等访问索引	索引类型不适合查询
Heap	访问表数据 tuple	回表、可见性检查、膨胀影响扫描

为什么 Index Scan 也可能慢

PostgreSQL 的索引和 heap 是分开的。普通 B-tree 索引项里定位到的是表中 tuple 的位置，很多时候还需要访问 heap。

flowchart TD
    A["B-tree 索引"] --> B["找到 TID"]
    B --> C["访问 Heap Page"]
    C --> D["读取 tuple"]
    D --> E["检查 MVCC 可见性"]
    E --> F["返回可见行"]

即使字段都在索引中，PostgreSQL 的 Index Only Scan 也不等于永远不用访问 heap。它还要看 Visibility Map。

条件	结果
页被标记为 all-visible	可以更放心从索引返回，减少 heap 访问
页没有 all-visible	仍可能访问 heap 检查 tuple 可见性
VACUUM 跟不上	Visibility Map 更新不足，Index Only Scan 效果变差

这就是 PostgreSQL 和 MySQL 覆盖索引体验不完全一样的地方。PostgreSQL 必须考虑 MVCC 可见性。

一条 UPDATE 的全过程

示例：

update asset
set status = 'USED',
    updated_at = now()
where asset_no = 'A202607050001'
  and status = 'IDLE';

PostgreSQL 通常不会原地覆盖旧行，而是写出一个新 tuple 版本。

flowchart TD
    A["定位旧 tuple"] --> B["检查可见性和写锁"]
    B --> C["生成新 tuple"]
    C --> D["旧 tuple 设置 xmax"]
    D --> E["新 tuple 设置 xmin"]
    E --> F["写 WAL"]
    F --> G["事务提交"]
    G --> H["新版本对后续事务可见"]
    H --> I["VACUUM 后续清理旧版本"]

为什么不直接覆盖：

直接覆盖会怎样	PostgreSQL 的解决方式
老事务读不到旧值	保留旧 tuple，快照读旧版本
回滚没有旧值可恢复	WAL 和 tuple 版本支持恢复
读写更容易互相阻塞	读旧版本，写新版本
删除旧版本时机难判断	VACUUM 统一清理没人需要的版本

Tuple、xmin、xmax 和快照

PostgreSQL 表里保存的是 tuple。每个 tuple 可以理解为某行数据在某个时间点的版本。

字段	简化理解
xmin	创建这个 tuple 的事务 ID
xmax	删除或更新这个 tuple 的事务 ID
ctid	tuple 在表文件中的物理位置

更新前：

tuple(asset_no=A001, status=IDLE, xmin=10, xmax=空)

事务 20 更新后：

旧 tuple: asset_no=A001, status=IDLE, xmin=10, xmax=20
新 tuple: asset_no=A001, status=USED, xmin=20, xmax=空

查询时会生成快照。快照不是复制数据，而是一组可见性规则。

flowchart TD
    A["查询开始"] --> B["生成 Snapshot"]
    B --> C["扫描 tuple"]
    C --> D["检查 xmin 是否可见"]
    D --> E["检查 xmax 是否使它失效"]
    E --> F{"当前版本可见吗"}
    F -- "可见" --> G["返回"]
    F -- "不可见" --> H["跳过"]

这解释了为什么普通 select 不会读到未提交数据，也解释了为什么 PostgreSQL 能让普通读和写并发。

Read Committed 和 Repeatable Read

PostgreSQL 默认隔离级别是 Read Committed。

隔离级别	快照生成时机	现象
Read Committed	每条语句一个新快照	同一事务两次 select 可能看到别人已提交的新值
Repeatable Read	一个事务内复用快照	同一事务内多次 select 结果一致
Serializable	更强的串行化语义	可能因为冲突而事务失败，需要重试

Read Committed：

sequenceDiagram
    participant A as 事务A
    participant B as 事务B
    A->>A: select 看到 IDLE
    B->>B: update 为 USED
    B->>B: commit
    A->>A: 再 select 看到 USED

Repeatable Read：

sequenceDiagram
    participant A as 事务A
    participant B as 事务B
    A->>A: select 看到 IDLE
    B->>B: update 为 USED
    B->>B: commit
    A->>A: 再 select 仍看到 IDLE

项目里不要只背隔离级别名字，要知道它决定了“什么时候生成快照”和“同一事务内能不能看到别人新提交的数据”。

WAL 为什么是核心

WAL 是 Write-Ahead Logging，预写日志。核心规则是：

数据页真正刷盘前，描述这次修改的 WAL 必须先可靠写入。

flowchart TD
    A["修改 Shared Buffers 中的数据页"] --> B["生成 WAL 记录"]
    B --> C["提交时 WAL 刷盘"]
    C --> D["返回 commit 成功"]
    D --> E["数据页稍后刷盘"]
    E --> F["宕机后根据 WAL 重放"]

为什么这么设计：

方案	问题
每次提交都刷完整数据页	随机 IO 多，性能差
只改内存不写日志	宕机后已提交数据丢失
先写 WAL，再异步刷页	日志顺序写性能好，恢复可靠

WAL 的用途不只是崩溃恢复：

用途	说明
崩溃恢复	重放已提交修改
流复制	从库接收并重放主库 WAL
PITR	基础备份 + WAL 归档恢复到指定时间点
逻辑复制基础	变更可以被解析和发送

VACUUM 为什么决定长期性能

PostgreSQL 更新和删除会留下旧 tuple。旧 tuple 不能马上删，因为可能还有老事务需要看。

flowchart TD
    A["update/delete 产生 dead tuple"] --> B{"是否还有老事务需要"}
    B -- "有" --> C["暂时不能清理"]
    B -- "没有" --> D["VACUUM 清理或标记可复用"]
    D --> E["更新可见性信息"]
    E --> F["减少后续扫描成本"]

如果 VACUUM 跟不上：

问题	结果
dead tuple 堆积	表膨胀，扫描页变多
索引指向旧版本	索引膨胀，查询更多无效项
Visibility Map 不充分	Index Only Scan 仍要访问 heap
autovacuum 压力大	后台清理抢资源
长事务存在	旧版本不能清理

排查长事务：

select pid,
       usename,
       state,
       now() - xact_start as xact_age,
       query
from pg_stat_activity
where xact_start is not null
order by xact_age desc;

查看表统计：

select relname,
       n_live_tup,
       n_dead_tup,
       last_vacuum,
       last_autovacuum
from pg_stat_user_tables
order by n_dead_tup desc;

索引体系怎么选

PostgreSQL 不只有 B-tree。

索引	适合	不适合
B-tree	等值、范围、排序、唯一约束	JSON 包含、数组包含、全文检索
GIN	jsonb、数组、全文检索	高频更新且字段很大时成本高
GiST	空间、范围、相似性、PostGIS	普通等值查询不一定合适
BRIN	超大表，字段和物理顺序相关	随机分布字段
Hash	等值查询	通用性不如 B-tree

商业资产表例子：

create table asset (
  id bigserial primary key,
  asset_no varchar(64) not null,
  owner_id bigint not null,
  status varchar(20) not null,
  attrs jsonb not null default '{}',
  created_at timestamp not null default now(),
  updated_at timestamp not null default now(),
  unique (asset_no)
);

create index idx_asset_owner_created
on asset(owner_id, created_at desc);

create index idx_asset_attrs_gin
on asset using gin(attrs);

查询某个用户最近资产：

select id, asset_no, status
from asset
where owner_id = 1001
order by created_at desc
limit 20;

查询 JSON 属性：

select id, asset_no
from asset
where attrs @> '{"deviceType": "CT"}';

为什么分别建不同索引：

1. owner_id + created_at 是典型等值 + 排序，B-tree 合适。
2. attrs @> 是 JSONB 包含查询，GIN 更合适。
3. 如果所有查询都只建 B-tree，JSON 查询可能仍然很慢。
4. 如果所有字段都建 GIN，写入和更新成本会很高。

EXPLAIN ANALYZE 怎么读

PostgreSQL 优化不能只看有没有索引，要看真实执行计划。

explain analyze
select id, asset_no
from asset
where owner_id = 1001
order by created_at desc
limit 20;

重点看：

字段	含义	排查意义
Seq Scan	顺序扫描全表	大表上要警惕
Index Scan	通过索引定位，再访问 heap	常见
Index Only Scan	尽量只读索引	还要看 Heap Fetches
Bitmap Index Scan	先从索引构建位图	多条件或返回较多行常见
Nested Loop	外层每行驱动内层查找	外层行多时可能爆炸
Hash Join	构建哈希表再连接	需要内存，可能溢出
Sort	排序	大排序可能吃内存和磁盘
actual time	真实耗时	比估算更可信
rows	估算或真实行数	估算和真实差距大说明统计信息问题
Buffers	缓冲命中和读取	判断是否 IO 压力

建议使用：

explain (analyze, buffers)
select id, asset_no
from asset
where owner_id = 1001
order by created_at desc
limit 20;

注意：EXPLAIN ANALYZE 会真实执行 SQL。生产环境对大查询、写 SQL 要谨慎。

慢 SQL 排查流程

flowchart TD
    A["发现 SQL 慢"] --> B["explain analyze buffers"]
    B --> C{"估算行数和真实行数差距大吗"}
    C -- "是" --> D["更新统计信息 / 调整统计目标"]
    C -- "否" --> E{"扫描方式合理吗"}
    E -- "Seq Scan 大表" --> F["补索引或缩小范围"]
    E -- "Index Scan 仍慢" --> G["看 heap 访问、回表、膨胀"]
    G --> H{"dead tuple 多吗"}
    H -- "是" --> I["查长事务和 autovacuum"]
    H -- "否" --> J["看排序、Join、IO、锁等待"]

常用命令：

analyze asset;

select *
from pg_stat_activity
where wait_event is not null;

select relname, n_dead_tup, last_autovacuum
from pg_stat_user_tables
order by n_dead_tup desc;

商业场景：资产检索与灵活属性

假设医疗资产平台需要管理设备资产：

1. 固定字段：资产编号、所属医院、状态、创建时间。
2. 灵活字段：设备型号、科室、厂商、维保属性。
3. 查询场景：按医院分页、按状态统计、按 JSON 属性检索、导出报表。

推荐设计：

create table medical_asset (
  id bigserial primary key,
  asset_no varchar(64) not null,
  hospital_id bigint not null,
  status varchar(20) not null,
  attrs jsonb not null default '{}',
  created_at timestamp not null default now(),
  updated_at timestamp not null default now(),
  unique (asset_no)
);

create index idx_asset_hospital_created
on medical_asset(hospital_id, created_at desc);

create index idx_asset_status
on medical_asset(status);

create index idx_asset_attrs_gin
on medical_asset using gin(attrs);

为什么这样设计：

设计	原因
固定字段单独建列	高频过滤和排序更稳定
灵活字段放 jsonb	不同医院属性差异大
JSONB 配 GIN	支持属性包含查询
医院 + 时间联合索引	支持列表页和游标分页
不把所有字段都塞 JSON	重要查询难优化，约束也弱

错误设计：

错误	后果
所有字段都放 JSON	约束弱，索引复杂，统计信息差
不管 VACUUM	更新频繁后表膨胀
大报表长事务	阻止旧版本清理
只看估算计划	真实执行可能完全不同

常见坑

坑	后果	正确做法
把 PostgreSQL 当 MySQL 使用	忽略 VACUUM、JSONB、GIN、执行计划差异	按 PG 机制设计
长事务不提交	VACUUM 清理受阻	事务短小，报表避免长事务
大量更新 JSONB 大字段	tuple 和索引膨胀	拆出高频变更字段
以为 Index Only Scan 一定不访问表	MVCC 可见性仍可能访问 heap	关注 Visibility Map 和 Heap Fetches
GIN 索引滥用	写入变慢，索引变大	只给真实高频 JSON 查询建
不更新统计信息	Planner 估算错误	analyze，必要时调统计目标
从库读强一致数据	流复制可能延迟	写后读主库或做延迟判断

面试标准回答

PostgreSQL 一条 SQL 怎么执行

PostgreSQL 客户端连接通常由一个后端进程处理。SQL 进入后会经过 Parser 解析语法、Rewriter 做规则和视图重写、Planner 根据统计信息生成并选择成本较低的执行计划，Executor 按计划执行。执行过程中可能通过 B-tree、GIN 等索引访问，也可能顺序扫描 heap。读取 tuple 时还要根据 MVCC 快照判断版本是否可见。执行计划是否合理要用 EXPLAIN ANALYZE 结合真实行数、耗时和 Buffers 判断。

PostgreSQL MVCC 怎么工作

PostgreSQL 的 MVCC 通过 tuple 多版本实现。每个 tuple 有 xmin、xmax 等事务信息。更新时通常不是原地覆盖，而是写出新 tuple，并把旧 tuple 标记为被更新。查询开始时生成快照，根据快照判断某个 tuple 版本是否可见，所以普通 select 可以读旧版本，不阻塞 update。旧版本不能马上删除，因为可能还有老事务需要它，后续由 VACUUM 清理。

WAL 和 VACUUM 分别解决什么

WAL 解决可靠性问题。数据页真正刷盘前，描述修改的 WAL 要先落盘，事务提交后即使宕机，也可以通过 WAL 重放恢复。VACUUM 解决 MVCC 带来的旧版本清理问题，更新和删除产生的 dead tuple 需要在没有事务需要后清理或标记空间可复用，否则表和索引会膨胀，查询扫描更多页，性能下降。

关联知识点

• PostgreSQL 总览
• 基础 SQL
• 架构与存储
• 索引与优化器
• 事务与锁
• MVCC、VACUUM 与 WAL 原理
• 性能排查
• PostgreSQL 面试

本章小结

PostgreSQL 的学习主线不是背语法，而是理解“SQL 怎么执行、tuple 怎么多版本、WAL 怎么保证恢复、VACUUM 怎么治理旧版本、索引和统计信息怎么影响计划”。一旦把这些过程串起来，你就能解释为什么更新会膨胀、为什么长事务危险、为什么 Index Only Scan 也可能访问 heap、为什么有索引但仍然慢，以及商业系统里什么时候该用 JSONB、GIN、BRIN、分区、主从和备份恢复。