PostgreSQL 实现原理深度剖析：高性能数据库引擎的核心机制

理解 PostgreSQL 的原理，不只是为了面试。知道它的 MVCC 是"保留旧版本"而不是"undo 回滚"，你才会明白为什么它必须有 VACUUM、为什么长时间不清理的表会持续膨胀。本文按架构、存储、MVCC、WAL、优化器、索引逐层拆解，每一节都给出可以亲手敲命令验证的观察方法——原理只有能被验证，才算真的理解。

视频：养活国内大半自研数据库团队的PostgreSQL是什么？

一、整体架构：多进程模型

PostgreSQL 采用多进程（process-based）架构，每个客户端连接对应一个独立的操作系统进程。这和 MySQL 的多线程模型是根本差异。

多进程的代价是每个连接占更多内存（进程隔离更彻底），好处是隔离性强——某个连接里的查询把内存写坏了，其他连接不受影响。这也是 PG 必须配连接池（pgbouncer）的原因：几百个连接就是几百个进程。

graph TD
    Client[客户端应用] -->|TCP/IP :5432| Postmaster[Postmaster 主进程]
    Postmaster -->|fork| Backend1[Backend 服务进程 1]
    Postmaster -->|fork| Backend2[Backend 服务进程 2]
    Postmaster -->|fork| Backend3[Backend 服务进程 3]

    subgraph SharedMem[共享内存 Shared Memory]
        SharedBuffer[Shared Buffer 共享缓冲区]
        WALBuffer[WAL Buffer 日志缓冲区]
        Backend1 <--> SharedBuffer
        Backend2 <--> SharedBuffer
        Backend3 <--> SharedBuffer
    end

    subgraph BgProc[关键后台进程]
        BgWriter[BgWriter 后台写入器]
        Checkpointer[Checkpointer 检查点进程]
        WALWriter[WAL Writer 日志写入器]
        Autovacuum[Autovacuum 自动清理]
    end

    SharedBuffer --> BgWriter
    SharedBuffer --> Checkpointer
    WALBuffer --> WALWriter
    SharedBuffer --> Autovacuum
    BgWriter -->|刷脏页| DataFile[(数据文件)]
    WALWriter -->|刷日志| WALFile[(WAL 文件)]

各组件职责：

组件	职责
Postmaster	主进程。监听端口、接受连接、为每个连接 `fork` 一个 Backend。它不是 PID 1——PID 1 是操作系统的 init/systemd，Postmaster 只是 PG 自己的主进程
Backend	每个客户端连接一个，负责解析、规划、执行这个连接的 SQL
Shared Buffer	所有进程共享的数据页缓存（大小由 `shared_buffers` 控制）
WAL Buffer	写前日志的内存缓冲
BgWriter	后台把脏页刷盘，分摊 Checkpoint 的压力
Checkpointer	定期做检查点，把内存脏页和 WAL 落盘，缩短崩溃恢复时间
WAL Writer	把 WAL Buffer 刷进 WAL 文件
Autovacuum	自动回收死元组、更新统计信息

1.1 双缓存架构

PG 有两层缓存：自己的 shared_buffers + 操作系统的 page cache。这是它常被讨论的一个特点——数据可能同时存在于两处。

所以 shared_buffers 不是越大越好：设成内存的 70% 反而会挤占 OS cache，得不偿失。常规建议是物理内存的 25%（PG 官方文档的建议），比 MySQL 的 70% 保守得多。

1.2 【观察】验证这些进程真的存在

# 看 PG 的所有进程（会看到 postmaster + 每个连接的 backend + 后台进程）
ps -ef | grep postgres

-- 从数据库内部看后台进程（background worker）
SELECT pid, backend_type FROM pg_stat_activity ORDER BY backend_type;

  pid  |      backend_type
-------+-----------------------
 12034 | autovacuum launcher
 12035 | background writer
 12036 | checkpointer
 12037 | walwriter
 12045 | client backend
 12046 | client backend

backend_type 会明确告诉你每个进程在干什么——client backend 对应客户端连接，checkpointer/walwriter 是后台进程。这就是多进程架构的直接证据。

二、存储：页面与元组

PG 的表是堆表（heap table）——数据无序存放，靠索引定位。

