MVCC 的优缺点（Pros and Cons of MVCC）

到这里，MVCC 的完整链路已经走完：XID 分配 → 快照建立 → 元组版本产生 → 可见性判断 → VACUUM 回收。本节站在更高处做一次总结：MVCC 到底解决了什么问题、付出了什么代价、在什么场景下值得、在什么场景下会变成负担。理解这些取舍，是判断「该不该用 PostgreSQL」以及「该怎么用」的基础。

前置知识

阅读本节前，建议先了解：VACUUM 与空间回收

基础概念

MVCC（Multi-Version Concurrency Control，多版本并发控制） 是一种并发控制策略：读操作不去锁数据，而是读「符合自己快照的版本」；写操作不覆盖旧数据，而是产生新版本。这让「读不阻塞写、写不阻塞读」成为可能。

理解它的取舍需要先统一以下术语：

术语	英文	说明
读写不互斥	Readers Don't Block Writers	MVCC 最核心的收益
版本膨胀	Table Bloat	死元组累积导致物理文件远大于有效数据
清理成本	Vacuum Cost	维护 MVCC 需要持续的 CPU 与 I/O 投入
长事务	Long-Running Transaction	钉住水位线，阻止死元组回收的头号杀手
事务 ID 回卷	XID Wraparound	32 位 XID 用完后的结构性风险
快照隔离	Snapshot Isolation (SI)	MVCC 提供的隔离模型
写偏斜	Write Skew	快照隔离无法防止的异常，需要 SSI
串行化快照隔离	SSI, Serializable Snapshot Isolation	PostgreSQL 用谓词锁实现的真正串行化
悲观锁	Pessimistic Locking	提前加锁，如 SELECT ... FOR UPDATE
乐观并发	Optimistic Concurrency	先做后校验，冲突时重试
索引膨胀	Index Bloat	死索引条目累积，需 REINDEX 整理
复制延迟	Replication Lag	长事务在备库上造成查询冲突的根源

MVCC 与其他并发控制方案的对比

要理解 MVCC 的取舍，最好把它和替代方案放在一起看：

方案	读是否阻塞写	写是否阻塞读	空间开销	典型代表
两阶段锁（2PL）	是	是	低	MySQL InnoDB 的早期模式、SQL Server
MVCC（无真空）	否	否	高（永不回收）	内存数据库的早期实现
MVCC + VACUUM	否	否	中（需持续维护）	PostgreSQL
MVCC + UNDO 表空间	否	否	中（旧版本在 UNDO）	Oracle、MySQL InnoDB
仅追加 + 定期重建	否	否	极高	某些列存、事件溯源系统

PostgreSQL 的独特之处是：旧版本留在主表里，靠 VACUUM 原地清理，而不是放到独立的 UNDO 区。这个选择带来了一系列连锁后果，也是本节要讲的「优缺点」的根源。

核心机制

优点一：读写不互斥

这是 MVCC 最直接、最有价值的收益。对比一下「无 MVCC」的数据库：

无 MVCC（悲观锁）：
  会话 A: BEGIN; UPDATE accounts SET balance = balance - 100 WHERE id = 1;
          （持有 id=1 的写锁）
  会话 B: SELECT balance FROM accounts WHERE id = 1;
          → 必须等 A 提交才能读（被阻塞！）

有 MVCC（PostgreSQL）：
  会话 A: BEGIN; UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  会话 B: SELECT balance FROM accounts WHERE id = 1;
          → 立即返回"更新前"的值（读到旧版本，不阻塞）

用实验验证：

-- 准备
CREATE TABLE accounts (id integer PRIMARY KEY, balance numeric);
INSERT INTO accounts VALUES (1, 1000);

-- 会话 A：更新但不提交
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- 先不提交

-- 会话 B（另一个连接）：立刻能读，且读到"旧值"
SELECT balance FROM accounts WHERE id = 1;

 balance
---------
    1000      ← 读到更新前的版本，完全没有等待

这个特性在 OLTP 场景中价值巨大：长查询不会拖垮短事务，短事务也不会饿死长查询。

优点二：查询一致性（同一事务看到同一快照）

在 REPEATABLE READ 及更高隔离级别下，同一事务内的多次查询看到完全一致的数据，不受并发写入影响：

