从 Index、MVCC 到 WAL：PostgreSQL 的重要机制

上一篇对比 PostgreSQL、Cassandra 和 Elasticsearch时，我把 PostgreSQL 概括成“维护业务事实的关系型数据库”：它用表、约束和事务保证资产关系正确。如果把 PostgreSQL 看成“维护业务事实的数据库”，这篇文章继续追问：当很多请求同时修改这些事实时，PostgreSQL 到底如何让它们既不互相踩踏，又尽可能少地等待？

答案不是“所有操作都加一把大锁”。PostgreSQL 的核心是 MVCC（Multi-Version Concurrency Control，多版本并发控制）：同一行可以在一段时间内存在多个版本，不同事务根据自己的快照判断哪个版本可见。锁仍然存在，但主要用来处理真正的冲突，而不是让每一次读取都阻塞写入。

可以先记住一条主线：

事务开始
   ↓
拿到快照，决定哪些版本可见
   ↓
UPDATE 产生新的 Tuple 版本
   ↓
WAL 保证崩溃后可以恢复
   ↓
COMMIT 后新版本对后续事务可见
   ↓
VACUUM 回收不再需要的旧版本

后文分别回答五个问题：如何找到行，如何判断版本可见，什么时候需要等待，修改如何持久化，以及旧版本最终如何被清理。

不过，PostgreSQL 的能力并不只有 MVCC。一次普通请求通常会同时经过多个机制：

下面以一次业务更新为主线，把这些机制放回它们各自的位置。

1. MVCC：UPDATE 不是把旧行改掉

在很多初学者的直觉里，执行：

UPDATE accounts
SET balance = balance - 100
WHERE id = 1;

似乎就是在原来的数据页上把 balance 覆盖成新值。但 PostgreSQL 的 heap tuple 更接近下面这种过程：

旧 Tuple
id=1, balance=1000
      │
      │ UPDATE
      ▼
新 Tuple
id=1, balance=900

旧版本不会立即从物理页面消失。每个 tuple 的头部包含创建它的事务 ID、删除或替换它的事务信息等元数据。事务读取一行时，不只是看业务字段，还会结合自己的快照判断：创建该版本的事务是否已经提交？删除它的事务是否对当前快照可见？

因此，同一时刻可能出现这样的状态：

Heap
├── id=1, balance=1000  ← 旧版本，某些快照仍然可见
└── id=1, balance=900   ← 新版本，后续快照可见

这就是“读不阻塞写、写通常也不阻塞普通读”的来源。正在更新一行的事务还没有提交时，另一个普通 SELECT 不会读到它的中间结果，而是继续看到自己快照中可见的旧版本。

这里的“旧版本”不是复制给每个事务的一份完整数据库。快照只是记录当前哪些事务已经完成、哪些仍在进行；真正的数据版本仍在表和索引页中。查询计划先通过 Seq Scan 或 Index Scan 找到可能的 tuple，再做可见性判断。

2. Index：定位行，也参与 MVCC 的可见性优化

上一篇已经从 B-Tree 讲过 PostgreSQL 的索引。这里再补一个和 MVCC 密切相关、也很容易被忽略的事实：Index 找到的不是“最终行”，而是可能需要进一步做可见性判断的候选位置。

B-Tree
   ↓ 找到 key 与 TID
Heap Page
   ↓ 判断 tuple 对当前快照是否可见
返回结果

因此，Index Scan 往往还需要回到 heap 读取 tuple。即使查询只需要索引中的字段，也不一定能完全跳过 heap，因为索引本身通常没有记录这个 tuple 对当前事务是否可见。

这正是 Index Only Scan 和 visibility map 的意义。VACUUM 会维护每个 heap page 的可见性信息；如果某个 page 已知其中所有 tuple 对所有事务都可见，查询就可以只读索引，不再访问 heap：

Index
  ↓
Visibility Map：该 Heap Page 全部可见？
  ├─ 是 → 直接从 Index 返回列
  └─ 否 → 回 Heap 做可见性检查

如果索引需要返回的列不在索引中，仍然需要访问 heap。对于覆盖查询，可以使用 INCLUDE 把非搜索列放进索引：

CREATE INDEX idx_jobs_pending
ON jobs (status, created_at) INCLUDE (id);

它可以服务类似下面的查询（是否真的采用 Index Only Scan，仍要以执行计划为准）：