标准页面大小 8KB。页面内部结构：

classDiagram
    class PageHeaderData {
        +uint16 pd_lsn (最后修改的 WAL 位置)
        +uint16 pd_lower (空闲空间起始)
        +uint16 pd_upper (空闲空间结束)
        +uint16 pd_special (特殊空间起始)
    }
    class ItemIdData {
        +uint32 行指针(偏移+长度+标志)
    }
    class HeapTupleHeaderData {
        +uint32 t_xmin (创建事务ID)
        +uint32 t_xmax (删除事务ID)
        +ItemPointerData t_ctid (物理位置)
        +uint16 t_infomask (标志位)
    }
    class TablePage {
        PageHeaderData header
        ItemIdData[] item_pointers (从前往后生长)
        FreeSpace 空闲空间
        HeapTuple[] tuples (从后往前生长)
    }
    TablePage "1" *-- "1" PageHeaderData
    TablePage "1" *-- "n" ItemIdData : 行指针数组
    TablePage "1" *-- "n" HeapTuple : 实际元组

关键设计：索引不直接指向数据行，而是指向"行指针"（ItemId）。

这样做的好处是——页面内部整理碎片（移动元组位置）时，外部索引完全不用改，只需更新行指针的偏移量。

2.1 HOT 更新

一个直接的应用是 HOT（Heap-Only Tuple）更新：如果更新没有修改任何索引列，且新版本能放进同一页面，PG 就在原页面内做一个"新版本指回旧版本"的链，不更新索引。这省掉了一次索引插入。

2.2 TOAST：大字段怎么办

8KB 的页面装不下超大字段（长 TEXT、大 JSONB）。PG 的处理叫 TOAST：

先尝试压缩变长字段
还是太大就切片，存到独立的 TOAST 表里
主表里只留一个指针（20 字节左右）

TOAST 表的名字形如 pg_toast.pg_toast_16385。为什么平时感受不到它？因为读取时 PG 会自动拼回原值——但这也意味着，SELECT * 会把 TOAST 字段全部解压并读取，代价可能很高。

2.3 辅助文件：FSM 与 VM

文件	作用
FSM（Free Space Map）	记录每个页面还剩多少空闲空间，插入时快速找到能放下新行的页面，避免全表扫描
VM（Visibility Map）	记录每个页面是否"所有元组对所有事务都可见"

VM 的用途是 Index-Only Scan：如果索引扫描需要的列都在索引里，且 VM 显示对应页面全可见，PG 就能完全不回表，直接从索引返回数据。这是 PG 的重要优化，也是为什么 VACUUM 之后查询会变快——VACUUM 更新了 VM。

2.4 【观察】亲手看页面和元组

CREATE EXTENSION IF NOT EXISTS pageinspect;      -- 页面与元组结构
CREATE EXTENSION IF NOT EXISTS pg_visibility;    -- 可见性图 (VM)
CREATE EXTENSION IF NOT EXISTS pg_freespacemap;  -- 空闲空间图 (FSM)

-- 看页头
SELECT * FROM page_header(get_raw_page('employees', 0));

-- 看行指针与元组（t_xmin / t_xmax / t_ctid 都在这）
SELECT lp, lp_off, lp_len, t_xmin, t_xmax, t_ctid
FROM heap_page_items(get_raw_page('employees', 0))
LIMIT 5;

-- VM 和 FSM 的状态
SELECT * FROM pg_visibility('employees') LIMIT 5;
SELECT * FROM pg_freespace('employees') LIMIT 5;

这个 heap_page_items 的输出，就是上面那张页面结构图的真实数据——t_xmin/t_xmax 正是 MVCC 判断可见性的依据。

三、MVCC：版本链与可见性

这是 PG 和 MySQL 差异最大的地方，也是最值得理解的部分。

PG 的 MVCC 靠"保留旧版本"实现：更新一行，不是覆盖原行，而是写入一个全新的行版本，然后把旧版本标记为已删除。