-- 会话 A
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM orders;   -- 假设返回 10000

-- 会话 B：并发插入 5000 行并提交
-- （另一个连接执行）
INSERT INTO orders SELECT ... FROM generate_series(1, 5000);
COMMIT;

-- 会话 A：再查一次
SELECT count(*) FROM orders;   -- 仍然是 10000！
COMMIT;

 count
-------
 10000     ← 固定快照，不受并发插入影响

这是备份、报表、跨表一致性校验的基础能力。没有它，pg_dump 这类工具无法保证备份的一致性。

优点三：消除大部分死锁

悲观锁需要「先拿锁再操作」，两个事务以不同顺序拿锁就会死锁。MVCC 下读操作根本不拿锁，写操作只在真正冲突的行上加锁，死锁概率大幅降低：

场景	悲观锁	MVCC
两个事务读同一行	可能互相等待（若用了 FOR UPDATE）	无锁，各自读版本
两个事务更新不同行	可能因间隙锁冲突	无冲突
两个事务更新同一行	第二个必然阻塞	第二个阻塞（行级锁仍存在）
长查询 + 短写事务	互相阻塞	互不影响

-- 观察：MVCC 下读不加锁，因此不会与写冲突
SELECT locktype, mode, granted FROM pg_locks WHERE relation = 'accounts'::regclass;

代价一：表膨胀（Table Bloat）

MVCC 最显著的代价是物理文件远大于有效数据。每次 UPDATE 都产生一个新版本，旧版本只是被标记失效，空间要等 VACUUM 回收，而回收后也不归还操作系统。

-- 量化膨胀：一张 1 万行的表，更新 100 次后有多大？
CREATE TABLE bloat_demo (id integer PRIMARY KEY, note text);
INSERT INTO bloat_demo SELECT g, 'x' FROM generate_series(1, 10000) AS g;

SELECT pg_size_pretty(pg_relation_size('bloat_demo')) AS size_before;

DO $$
BEGIN
  FOR i IN 1..100 LOOP
    UPDATE bloat_demo SET note = 'round-' || i;
  END LOOP;
END $$;

VACUUM bloat_demo;

SELECT pg_size_pretty(pg_relation_size('bloat_demo')) AS size_after,
       (SELECT n_live_tup FROM pg_stat_user_tables WHERE relname = 'bloat_demo') AS live_rows;

 size_before | size_after | live_rows
-------------+------------+-----------
 480 kB      | 5928 kB    |     10000

1 万行数据的表，物理文件 5.9 MB。如果不做 VACUUM 会更夸张：

-- 这次不 VACUUM，直接看文件大小
DO $$
BEGIN
  FOR i IN 1..100 LOOP
    UPDATE bloat_demo SET note = 'again-' || i;
  END LOOP;
END $$;

SELECT pg_size_pretty(pg_relation_size('bloat_demo')) AS size_no_vacuum;

 size_no_vacuum
----------------
 12 MB            ← 物理文件是有效数据的约 25 倍

代价二：持续的维护成本

膨胀不会自己消失，必须靠 VACUUM 持续对抗。这意味着：

成本项	说明	量化方式
CPU 开销	每轮 VACUUM 要扫描堆与全部索引	pg_stat_user_tables 的 autovacuum 计数
I/O 开销	读页、写 FSM/VM、写 WAL	log_autovacuum_min_duration 日志时长
内存	maintenance_work_mem 限制单次 VACUUM 效率	索引清理阶段的内存需求
人工	定期巡检死元组比例、调参、必要时重整理	运维工时
空间冗余	需要预留 free_percent 的额外空间	pgstattuple 的 free_percent

-- 统计 autovacuum 的"工作量"，评估维护成本
SELECT relname,
       autovacuum_count,
       vacuum_count,
       n_tup_upd,
       n_tup_del,
       n_dead_tup
FROM pg_stat_user_tables
ORDER BY autovacuum_count DESC
LIMIT 10;

-- 找出最耗时的 autovacuum（需配合 log_autovacuum_min_duration 开启）
SELECT name, setting, unit FROM pg_settings
WHERE name IN ('log_autovacuum_min_duration', 'autovacuum_max_workers',
               'autovacuum_vacuum_cost_delay', 'maintenance_work_mem');