SELECT id
FROM jobs
WHERE status = 'pending'
ORDER BY created_at
LIMIT 10;

但索引不是越多越好。每次 INSERT、UPDATE 或 DELETE 都可能需要维护相关索引；索引还会占用缓存和磁盘，并增加 vacuum 的工作量。另一个实用机制是 HOT（Heap-Only Tuple）：如果更新没有改变任何索引列，并且新版本能放在同一个 heap page，PostgreSQL 可以只在 heap 中建立版本链，避免为这次更新新增索引项；即便如此，heap 中仍然会产生新版本，仍需要 vacuum。由此可见，索引设计同时影响读路径、写放大和表膨胀。

除了普通 B-Tree，还可以根据查询形状选择部分索引、表达式索引以及 GIN、GiST、BRIN 等类型。例如任务表可以只为待处理数据建立部分索引：

CREATE INDEX idx_jobs_pending_created
ON jobs (created_at, id)
WHERE status = 'pending';

索引是否有效，最终仍取决于查询条件、数据分布和 Planner 的判断，而不是索引名字或数量。

3. 默认的 Read Committed 到底保证什么

PostgreSQL 默认隔离级别是 READ COMMITTED。它的一个容易被忽略的特征是：每一条 SQL 语句获得自己的快照，不是整个事务只获得一次。

例如：

T1: BEGIN;
T1: SELECT balance FROM accounts WHERE id = 1; -- 看到 1000

T2: UPDATE accounts SET balance = balance - 100 WHERE id = 1;
T2: COMMIT;

T1: SELECT balance FROM accounts WHERE id = 1; -- 这条语句可能看到 900
T1: COMMIT;

这并不是同一条查询前后返回了矛盾结果，而是两个语句分别在不同的已提交状态上执行。READ COMMITTED 保证的是“每条语句看到一个已提交快照”，不是“整个事务看到一个固定世界”。

如果业务需要事务内的多次读取保持一致，可以显式使用：

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;

REPEATABLE READ 会让事务中的普通查询使用同一个事务级快照；这个快照通常在事务中的第一条普通 SQL 开始时建立，而不是严格在 BEGIN 这一刻建立。它不是完整的 Serializable 隔离，跨行的 write skew 仍需谨慎。并发事务之间发生冲突时，当前事务可能失败，需要由应用重试，而不是悄悄返回一个不符合业务预期的结果。

更严格的 SERIALIZABLE 会进一步检测可能导致不可串行化的依赖，并以序列化失败结束其中一些事务。它不是“自动让所有请求排队”，而是允许并发执行，发现无法等价于某个串行顺序时回滚一个事务。因此使用它必须准备好捕获 serialization_failure 并重试。

4. 快照能解决什么，解决不了什么

MVCC 很适合解决“读取过程中不要看到半个更新”的问题，但它不会自动保证所有业务规则。

考虑库存扣减：

SELECT stock FROM products WHERE id = 42;
-- 应用层判断 stock > 0

UPDATE products SET stock = stock - 1 WHERE id = 42;

两个事务可能同时读到 stock = 1，然后都认为自己可以购买。MVCC 让每个事务读到一致的版本，却没有替应用判断“最终库存不能小于零”。如果先 SELECT 再无条件 UPDATE，后一个更新通常会等待前一个更新完成，然后基于新版本继续执行，库存可能被减成负数。

更稳妥的写法是把条件放进更新，并检查影响行数：

UPDATE products
SET stock = stock - 1
WHERE id = 42
  AND stock > 0;

影响行数为 0 时，应用需要判断是库存不足还是商品不存在，不能默认扣减成功；也可以使用 RETURNING 把更新后的库存直接返回。

或者把业务不变量交给数据库：

ALTER TABLE products
ADD CONSTRAINT products_stock_nonnegative CHECK (stock >= 0);

关键原则是：**快照负责可见性，约束负责不变量，原子更新或显式锁负责竞争同一资源时的协调。**不要把一次 SELECT 和后续的业务判断想象成自动不可分割的操作。

5. 行锁：只有真正需要协调时才等待

普通 SELECT 通常不会因为别的事务更新同一行而等待。如果应用需要“先读出这行，再基于当前值做一系列操作”，可以使用行级锁：

BEGIN;

SELECT *
FROM accounts
WHERE id = 1
FOR UPDATE;