graph TB
    subgraph Chain[同一行的版本链]
        V1["版本1<br/>xmin=100 (已提交)<br/>xmax=200 (已提交)"]
        V2["版本2<br/>xmin=200 (已提交)<br/>xmax=300 (运行中)"]
        V3["版本3<br/>xmin=300 (运行中)<br/>xmax=0 (未删除)"]
    end
    V1 -->|ctid| V2
    V2 -->|ctid| V3
    V1 -.- L1[已过期]
    V2 -.- L2[当前可见]
    V3 -.- L3[未提交，不可见]
    classDef ok fill:#d5f5e3,stroke:#2ecc71,stroke-width:2px
    class V2 ok

每个元组头部有两个关键字段：

xmin：创建这个版本的事务 ID
xmax：删除/更新这个版本的事务 ID（0 表示还没被删）

可见性判断规则（简化版）：

一个元组对当前事务可见 ⟺ xmin 已提交 且 （xmax == 0 或 xmax 未提交）

也就是说：我只能看到"在我开始前已提交、且在我开始前没被删掉"的数据。这里的"已提交"要查事务状态（clog，提交日志），不只是比较 ID 大小。

3.1 与 MySQL 的根本差异

	PostgreSQL	MySQL / InnoDB
MVCC 实现	保留旧版本在表内	旧版本写进 undo log
空间回收	靠 VACUUM 显式清理	Purge 线程自动清理 undo
表膨胀	可能发生（死元组堆积）	相对不会
读是否阻塞写	不阻塞	不阻塞
长事务影响	阻止死元组回收 → 表膨胀	撑大 undo 表空间

这个差异的直接后果：PG 的表会"膨胀"（bloat），必须靠 VACUUM 治理；MySQL 不会（代价是 undo 表空间可能涨）。这就是 PG 必须有 autovacuum 的根因。

3.2 死元组与膨胀

一个被 DELETE 或 UPDATE 掉的旧版本叫死元组（dead tuple）。它占的空间不会自动归还，要等 VACUUM 标记为可重用。

长事务是膨胀的头号元凶：一个开了几小时的事务会让所有比它更新的死元组都无法回收（因为可能还需要看到它们）。

3.3 【观察】看版本链与死元组

-- ctid 就是元组的物理位置（页号, 行号）
SELECT ctid, xmin, xmax, name FROM employees LIMIT 3;

 ctid  | xmin  | xmax | name
-------+-------+------+------
 (0,1) | 12045 |    0 | 张伟
 (0,2) | 12045 |    0 | 李娜

-- 更新一行，再查一次：ctid 变了，旧位置留下死元组
UPDATE employees SET salary = salary + 1 WHERE name = '张伟';
SELECT ctid, xmin, xmax, name FROM employees WHERE name = '张伟';
-- 新版本 ctid 不同；旧 ctid 那行现在 xmax 非 0

-- 看死元组堆积情况
SELECT relname, n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
WHERE relname = 'employees';

执行 VACUUM employees; 后再查 n_dead_tup，会看到它归零——这就是 VACUUM 在做什么的最好证明。

四、WAL 与崩溃恢复

PG 保证不丢数据的基石是 WAL（Write-Ahead Log，预写日志）。原则一句话：

对数据文件的修改，必须先写日志，再（稍后）写数据。

sequenceDiagram
    participant Tx as 事务
    participant WALBuf as WAL Buffer
    participant WALFile as WAL 文件(磁盘)
    participant Buf as Shared Buffer
    participant DataFile as 数据文件(磁盘)

    Note over Tx,WALFile: 预写日志：日志先行
    Tx->>WALBuf: 1. 写变更日志 (XLogRecord)
    Tx->>Buf: 2. 改内存中的数据页(变成脏页)
    Tx->>WALBuf: 3. 写 Commit Record
    WALBuf->>WALFile: 4. 刷盘 (fsync)
    WALFile-->>Tx: 5. 提交成功
    Note over Buf,DataFile: 此时数据页可能还在内存，没落盘
    loop Checkpoint / BgWriter
        Buf->>DataFile: 6. 异步刷脏页
    end

为什么这样能保证不丢：提交时只要日志落了盘，就算随后断电、数据页没来得及写，重启后也能用 WAL 重放，把数据恢复出来。

4.1 LSN：日志序列号

每条 WAL 记录都有 LSN（Log Sequence Number）——一个不断增长的字节位置，标记日志写到哪了。