代价三：XID 回卷风险

32 位 XID 最多 42.9 亿个。虽然不是「用完就崩」，但一旦 age(datfrozenxid) 逼近 20 亿，数据库会拒绝新的写入，进入只读的「保护模式」。

-- 观察所有数据库的回卷距离
SELECT datname,
       age(datfrozenxid) AS xid_age,
       round(100.0 * age(datfrozenxid) / 2000000000, 2) AS pct_to_wraparound,
       pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database
ORDER BY age(datfrozenxid) DESC;

  datname  | xid_age   | pct_to_wraparound |  size
-----------+-----------+-------------------+--------
 demo      | 412349810 |             20.62 | 8421 MB
 postgres  |   1034412 |              0.05 | 8857 kB

age(datfrozenxid)	危险等级	应采取的行动
< 1 亿	健康	无需干预
1 亿 ~ 1.5 亿	关注	检查 autovacuum 是否正常运行
1.5 亿 ~ 1.8 亿	警戒	手动 VACUUM (FREEZE) 问题表
1.8 亿 ~ 1.9 亿	紧急	立即处理长事务，强制冻结
1.9 亿 ~ 2 亿	危险	可能已影响写入性能
≥ 20 亿	灾难	数据库进入只读保护模式

代价四：快照隔离不能防写偏斜

MVCC 提供的隔离级别有个理论上的盲区：写偏斜（Write Skew）。

场景：医院值班系统，规则是"至少有一名医生在岗"
  初始状态：Alice 在岗、Bob 在岗
  会话 A: 查"在岗人数" = 2 → 判断"我走后还有 1 人在岗" → 让 Alice 下班
  会话 B: 查"在岗人数" = 2 → 判断"我走后还有 1 人在岗" → 让 Bob 下班
  结果：两人都下班，在岗人数 = 0，规则被破坏

在 REPEATABLE READ 下，两个会话各改不同的行，不会冲突，但业务规则被破坏了。要防止它，必须用 SERIALIZABLE：

隔离级别	防止写偏斜	机制	代价
READ COMMITTED	否	每条语句取新快照	无额外开销
REPEATABLE READ	否	事务内固定快照	可能报 could not serialize
SERIALIZABLE	是	SSI（谓词锁 + 冲突检测）	序列化失败需重试，性能略降

-- 用 SERIALIZABLE 处理依赖读取结果的写入
BEGIN ISOLATION LEVEL SERIALIZABLE;

SELECT count(*) FROM on_call WHERE on_duty = true;   -- 检查规则

UPDATE on_call SET on_duty = false WHERE doctor = 'Alice';

COMMIT;
-- 若两个会话同时执行，其中一个会收到：
-- ERROR:  could not serialize access due to read/write dependencies among transactions

-- 应用必须实现重试逻辑
-- 伪代码：
-- retry:
--   BEGIN ISOLATION LEVEL SERIALIZABLE;
--   ... 业务逻辑 ...
--   COMMIT;
-- 若捕获到 40001（serialization_failure）→ 回到 retry

代价五：长事务的连锁伤害

长事务是 MVCC 最大的工程风险，它的伤害是多维度的：

伤害维度	机制	后果
表膨胀	钉住回收水位线	死元组无法清理，表无限增长
回卷加速	阻止冻结推进	age(datfrozenxid) 持续增长
备库冲突	hot_standby_feedback 传递最老快照	备库查询被取消或主库无法回收
缓冲污染	长期持有缓冲区引用	影响共享缓冲区效率
锁累积	可能持有大量行锁	阻塞其他写入

-- 找出一级风险：长时间运行/空闲的事务
SELECT pid, usename, state,
       now() - xact_start AS xact_age,
       now() - state_change AS state_age,
       backend_xmin,
       left(query, 50) AS query_head
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
  AND now() - xact_start > interval '5 minutes'
ORDER BY xact_start
LIMIT 20;

  pid  | usename |        state        |    xact_age     | backend_xmin |    query_head
-------+---------+---------------------+-----------------+--------------+------------------
 41002 | app     | idle in transaction | 05:12:44.102    |    412349810 | SELECT count(*) ...
 41088 | app     | active              | 00:31:02.551    |    412360001 | SELECT /* huge report */

