PostgreSQL 体系结构：一条 SQL 走完的四个世界

如果你已经熟悉 MySQL，那 PG 不用从零学——大部分概念是通的，但底层实现几乎每一处都不一样，而且每一处不一样都会在实际运维里变成具体问题。

这篇文章只讲一件事：一条 SQL 从客户端进来，到数据落到磁盘，中间经过了什么。把这条链路走通，后面所有的「为什么」都有地方挂。

▶ 打开第一辑交互版图解

进程模型、堆表与回表、MVCC 行内多版本、VACUUM 膨胀、事务 ID 回卷——五个考点都能动手切。

先把版本说清楚

这件事在 2026 年 11 月这个时间点很关键：

后面讲参数和命令时，我以 18 为准，涉及 19 的改动会单独标注。网上的 PG 教程有一大半写于 9.x / 10 时代，那时候还没有声明式分区、没有 pg_stat_progress_vacuum、没有并行 vacuum，照抄会踩坑。

一、进程模型：一连接一进程

这是 PG 和 MySQL 分岔的第一个路口。

postmaster（父进程，只做监听和 fork）
├── backend × N          ← 每个客户端连接一个
├── checkpointer         ← 定期做检查点
├── background writer    ← 持续把脏页刷出去
├── walwriter            ← 把 WAL buffer 刷到磁盘
├── autovacuum launcher  ← 拉起 autovacuum worker
├── walsender × N        ← 每个复制连接一个
└── archiver             ← 归档 WAL（开了 archive_mode 才有）

MySQL 是「一个 mysqld 进程 + N 个线程」，线程共享地址空间，创建成本低；PG 是「一个 postmaster + N 个进程」，每个后端有独立的地址空间。

这个差别带来三个直接后果：

第一，连接数就是进程数。 max_connections 默认 100，每个后端除共享内存外还有约 10MB 私有内存，再加上 work_mem。500 个连接不是「再多 400 个线程」，而是 500 个操作系统进程在抢 CPU 时间片。所以 PG 生产环境默认要挂 PgBouncer，用 transaction 模式把几百个应用连接收敛成几十个真实会话。

第二，进程崩溃不会带崩全库。 某个 backend 段错误，postmaster 会杀掉所有子进程并做一次崩溃恢复，虽然业务会抖一下，但内存不会像线程模型那样被污染到无法收拾。这是 PG 稳定性的一个来源。

第三，没有「连接线程」这一层可以塞逻辑。 MySQL 有连接线程做权限、会话变量、预处理；PG 里这些都在 backend 进程内部完成，所以每个会话的状态是进程私有的——这也是为什么 PgBouncer 的 transaction 模式会破坏 SET、临时表、advisory lock：会话被换到另一个 backend 上了。

二、内存：共享的和私有的，分得很清

这里最容易被误解的是 work_mem。 它不是「每个连接 4MB」，而是「每个算子 4MB」。一条 SQL 里有一个 Sort 加一个 Hash Join，这一个连接就要吃 8MB；并行查询起了 4 个 worker，还要各算一遍。所以「把 work_mem 调到 256MB」在连接数上千的库上是灾难。

第二个反直觉的点：shared_buffers 不该开到很大。 MySQL 的习惯是把 innodb_buffer_pool_size 开到物理内存 7080%，因为 InnoDB 不依赖 OS 缓存。PG 不一样——它读数据走普通文件 IO，OS 页缓存会再缓存一份。给 shared_buffers 太多，会和 OS 缓存重复占用同一批页（双缓冲），反而更慢。行业惯例是 **25% 左右，且一般不超过 816GB**。

三、存储：堆表 + 一堆独立索引

这是两边差异最大、也最容易影响索引设计的一点。

InnoDB：主键 B+ 树的叶子 = 整行数据
        二级索引叶子 = 主键值 → 回主键树取整行

PG：    堆文件（无序，按插入顺序追加）
        每个索引都是一棵独立的 B-tree，叶子只存 ctid（物理地址）

所以：

• PG 没有聚簇索引。 主键索引和普通索引地位完全平等，都是一棵独立的树。
• 按主键查也是两次 IO。 先走索引树拿到 ctid，再按物理位置去堆里取行。
• UPDATE 会改 ctid。 因为 PG 的更新是「标记旧行 + 插入新行」，所有索引都要指向新位置——除非走 HOT（见第五节）。

一个 8KB 的页里放着若干行，每行开头有个 23 字节的头，里面有 xmin、xmax、ctid 这些关键字段。ctid 长这样：(block_number, item_offset)，比如 (0,5) 表示第 0 个数据块的第 5 个行指针。

大字段怎么办（TOAST）：变长字段超过约 2KB 时，PG 会先尝试压缩，压不下就挪到一张单独的 TOAST 表里，主表只留一个指针。这一切对用户透明，但意味着查一个大 TEXT 字段会多一次 IO——所以 SELECT * 在 PG 里比在 MySQL 里更值得警惕。

四、一条 SELECT 的旅程

客户端
  │ ① 连接（TCP 5432）→ postmaster fork 一个 backend
  ▼
backend 进程
  │ ② 解析（raw parse）→ 语法树
  │ ③ 重写（rewrite）→ 展开视图、应用行级安全策略
  │ ④ 规划（planner）→ 生成执行计划，基于统计信息估算 cost
  │ ⑤ 执行（executor）→ 火山模型，逐行 pull
  ▼