关键关系：只要「数据页里记录的 LSN」≤ 「已落盘的 WAL 的 LSN」，这个数据页就是安全的（可以从 WAL 重放出来）。

4.2 Checkpoint

Checkpoint 做两件事：

把 Shared Buffer 里所有脏页刷到数据文件
记录一个redo 起点——崩溃恢复从这点开始重放 WAL，不需要从头

Checkpoint 越频繁，恢复越快，但刷盘 IO 压力越大。这是 checkpoint_timeout / max_wal_size 要权衡的点。

4.3 Full Page Writes：防止半页写

操作系统崩溃时，一个 8KB 页可能只写了一半（torn page / 半页写）。这样的页是无法用 WAL 重放修复的（因为 WAL 记录的是"增量修改"）。

PG 的解法是 full_page_writes：Checkpoint 之后，每个页面第一次被修改时，把整个页面内容完整记进 WAL。

好处：即使发生半页写，也能用 WAL 里的完整页面覆盖修复
代价：WAL 体积会明显增大（每个页第一次改动都要记 8KB）

所以 full_page_writes 默认是 on 的——除非你用的文件系统/存储能保证原子写（很少见），否则别关。

4.4 【观察】验证 WAL 在工作

-- 当前 WAL 写入位置
SELECT pg_current_wal_lsn();

-- 做一次写入，再看 LSN 变大
CREATE TABLE wal_demo(id int);
SELECT pg_current_wal_lsn();      -- 比上面大

-- 当前 WAL 文件列表
SELECT * FROM pg_ls_waldir() ORDER BY modification DESC LIMIT 5;

-- 检查点与刷盘统计
SELECT checkpoints_timed, checkpoints_req, buffers_checkpoint, buffers_backend
FROM pg_stat_bgwriter;

-- WAL 生成量统计（PG 14+ 用 pg_stat_wal，不是 pg_stat_database）
SELECT wal_records, wal_fpi, pg_size_pretty(wal_bytes) AS wal_size, wal_buffers_full
FROM pg_stat_wal;

checkpoints_req（因请求触发的检查点）远多于 checkpoints_timed（定时触发的），说明 max_wal_size 设小了——这是很实用的调优信号。

-- 看某条 WAL 记录在说什么
SELECT * FROM pg_get_wal_records_info(
    pg_current_wal_lsn() - 1000, pg_current_wal_lsn()
) LIMIT 5;   -- 需要 pg_walinspect 扩展（PG 13+）

五、查询处理与优化器

一条 SQL 到结果，要经过五个阶段：

sequenceDiagram
    participant C as 客户端
    participant P as 解析器 Parser
    participant A as 分析器 Analyzer
    participant R as 重写器 Rewriter
    participant PL as 规划器 Planner
    participant E as 执行器 Executor
    C->>P: SQL 文本
    P->>A: 原始解析树
    A->>R: 查询树(语义已确认)
    R->>PL: 重写后的查询树(视图展开等)
    PL->>PL: 逻辑优化 + 物理优化
    Note right of PL: 基于成本的优化(CBO)<br/>连接顺序/连接算法选择
    PL->>E: 执行计划(Plan Tree)
    E->>E: 递归执行各算子(Scan/Join/Sort)
    E-->>C: 结果集

5.1 基于成本的优化器（CBO）

规划器不追求"最优"，而是"估计成本最低"。成本 = 各算子的 IO 成本 + CPU 成本，参数在 postgresql.conf 里（seq_page_cost、random_page_cost、cpu_tuple_cost 等）。

它的估计依赖"统计信息"——每个表的行数分布、列的直方图、不同值的个数。这些由 ANALYZE 收集，过期的统计信息会导致选错计划，这就是"为什么有时加了索引却不用"的常见原因。

5.2 GEQO：连接表太多时

当一条查询 JOIN 的表超过 geqo_threshold（默认 12）时，穷举所有连接顺序的组合数会爆炸。PG 会自动切换到遗传算法（GEQO），找一个"足够好"的顺序，而不是最优的。

5.3 执行器的迭代模型

执行器采用火山模型（Volcano / iterator model）：每个算子（Scan、Join、Sort）实现 next() 接口，向上层返回一行。上层不断调 next() 拉数据。结构清晰、易扩展，但每行一次函数调用有额外开销——这也是 JIT 想解决的问题。