UPDATE accounts
SET balance = balance - 100
WHERE id = 1;

COMMIT;

FOR UPDATE 表达的是：在当前事务结束前，不允许其他事务同时更新、删除或以类似方式锁定这行。第二个事务执行同样的 SELECT ... FOR UPDATE 时会等待第一个事务提交或回滚。

常见的变体有：

例如，多个 worker 可以在同一个事务中用下面的模式原子领取任务：

WITH picked AS (
  SELECT id
  FROM jobs
  WHERE status = 'pending'
  ORDER BY id
  FOR UPDATE SKIP LOCKED
  LIMIT 10
)
UPDATE jobs AS j
SET status = 'processing'
FROM picked
WHERE j.id = picked.id
RETURNING j.*;

它避免了所有 worker 都抢到同一批任务后排队。事务提交后，任务已经变成 processing；生产系统还需要考虑 worker 崩溃后的超时回收、重试和幂等。不过，SKIP LOCKED 改变了查询语义：返回的是“当前能拿到的任务”，不是严格意义上的全局最早任务。是否使用它，应由任务公平性和吞吐量要求决定。

如果要锁定的资源还没有对应的数据库行，例如某个租户的定时任务或一个外部设备，也可以考虑 advisory lock。它只是数据库提供的协调工具，数据库不会替应用强制所有代码遵守这把锁，因此必须统一约定 key 和释放方式。

6. 锁等待与死锁：数据库为什么会主动终止事务

锁等待本身不一定是故障。一个短事务更新一行，另一个事务等待几毫秒是正常的；真正危险的是事务持锁期间做了网络调用、等待用户输入，或者因为连接池问题一直没有提交。

死锁则是环形等待：

事务 A 持有 row 1，等待 row 2
        ▲                  │
        │                  ▼
事务 B 持有 row 2，等待 row 1

PostgreSQL 会检测这种环，并主动取消其中一个事务，让另一个事务继续。应用必须把死锁错误当作可重试错误处理；更重要的是，让所有代码以稳定顺序获取资源：例如总是先锁较小的 account_id，再锁较大的 account_id。

排查当前阻塞关系时，可以把 pg_stat_activity 和 pg_locks 结合起来：

SELECT
  blocked.pid AS blocked_pid,
  blocked.query AS blocked_query,
  blocker.pid AS blocker_pid,
  blocker.query AS blocker_query,
  blocked.wait_event_type,
  blocked.wait_event,
  now() - blocker.xact_start AS blocker_transaction_age
FROM pg_stat_activity AS blocked
CROSS JOIN LATERAL unnest(pg_blocking_pids(blocked.pid)) AS p(blocker_pid)
JOIN pg_stat_activity AS blocker
  ON blocker.pid = p.blocker_pid;

pg_blocking_pids 适合直接回答“谁在阻塞我”；pg_locks 则适合进一步分析锁的类型和对象。线上排查时，最值得关注的往往不是“哪条 SQL 最慢”，而是 blocker 的 xact_start：一条看起来已经执行完的语句，如果事务仍未提交，依然可能长期持有锁并阻止其他请求。

7. VACUUM：MVCC 留下的版本，最终由谁清理？

既然 UPDATE 会留下旧 tuple，那么旧版本总要有人处理。VACUUM 的主要工作包括：

• 标记已经对所有仍可能存在的快照不可见的 tuple 空间，使其可以被后续写入复用；
• 清理索引和表中不再需要的版本引用；
• 更新 visibility map，帮助 index-only scan 判断某些 heap page 是否全部可见；
• 推进事务 ID 的冻结，避免长期运行后发生 XID wraparound。

普通 VACUUM 通常不需要把表完整复制成一份新表，也不要求阻塞普通读写；它更像是在线整理。它主要让空间可以被同一张表后续复用，通常不会缩小数据文件、把空间还给操作系统。VACUUM FULL 则会重写表并释放更多磁盘空间，通常持有 ACCESS EXCLUSIVE 锁，应该安排在维护窗口执行。

长事务会让旧版本“可能仍然可见”，于是 VACUUM 不敢回收它们：

长事务 T1 持有很早的快照
          │
          ▼
T2/T3 不断 UPDATE，产生新版本
          │
          ▼
VACUUM 发现 T1 仍可能需要旧版本
          │
          ▼