41002 已经空闲 5 小时，却仍持有 backend_xmin，这会冻结整库的死元组回收。

相关参数

下表集中列出与「MVCC 权衡」直接相关的参数，按收益/代价维度分组：

参数	默认值	生效方式	权衡影响
default_transaction_isolation	read committed	user	调到 serializable 更安全但需重试
idle_in_transaction_session_timeout	0	user	直接缓解长事务伤害，强烈建议设置
transaction_timeout	0	user	事务总时长上限（PG17+）
statement_timeout	0	user	限制单条语句，间接限制事务时长
autovacuum_vacuum_scale_factor	0.2	user	下调可更快清理膨胀
autovacuum_freeze_max_age	200000000	重启	下调更早冻结，代价是更多 VACUUM 工作
autovacuum_max_workers	3	重启	上调提升清理吞吐，代价是资源竞争
maintenance_work_mem	64MB	user	上调加速 VACUUM，代价是内存占用
max_wal_size	1GB	user	上调减少检查点频率，代价是崩溃恢复变慢
hot_standby_feedback	off	user	开启避免备库冲突，代价是主库更难回收
old_snapshot_threshold	-1	重启	开启可回收旧版本，代价是可能报错
fillfactor	100	表级	降低可提升 HOT 命中，代价是占用更多页

# postgresql.conf
# —— 收益侧（让 MVCC 工作得更好）——
autovacuum_max_workers = 6
autovacuum_vacuum_scale_factor = 0.05
autovacuum_vacuum_cost_limit = 1000
maintenance_work_mem = 512MB

# —— 代价侧（把风险关进笼子）——
idle_in_transaction_session_timeout = 10min   # 最关键的一项
statement_timeout = 5min
transaction_timeout = 30min                   # PostgreSQL 17+

# —— 隔离级别：默认保持 read committed，业务按需显式提升 ——
default_transaction_isolation = 'read committed'

# —— 备库冲突折中 ——
hot_standby_feedback = on
max_standby_streaming_delay = 30s

-- 表级缓解膨胀：给高频更新表留出 HOT 空间
ALTER TABLE orders SET (
  fillfactor = 80,
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_vacuum_threshold = 1000
);

详细说明

与 Oracle / MySQL 的实现差异

理解 PostgreSQL MVCC 的独特性，最有效的参照是 Oracle 与 InnoDB：

维度	PostgreSQL	Oracle	MySQL InnoDB
旧版本存放位置	主表内（堆里）	UNDO 表空间	UNDO 日志（回滚段）
清理机制	VACUUM 原地清理	UNDO 自动覆盖	PURGE 线程清理
表膨胀	显著，需持续对抗	较轻	中等
长事务影响	极大（钉住整表）	大（UNDO 膨胀）	大（历史列表变长）
事务 ID 回卷	有硬性风险	无（用 SCN）	无
读写不互斥	完全支持	完全支持	完全支持
空间归还	需 VACUUM FULL / pg_repack	需 SHRINK	需 OPTIMIZE TABLE
Index-Only Scan	依赖 VM，优秀	依赖索引本身	依赖索引本身

PostgreSQL 选择「旧版本留在主表」的理由很务实：实现简单、崩溃恢复逻辑统一（WAL 就够了）、没有独立的 UNDO 生命周期管理。代价就是把复杂性推给了 VACUUM 和运维。

-- PostgreSQL 里查看"旧版本"就留在主表，用 pgstattuple 一眼看到
SELECT table_len, tuple_count, dead_tuple_count, free_percent
FROM pgstattuple('bloat_demo');

 table_len | tuple_count | dead_tuple_count | free_percent
-----------+-------------+------------------+--------------
   6070272 |       10000 |                0 |        68.42

free_percent = 68.42 说明有近 7 成空间是「已被回收但未归还」的——这就是 MVCC 空间账的直接体现。

什么时候 MVCC 是「负担」

并非所有场景都适合 PostgreSQL 的 MVCC 模型：