访问方法层（heap / index / bitmap）
  │ ⑥ 需要某个页 → 先查 shared_buffers，没有就向 OS 要，再没有就读磁盘
  ▼
存储管理层 → 文件系统

三个细节值得记住：

① 规划器比 MySQL 强，但依赖统计信息。 PG 支持 nestloop / hash join / merge join 三种连接算法，会自动选连接顺序。代价是统计信息过期时计划会崩——大批量导入后没跑 ANALYZE，执行计划可能选全表扫。这是 PG 最常见的「突然变慢」原因之一。

② 执行器是火山模型（pull-based）。 上层不断向上层要下一行，天然支持 LIMIT 提前终止。但这也意味着复杂查询的元组在算子之间反复传递，开销不低。

③ 18 开始有了异步 IO。 PG 18 引入 io_method，把原来同步的读改成可以异步提交，对全表扫和 vacuum 有明显帮助；19 又让 I/O worker 数量自适应。这是近年 PG 在存储层最大的改动。

五、一条 UPDATE 的旅程

这是理解 VACUUM 和膨胀的前提。

-- 原来：ctid (0,1), xmin=1000, xmax=0, balance=100
UPDATE accounts SET balance = 200 WHERE id = 1;   -- 事务 1001

PG 内部做三件事：

1. 把旧行的 xmax 改成 1001（标记「被这个事务删掉了」）
2. 在页里插入一条新行：xmin=1001, xmax=0
3. 新行的 ctid 变了，所有索引都要插一条指向新位置的新条目

注意第 3 步——旧索引条目还在，指向那条已经死掉的行。这些垃圾索引条目也要等 VACUUM 清。

HOT 更新能省掉第 3 步，条件是两条：

• 被更新的列没有被任何索引引用
• 新版本能塞进同一个页的空闲空间

满足时，PG 只在新旧行之间加一条指针，索引继续指向旧位置，顺着链能找到新版本。所以：

索引太多会害了更新。 一张高频更新的表，如果给不常查的列也建了索引，每次更新都可能被迫走非 HOT 路径。

高频更新的表要把 fillfactor 调低（70~80），给页里留出更新空间，让 HOT 有机会命中。

这一点和 MySQL 的思维正好相反：MySQL 里多建索引主要影响写放大的 IOPS，PG 里多建索引还会直接破坏 HOT 优化，让更新路径变长。

六、WAL、检查点与崩溃恢复

PG 的持久化走 WAL（Write-Ahead Log），和 InnoDB 的 redo 思路一致，但实现不同：

最后一行是关键：PG 没有 binlog，WAL 同时承担崩溃恢复和复制两个职责。这是第二篇（复制）的起点。

检查点（checkpoint） 做两件事：把所有脏页刷到磁盘；在 WAL 里记一个位置，表示「这之前的所有修改都已经落盘」。崩溃恢复时只需从最近一次检查点开始重放 WAL，不用从头来。

相关参数：

checkpoint_timeout = 5min      -- 最长多久做一次
max_wal_size = 1GB             -- 两次检查点之间 WAL 最多长这么大
full_page_writes = on          -- 检查点后第一次修改整页写入，防页断裂（必须开）

full_page_writes 一定要开。 它让 WAL 变大，但它是防止「页断裂」的唯一手段——8KB 的页写到一半断电，会造成无法恢复的损坏。关掉它换性能，是拿数据完整性换的。

七、和 MySQL 的十项对照

这张表建议直接背下来，PG 面试里问「你之前用 MySQL？那你说说两者区别」的概率接近 100%。

第 6 条最容易踩：PG 的 RR 不需要间隙锁也能防幻读，因为快照本身就是一个时间点。代价是并发更新同一行时，后提交的那个直接收到 could not serialize access due to concurrent update——这不是异常，是正常路径，业务必须实现重试。从 MySQL 迁过来最容易在这里出线上问题。

第 7 条同理：MySQL 里两个事务抢同一行，第二个等锁；PG 里第二个可能直接失败。所以**「把 MySQL 的应用搬到 PG 只改连接串」是很危险的做法**，重试逻辑必须补。

八、四个常见误解

「PG 的主键也是聚簇索引。」 不是。PG 没有聚簇索引，CLUSTER 只是一次性重排，后续插入不会维护。

「VACUUM 会缩小表文件。」 不会。VACUUM 只把死元组占的空间标记为可复用，文件大小不变。要缩小只能 VACUUM FULL（锁全表）或 pg_repack（在线）。

「work_mem 是每连接一份。」 是每算子一份。一条有几个排序/哈希节点的 SQL，会吃好几份。

「PG 不适合写多的场景。」 这个说法在十年前成立，现在不成立。PG 的更新确实比 InnoDB 贵（要插新行 + 改索引），但通过合理的 fillfactor + HOT + 及时 vacuum，写密集场景完全能扛。真正不适合的是「高频更新同一批行且索引很多」的极端场景——那是 HOT 失效导致的，可以针对性设计。

接下来

这篇讲的是单机内部。下一篇讲两台机器之间：WAL 流复制、同步异步、复制槽、逻辑复制，以及主备切换时到底会发生什么。它的每一项都直接建立在这篇的 WAL 和检查点之上。