旧 tuple 无法回收，表和索引逐渐膨胀

所以表膨胀不只是“写入很多”的结果，也可能是长事务、失控的连接、低频执行的批处理造成的。应用侧应尽量缩短事务范围，避免在事务里调用外部服务；数据库侧则要关注 autovacuum 的运行情况、表的 dead tuples 和最老事务年龄。

8. PostgreSQL 还有哪些常用机制

WAL 与 Checkpoint：先写日志，再认为提交可靠

事务提交时，PostgreSQL 不要求先把所有修改过的 heap page 和 index page 立刻写回数据文件。它会先把描述这些修改的 WAL（Write-Ahead Log，预写式日志）写入日志，并遵守“日志先于数据页落盘”的原则。即使数据库进程随后崩溃，重启时也可以从 WAL 重放已经写入日志的修改。

SQL 修改
   ├─ 内存中的 shared buffers
   └─ WAL buffer → WAL 文件 → WAL flush
                         ↓
                    COMMIT 返回

Checkpoint：把部分脏数据页写回数据文件，缩短恢复时需要重放的 WAL 范围

在默认的同步提交配置下，事务提交成功通常意味着对应的 WAL 已经 flush 到持久存储；如果关闭 synchronous_commit，提交响应可能早于 WAL 真正落盘，需要接受数据库崩溃时最近事务丢失的可能性。fsync 和底层存储设备的缓存可靠性也会影响最终的持久性保证。

Checkpoint 不是每次提交都发生的“保存按钮”，而是把内存中的脏页逐步落盘的恢复锚点。WAL 也因此成为流复制、WAL 归档和时间点恢复（PITR）的基础：备份保存某个时刻的数据，WAL 则保存之后发生过的变化。复制槽如果长期没有消费，也可能阻止旧 WAL 回收，需要纳入监控。

Planner 与 EXPLAIN：SQL 写法和执行方式是两回事

PostgreSQL 不会机械地把 WHERE 翻译成某一种访问方式。Planner 会根据统计信息、表大小、索引选择性、排序和 join 代价，在 Seq Scan、Index Scan、Bitmap Scan 以及多种 join 算法中选择计划。

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at
FROM jobs
WHERE tenant_id = 10
  AND status = 'pending'
ORDER BY created_at
LIMIT 10;

EXPLAIN 展示估算行数和成本；加上 ANALYZE 后会真正执行并展示实际行数与时间，BUFFERS 则帮助判断访问了多少 shared buffer。这个查询只请求索引中可能包含的列，有机会使用 Index Only Scan，但最终是否采用仍要以执行计划和 visibility map 为准。对 UPDATE、DELETE 使用 EXPLAIN (ANALYZE) 时要特别小心，因为它会真正执行语句。

若估算行数和实际行数差异很大，问题可能不在“缺一个索引”，而在统计信息过旧、数据分布倾斜或查询条件相关性没有被 Planner 充分了解。此时应先确认计划，再考虑索引、ANALYZE 或扩展统计信息，而不是直接把 enable_seqscan 关掉。

Constraint 与类型：把正确性放在数据库边界

PostgreSQL 的约束不是只为文档好看，它们是并发环境中的最后一道业务边界：

CREATE TABLE memberships (
  user_id bigint REFERENCES users(id),
  team_id bigint REFERENCES teams(id),
  role text NOT NULL CHECK (role IN ('admin', 'member')),
  PRIMARY KEY (user_id, team_id)
);

PRIMARY KEY 保证身份唯一，FOREIGN KEY 防止悬空引用，UNIQUE 和 CHECK 表达业务不变量，NOT NULL 则把“这个字段必须存在”从应用代码推进数据库。配合事务，它们比“先查询确认，再依赖应用层不出错”更可靠。CHECK 通常只检查当前行，不能直接表达跨行规则；而且 CHECK 表达式为 UNKNOWN（常见于 NULL）时通常不会拒绝写入，所以库存这类字段一般还需要 NOT NULL。

主键和唯一约束会自动创建索引，但 PostgreSQL 不会自动为外键的 referencing columns 创建索引。父表删除、更新，或按外键连接很频繁时，通常需要手动为这些列建索引。

并发创建订单时，也不要先 SELECT 检查订单号不存在，再执行 INSERT；两个请求可能同时通过检查。应让唯一约束成为最终边界，并使用 ON CONFLICT 表达幂等写入：