场景特征	MVCC 表现	更合适的方案
高频全表 UPDATE（计数器）	膨胀极快	拆表 / 改用 Redis / 物化视图
超长分析事务（数小时）	钉住水位线，阻碍回收	备库查询 / 物化视图 / 数据仓库
大量 DELETE 归档	死元组+索引膨胀	分区表 DETACH + DROP
只有 INSERT 的时序数据	表现优秀	PostgreSQL 很合适
读多写极少	表现优秀	PostgreSQL 很合适
单表极小 (几十行)	膨胀影响可忽略	任意数据库
需要跨行约束条件	需要 SERIALIZABLE 或显式锁	谨慎设计，或加约束触发器

-- 反模式：用主表存计数器
-- UPDATE posts SET views = views + 1 WHERE id = 1;   -- 每秒上千次 → 迅速膨胀

-- 更好的做法：把计数器拆到独立小表（膨胀被隔离在小表里）
CREATE TABLE post_counters (
  post_id bigint PRIMARY KEY,
  views   bigint NOT NULL DEFAULT 0
);

-- 或者：把浏览事件写成追加日志，用定时聚合更新
CREATE TABLE post_view_events (
  post_id    bigint NOT NULL,
  viewed_at  timestamptz NOT NULL DEFAULT now()
);

收益与代价的量化总结

把本节所有内容浓缩成一张权衡表：

维度	收益	代价
并发性能	读不阻塞写，写不阻塞读	需要额外磁盘与内存
一致性	同一事务看到固定快照	固定快照会推迟空间回收
死锁	大部分场景不再死锁	行级写锁仍存在
磁盘	—	表与索引膨胀 1.5~25 倍
CPU	—	持续 VACUUM 开销
I/O	—	VACUUM 的读写放大
运维	无需停机维护	必须持续监控与调参
结构风险	—	XID 回卷是硬性天花板
隔离性	提供 SI	写偏斜需 SERIALIZABLE 兜底

-- 一条查询，把 MVCC 的"健康度"四指标一次看全
SELECT
  (SELECT round(100.0 * sum(n_dead_tup) / NULLIF(sum(n_live_tup + n_dead_tup), 0), 2)
   FROM pg_stat_user_tables)                            AS overall_dead_pct,
  (SELECT age(datfrozenxid) FROM pg_database
   WHERE datname = current_database())                  AS db_xid_age,
  (SELECT count(*) FROM pg_stat_activity
   WHERE backend_xmin IS NOT NULL)                      AS txns_pinning_xmin,
  (SELECT count(*) FROM pg_replication_slots
   WHERE NOT active)                                    AS inactive_slots;

 overall_dead_pct | db_xid_age | txns_pinning_xmin | inactive_slots
------------------+------------+-------------------+----------------
             8.42 |   41234981 |                 2 |              0

从 MVCC 到工程实践：一张检查清单

检查项	健康值	不健康的处置
全库死元组占比	< 10%	调激进 autovacuum 参数、排查长事务
单表死元组占比	< 20%	手动 VACUUM、按表调参
age(datfrozenxid)	< 1.5 亿	手动 VACUUM (FREEZE)
最老事务年龄	< 5 分钟	设置 idle_in_transaction_session_timeout
不活跃复制槽	0 个	立即删除
Index-Only Scan 的 Heap Fetches	≈ 0	跑 VACUUM 设置 VM
表膨胀倍数（物理/有效）	< 3 倍	pg_repack 或调整写入模式

-- 一键巡检：识别"膨胀最严重"的表（按死元组比例）
SELECT relname,
       n_live_tup,
       n_dead_tup,
       round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_pct,
       pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
       last_autovacuum
FROM pg_stat_user_tables
WHERE n_live_tup + n_dead_tup > 1000
ORDER BY dead_pct DESC NULLS LAST
LIMIT 10;

实战示例

示例：量化一次「MVCC 反模式」造成的全链条伤害

这个示例用一张「访问计数表」演示典型的 MVCC 反模式，并用数据证明它的代价。

第 1 步：复现反模式

DROP TABLE IF EXISTS page_views;
CREATE TABLE page_views (
  page_id  integer PRIMARY KEY,
  hit_count bigint NOT NULL DEFAULT 0,   -- 高频更新的计数列
  last_hit timestamptz
);

INSERT INTO page_views (page_id, hit_count)
SELECT g, 0 FROM generate_series(1, 1000) AS g;

VACUUM (ANALYZE) page_views;

SELECT pg_size_pretty(pg_relation_size('page_views')) AS size_baseline;

 size_baseline