5.4 并行查询

PG 9.6+ 支持并行：规划器在计划里插入 Gather 节点，由多个 worker 进程并行扫描/连接/聚合，结果汇总到 leader。

只有"代价足够高"且"安全"（如不含 VOLATILE 函数）的查询才会并行。相关参数：max_parallel_workers_per_gather、parallel_setup_cost。

5.5 JIT

对复杂的大查询（不是简单点查），PG 可以用 LLVM 把表达式求值编译成机器码，减少每行解释执行的开销。参数是 jit = on（PG 12 起默认开）。

注意 JIT 有代价：编译本身耗时。对简单小查询开启 JIT 反而更慢。所以 PG 用 jit_above_cost 等阈值控制——只有预估成本足够高才编译。

5.6 【观察】看穿优化器

-- 实际执行 + 缓存统计（调优标配）
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT d.name, count(*)
FROM employees e JOIN departments d ON d.id = e.department_id
GROUP BY d.name;

-- 看统计信息是否新鲜
SELECT relname, last_analyze, last_autoanalyze, n_mod_since_analyze
FROM pg_stat_user_tables WHERE relname IN ('employees','departments');

-- 看某列的统计分布
SELECT * FROM pg_stats WHERE tablename = 'employees' AND attname = 'department';

-- 忘记关 JIT 时能明显看到（简单查询被 JIT 拖慢）
SET jit = on;
EXPLAIN ANALYZE SELECT count(*) FROM employees;
SET jit = off;
EXPLAIN ANALYZE SELECT count(*) FROM employees;   -- 对比 JIT 的耗时

计划里最该看的：rows=（预估行数）与实际行数的差距。差一个数量级，就是统计信息或估算模型有问题。

六、索引机制

6.1 B-tree：Lehman & Yao 算法

PG 的 B-tree 用了 Lehman & Yao 的并发 B-tree设计，核心是每个节点有一个右向链接（right-link）：

graph TD
    Root[Root Node] --> I1[Internal Node]
    Root --> I2[Internal Node]
    I1 --> L1[Leaf 1<br/>1..100]
    I1 --> L2[Leaf 2<br/>101..200]
    I2 --> L3[Leaf 3<br/>201..300]
    L1 -.->|Right-Link| L2
    L2 -.->|Right-Link| L3
    classDef leaf fill:#e8f8f5,stroke:#1abc9c
    class L1,L2,L3 leaf

右向链接解决什么问题：当一个节点分裂时，正在读它的其他事务不需要加锁等待——它沿着右向链接就能找到新的正确节点。这让 PG 的 B-tree 在高并发读写下表现出色。

6.2 索引类型全景

类型	数据结构	适合什么
B-tree	平衡树	绝大多数场景：`=` `>` `<` `BETWEEN` `ORDER BY`
GIN	倒排索引	`JSONB` 包含、数组、全文检索（多值字段的首选）
GiST	通用搜索树	地理数据（PostGIS）、几何重叠、范围类型
SP-GiST	空间分区树	非平衡数据：URL 前缀、电话号码前缀
BRIN	块范围索引	时序数据、按物理顺序写入的超大表（索引极小）

选型直觉：

普通列 → B-tree
JSONB / 数组 / 全文 → GIN
地图 / 几何 → GiST
几十亿行的时序日志 → BRIN

6.3 Index-Only Scan 依赖 VM

前面 §2.3 提到的 VM 在这里落地：索引扫描 + VM 全可见页 = 不回表。这是 PG 里性能提升最明显的优化之一。

所以 VACUUM 不只回收空间，还会让索引扫描变快——因为它更新了 VM。这也解释了为什么"刚导入完大量数据、还没 VACUUM 时，查询比 VACUUM 后慢"。

6.4 【观察】索引是否被用

-- 每个索引被扫描的次数（idx_scan=0 的索引可能是多余的）
SELECT relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan;

-- 对比回表与否：看计划的 "Heap Fetches"
EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM employees WHERE id > 100;
-- Index Only Scan 且 Heap Fetches: 0 → VM 生效，没回表

Heap Fetches: 0 是 Index-Only Scan 真正生效的标志。如果这个数字很大，说明 VM 没更新（该 VACUUM 了）。