INSERT INTO orders (order_no, user_id)
VALUES ($1, $2)
ON CONFLICT (order_no) DO NOTHING;

PostgreSQL 还提供 timestamp、数组、范围类型、jsonb、全文检索和自定义类型等能力。jsonb 适合承载变化中的附加属性，但稳定、高频过滤或需要约束的字段仍应优先建成明确列；类型越贴近业务语义，约束和索引越容易发挥作用。

Connection Pool 与 Extension：数据库也有运行时边界

PostgreSQL 使用进程/连接来承载会话状态，每个连接都有内存和管理成本。应用如果为每个请求新建连接，通常会先把数据库拖垮在连接数和上下文切换上，而不是达到 CPU 或磁盘上限。因此线上服务一般通过连接池限制并复用连接，同时避免把连接池大小简单设置成“越大越快”。连接归还连接池前，还要确保事务已经结束，并清理可能遗留的 session 参数、临时表或 advisory lock；否则下一个请求可能继承上一个请求的状态。

扩展机制也是 PostgreSQL 的重要特点：PostGIS 提供空间数据能力，pg_stat_statements 帮助统计 SQL，其他扩展则可以提供特定类型、索引或认证能力。扩展让 PostgreSQL 可以贴近具体领域，但也意味着备份、升级和迁移时要把扩展版本当作部署的一部分管理。

9. 把几个概念放回一次订单更新

假设用户提交订单，需要扣库存、写订单和记录支付意图：

BEGIN
  │
  ├─ INSERT orders
  ├─ UPDATE products SET stock = stock - 1 WHERE stock > 0
  ├─ INSERT payments
  │
  ├─ 约束检查：库存不能为负、订单号不能重复
  ├─ WAL：保证崩溃恢复时提交结果不会凭空消失
  ├─ MVCC：其他普通读不会看到半个订单
  └─ COMMIT：这些修改作为一个事务对后续快照可见

如果库存扣减影响 0 行，应用就应该返回库存不足或商品不存在，而不是继续创建一个“库存未扣但订单成功”的状态。若任一步约束检查或写入失败，事务会回滚，数据库内部不会留下半个订单。

但 COMMIT 只能覆盖 PostgreSQL 内部的修改，不能自动覆盖支付网关或消息队列。应用不应该在持有数据库锁的事务里等待外部支付服务；更常见的做法是先在事务中记录支付意图和 outbox 事件，提交后由异步 worker 调用外部服务，并通过幂等键、状态机和重试处理失败。

如果两个请求竞争最后一个库存，数据库会在更新同一行时协调它们，第二个请求最终要么更新不到行，要么在等待后重新面对已变化的版本。若应用把“读取库存、等待支付服务、再扣库存”全部放在一个事务里，问题就会从几毫秒的行锁竞争扩大成长时间锁等待。

因此，PostgreSQL 并发设计的落点通常不是“把隔离级别调到最高”，而是：让事务短而清晰，用 SQL 原子表达竞争条件，用约束保护不变量，只有确实需要时才锁行，并对死锁和序列化失败进行有限重试。

总结

可以用下面几句话记住 PostgreSQL 的重要机制：

1. Index 负责缩小搜索范围，但是否高效还取决于选择性、可见性和执行计划。
2. MVCC 让读取依据快照看到一致的版本，普通读写通常不互相阻塞。
3. 隔离级别决定快照持续多久，锁则协调真正竞争同一资源的事务。
4. 约束保护业务不变量，WAL 保护崩溃恢复，二者解决的不是同一个问题。
5. VACUUM 是多版本存储的必要维护工作；长事务会妨碍它回收旧版本。

这也补上了上一篇对 PostgreSQL 的描述：它不仅通过关系、约束和 SQL 维护业务事实，还通过 Index、WAL、MVCC、锁、Planner 和 VACUUM，让这些事实在并发修改、崩溃恢复和长期运行中保持可用。数据库的性能并不只取决于某条查询有没有走 Index，也取决于事务是否足够短、版本是否能够及时回收、统计信息是否可信，以及应用是否准确表达了它想要的并发语义。

References

• PostgreSQL：并发控制简介
• PostgreSQL：事务隔离
• PostgreSQL：显式锁定
• PostgreSQL：常规 VACUUM
• PostgreSQL：预写式日志（WAL）