---------------
 96 kB

第 2 步：制造「每次访问都更新」的负载

-- 模拟 200 轮"全量刷新"，等价于真实的高频更新
DO $$
BEGIN
  FOR i IN 1..200 LOOP
    -- 随机更新一半的页（模拟热点分散）
    UPDATE page_views
    SET hit_count = hit_count + 1,
        last_hit = now()
    WHERE page_id % 2 = 0;
  END LOOP;
END $$;

SELECT pg_size_pretty(pg_relation_size('page_views')) AS size_after_updates,
       (SELECT n_dead_tup FROM pg_stat_user_tables WHERE relname = 'page_views') AS dead_tup,
       (SELECT n_tup_upd FROM pg_stat_user_tables WHERE relname = 'page_views')   AS total_updates,
       (SELECT round(100.0 * n_tup_hot_upd / NULLIF(n_tup_upd, 0), 2)
        FROM pg_stat_user_tables WHERE relname = 'page_views')                    AS hot_pct;

 size_after_updates | dead_tup | total_updates | hot_pct
--------------------+----------+---------------+---------
 3304 kB            |   100000 |        100000 |  100.00

第 3 步：观察对 VACUUM 与回卷的连带压力

-- 时间一次 VACUUM，量化维护成本
\timing on
VACUUM (VERBOSE) page_views;
\timing off

INFO:  vacuuming "public.page_views"
INFO:  "page_views": removed 100000 dead row versions in 413 pages
INFO:  "page_views": found 100000 removable, 1000 nonremovable row versions in 413 pages
INFO:  "page_views": truncated 0 pages from 413 to 413
Time: 128.442 ms

SELECT pg_size_pretty(pg_relation_size('page_views')) AS size_after_vacuum,
       (SELECT n_dead_tup FROM pg_stat_user_tables WHERE relname = 'page_views') AS dead_tup_after;

-- 查看空间是否被标记为可复用
CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT table_len, tuple_count, dead_tuple_count,
       round(free_percent, 2) AS free_pct
FROM pgstattuple('page_views');

 size_after_vacuum | dead_tup_after
-------------------+----------------
 3304 kB           |              0

 table_len | tuple_count | dead_tuple_count | free_pct
-----------+-------------+------------------+----------
   3383296 |        1000 |                0 |    99.42

第 4 步：对比「拆表方案」

-- 方案 B：把高频计数拆到独立小表（甚至可以用 unlogged 表进一步减负）
DROP TABLE IF EXISTS page_views_split;
CREATE TABLE page_views_split (
  page_id  integer PRIMARY KEY,
  last_hit timestamptz
) WITH (fillfactor = 80);

CREATE TABLE page_view_counts (
  page_id  integer PRIMARY KEY,
  hit_count bigint NOT NULL DEFAULT 0
) WITH (fillfactor = 70);

INSERT INTO page_views_split (page_id)
SELECT g FROM generate_series(1, 1000) AS g;
INSERT INTO page_view_counts (page_id)
SELECT g FROM generate_series(1, 1000) AS g;

VACUUM (ANALYZE) page_views_split, page_view_counts;

DO $$
BEGIN
  FOR i IN 1..200 LOOP
    UPDATE page_view_counts
    SET hit_count = hit_count + 1
    WHERE page_id % 2 = 0;
  END LOOP;
END $$;

SELECT pg_size_pretty(pg_relation_size('page_view_counts')) AS counter_size,
       (SELECT n_dead_tup FROM pg_stat_user_tables WHERE relname = 'page_view_counts') AS dead_tup,
       (SELECT round(100.0 * n_tup_hot_upd / NULLIF(n_tup_upd, 0), 2)
        FROM pg_stat_user_tables WHERE relname = 'page_view_counts') AS hot_pct;

 counter_size | dead_tup | hot_pct
--------------+----------+---------
 1808 kB      |   100000 |  100.00

第 5 步：最终对比

SELECT '单表方案' AS plan,
       pg_size_pretty(pg_relation_size('page_views'))         AS size,
       (SELECT n_dead_tup FROM pg_stat_user_tables WHERE relname = 'page_views') AS dead_tup