七、分区与复制

7.1 分区剪枝

分区表把一张逻辑表拆成多个物理表，查询时规划器能只扫描相关分区（partition pruning）：

graph TD
    Parent[主表 sales<br/>PARTITION BY RANGE sale_date] --> P1[分区 sales_2026_q1]
    Parent --> P2[分区 sales_2026_q2]
    Parent --> P3[分区 sales_2026_q3]
    Q["查询: WHERE sale_date = '2026-02-15'"] -.->|只扫这个| P1
    Q -.->|跳过| P2
    Q -.->|跳过| P3

分区还带来一个运维优势：删历史数据用 DROP TABLE 分区，而不是 DELETE——前者是元数据操作，瞬间完成；后者要逐行删 + VACUUM。

7.2 物理复制 vs 逻辑复制

graph TB
    subgraph Physical[物理流复制 — 用于 HA]
        M1[主库] -->|WAL 流| S1[备库<br/>整个实例只读副本]
    end
    subgraph Logical[逻辑复制 — 用于数据同步]
        M2[发布端 Publisher] -->|逻辑解码| S2[订阅端 Subscriber<br/>表级粒度]
    end

	物理复制	逻辑复制
粒度	整个实例	表级
备库可用性	只读（可开热备查询）	可读写
跨大版本	不可以	可以
跨操作系统/架构	不可以	可以
典型用途	高可用、故障切换、只读扩展	数据汇聚、版本升级、微服务同步

7.3 【观察】复制状态

-- 主库：看有哪些备库在连、延迟多少
SELECT client_addr, state, sync_state, replay_lag FROM pg_stat_replication;

-- 备库：看回放进度
SELECT status, receive_start_lsn, replay_lsn, replay_lag FROM pg_stat_wal_receiver;

八、PostgreSQL vs MySQL 核心对比

维度	PostgreSQL	MySQL / InnoDB	关键差异
进程模型	多进程	多线程	PG 隔离强但连接开销大（必须上连接池）；MySQL 连接更轻
MVCC	表内保留旧版本	undo log 回滚段	PG 需 VACUUM 回收、可能膨胀；MySQL 由 Purge 线程处理
Join 算法	Nested Loop / Hash / Merge	长期只有 Nested Loop（8.0.18+ 才有 Hash Join）	PG 的复杂多表关联（OLAP）能力更强
数据类型	JSONB（可索引）、数组、范围、网络	JSON（较基础）、GIS 较弱	PG 原生支持更丰富的数据模型
索引类型	B-tree/GIN/GiST/SP-GiST/BRIN	主要是 B-tree + 全文	PG 可按数据类型选最合适的索引结构
扩展能力	`CREATE EXTENSION` 深度可扩展	插件体系较受限	PG 能"长成"时序库、向量库、GIS 库
许可证	PostgreSQL License（类 BSD）	GPL	PG 商业化更自由

选型建议（不是非此即彼）：

复杂查询、分析报表、GIS、JSON 文档、需要扩展 → PostgreSQL
简单点查、超高并发、团队 DBA 储备偏 MySQL → MySQL
两者都能扛住绝大多数业务。选团队更熟的那个，通常比选"理论更优"的那个更明智。

总结

把 PG 的原理压缩成三条主线：

多进程架构 → 隔离性好、连接贵 → 必须用连接池
MVCC 靠保留旧版本 → 读写不互相阻塞 → 必须靠 VACUUM 回收，否则表膨胀（且 Index-Only Scan 依赖 VM 更新）
WAL 日志先行 → 提交即安全、崩溃可恢复 → checkpoint 频率、full_page_writes 是性能与安全的权衡点

理解了这三条，PG 的绝大多数"奇怪行为"都能自己解释：为什么长事务会让表膨胀、为什么 VACUUM 后查询变快、为什么 WAL 会突然涨得很快。

读原理最有价值的方式，是边读边验证。本文每节的【观察】命令都可以直接粘进 psql 跑一遍——看到 heap_page_items 里的 t_xmin、看到 VACUUM 后 n_dead_tup 归零、看到 Index Only Scan 的 Heap Fetches: 0，原理才算真正落到你手里。