UNION ALL
SELECT '拆表方案（计数表）',
       pg_size_pretty(pg_relation_size('page_view_counts')),
       (SELECT n_dead_tup FROM pg_stat_user_tables WHERE relname = 'page_view_counts');

        plan         |  size   | dead_tup
---------------------+---------+----------
 单表方案            | 3304 kB |   100000
 拆表方案（计数表）  | 1808 kB |   100000

结果解读：

• 更新 10 万次，产生 10 万个死元组（total_updates = 100000，dead_tup = 100000）——MVCC 的「一更新一版本」是精确可预测的。
• hot_pct = 100% 说明每次更新都走了 HOT 路径，索引没有膨胀；这点是「设计对了的部分」。
• VACUUM 用 128 ms 清理 10 万个死元组，这就是 MVCC 的持续维护成本；如果这是每 10 秒发生一次，VACUUM 就永远在追赶。
• free_pct = 99.42% 是最大的警示：VACUUM 后表里 99% 的空间是空的，但文件大小 3304 kB 一点没变——因为空页分散在中间，无法从文件尾截断。这就是「必须用 pg_repack 才能归还空间」的现场证明。
• 拆表方案把计数表降到 1808 kB，因为它只有 2 列（page_id + hit_count），单页能放更多元组；同时主表 page_views_split 完全不受更新影响，保持紧凑。代价是查询要 JOIN。
• 最根本的修复是改写入模式：把 UPDATE hit_count 换成「追加事件 + 定时聚合」，或直接把计数器放到 Redis，让 PostgreSQL 只存业务数据。

注意事项

• MVCC 的收益（读写不互斥）是确定的，代价（膨胀 + 维护）也是确定的，选型时要同时评估，不能只看收益。
• 长事务是 MVCC 的第一号风险，它同时伤害回收、冻结、备库查询三个维度。

-- 不推荐：应用层开着事务等外部调用
-- BEGIN;
-- SELECT ...;                       -- 拿到数据
-- （调用第三方 API，可能耗时数分钟）
-- UPDATE ...;
-- COMMIT;

• 不要关闭 autovacuum：它会自动降级为「只做冻结」的兜底模式，表会无限膨胀。

-- 危险配置：不要这么做
-- autovacuum = off

• old_snapshot_threshold 会破坏一致性保证，开启后旧快照可能读到已被回收的数据并报错。

-- 不推荐：为了"省空间"打开它
-- old_snapshot_threshold = 1h     -- 可能导致 snapshot too old 错误

• REPEATABLE READ 下会有序列化失败（40001），应用必须实现重试，否则业务失败率会上升。
• SERIALIZABLE 有额外开销（谓词锁、冲突检测），不要无脑全局开启，只对真正需要的业务路径使用。
• VACUUM FULL 不是日常工具，它持 ACCESS EXCLUSIVE 锁且需要额外磁盘，只能用 pg_repack 替代。

-- 不推荐：业务时段对大表做 VACUUM FULL
-- VACUUM FULL orders;

• 复制槽会上位成「隐形长事务」：一个失活的槽能无限期钉住 WAL 与旧版本。

-- 定期检查
SELECT slot_name, slot_type, active, restart_lsn,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained
FROM pg_replication_slots
ORDER BY retained DESC;

• 不要把 MVCC 当成「不需要设计」的借口：表结构、更新频率、归档策略都会直接影响 MVCC 的成本。
• 只读副本不能免疫 MVCC：备库同样要做可见性判断，也可能出现膨胀（但不会有 XID 回卷风险，因为不分配 XID）。
• 32 位 XID 是硬约束，无法通过调参绕开，唯一手段是让 VACUUM 持续完成冻结工作。

最佳实践

• 把 MVCC 的四项健康指标纳入必测监控，并配上告警阈值：

-- MVCC 健康巡检大盘（建议做成监控采集项）
SELECT 'dead_tuple_pct' AS metric,
       round(100.0 * sum(n_dead_tup) / NULLIF(sum(n_live_tup + n_dead_tup), 0), 2)::text AS value
FROM pg_stat_user_tables
UNION ALL
SELECT 'db_xid_age',
       age(datfrozenxid)::text
FROM pg_database WHERE datname = current_database()
UNION ALL
SELECT 'oldest_xact_seconds',
       COALESCE(max(extract(epoch FROM now() - xact_start))::bigint, 0)::text
FROM pg_stat_activity WHERE xact_start IS NOT NULL
UNION ALL
SELECT 'inactive_replication_slots',
       count(*)::text
FROM pg_replication_slots WHERE NOT active;

        metric             | value
---------------------------+-------
 dead_tuple_pct            | 8.42
 db_xid_age                | 41234981
 oldest_xact_seconds       | 312
 inactive_replication_slots| 0

• 必须设置 idle_in_transaction_session_timeout，这是性价比最高的一项配置，能自动化解大部分长事务事故。

-- 应用连接池层面也设置一份，双保险
ALTER DATABASE demo SET idle_in_transaction_session_timeout = '10min';
ALTER DATABASE demo SET statement_timeout = '5min';
-- PostgreSQL 17+
ALTER DATABASE demo SET transaction_timeout = '30min';

• 按写入频率给表分级调参：高频更新表用激进的 autovacuum 参数 + 低 fillfactor。

-- 高频更新表的推荐配置
ALTER TABLE hot_table SET (
  fillfactor = 70,
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_vacuum_threshold = 500,
  autovacuum_vacuum_cost_delay = 0,
  autovacuum_analyze_scale_factor = 0.01
);

• 大表（千万行以上）必须下调 autovacuum_vacuum_scale_factor，否则默认的 20% 阈值意味着要积累数百万死元组才会触发。

# postgresql.conf：全局先把大表的基线压低
autovacuum_vacuum_scale_factor = 0.05
autovacuum_analyze_scale_factor = 0.02
autovacuum_max_workers = 6
autovacuum_naptime = 30s

• 归档用分区 DETACH + DROP，不要用 DELETE，可完全绕开 MVCC 成本。

-- 按月分区后，归档一个月 = 两次元数据操作
ALTER TABLE orders DETACH PARTITION orders_2024_01;
DROP TABLE orders_2024_01;   -- 瞬时完成，无死元组

• 把高频更新的「计数器类」字段拆出主表（或放到 Redis），是减少膨胀最有效的结构性手段。
• 用 pg_repack 而不是 VACUUM FULL 回收空间，前者在线、后者阻塞。
• 定期 REINDEX CONCURRENTLY 索引膨胀严重的表，VACUUM 不整理索引碎片。
• SERIALIZABLE 只用于真正需要防写偏斜的业务路径，并且必须实现重试；其余场景保持默认的 read committed。
• 用连接池的 idle_in_transaction 超时 + statement_timeout 双管齐下，从客户端侧就杜绝长事务。
• 理解并接受这笔交易：PostgreSQL 用「磁盘空间 + 维护工作」换来了「读写不互斥 + 一致性快照 + 大部分场景无死锁」。这个交换在绝大多数 OLTP 场景中非常划算——前提是你必须把 VACUUM 当核心组件维护，而不是当可选项。

-- 最后一步：把 MVCC 相关知识串起来自检
-- ① 我的库里有没有长期不提交的事务？
-- ② 我的高频更新表有没有专门配置 autovacuum 参数？
-- ③ 我的库 age(datfrozenxid) 是否在安全区间？
-- ④ 我的归档策略是否避开了大 DELETE？
SELECT current_database() AS db,
       (SELECT count(*) FROM pg_stat_activity
        WHERE xact_start < now() - interval '5 minutes')  AS long_txns,
       (SELECT count(*) FROM pg_class c
        JOIN pg_namespace n ON n.oid = c.relnamespace
        WHERE c.reloptions::text LIKE '%autovacuum%'
          AND n.nspname NOT IN ('pg_catalog', 'information_schema')) AS tables_with_autovacuum_opts,
       (SELECT age(datfrozenxid) FROM pg_database
        WHERE datname = current_database())               AS xid_age;

    db    | long_txns | tables_with_autovacuum_opts | xid_age
----------+-----------+-----------------------------+----------
 demo     |         0 |                          12 | 41234981

下一节

继续学习：WAL 概述（预写式日志）

参考链接

• 并发控制简介（Introduction to Concurrency Control）
• MVCC 实现细节（Multiversion Concurrency Control）
• 事务隔离（Transaction Isolation）
• 日常清理（Routine Vacuuming）
• 监控统计视图（Monitoring Database Activity）