PostgreSQL 使用多版本并发控制(MVCC)实现事务隔离，其实现方式与 MySQL InnoDB 有本质区别。PostgreSQL 将多个版本直接存储在表的数据页中，而非像 InnoDB 那样将旧版本存放在 Undo Log。

核心概念

元组(Tuple)的隐藏字段

PostgreSQL 中每行数据(元组)都包含以下隐藏系统列：

字段	说明
xmin	插入该元组的事务 ID
xmax	删除/更新该元组的事务 ID（0 表示未删除）
ctid	当前元组的物理位置 (page, offset)
cmin/cmax	同一事务内的命令序号
t_infomask	元组状态标志位

-- 查看隐藏字段
SELECT xmin, xmax, ctid, * FROM users;

--  xmin | xmax |  ctid  | id | name
-- ------+------+--------+----+-------
--   100 |    0 | (0,1)  |  1 | Alice
--   101 |  103 | (0,2)  |  2 | Bob
--   103 |    0 | (0,5)  |  2 | Bobby

事务 ID (XID)

元组可见性规则

PostgreSQL 通过比较元组的 xmin/xmax 与当前事务的快照(Snapshot)来判断可见性。

快照 (Snapshot)

-- Read Committed: 每条语句获取新快照
-- Repeatable Read: 事务开始时获取快照，整个事务使用

-- 快照包含三个信息：
-- xmin: 当前活跃事务中最小的 XID
-- xmax: 下一个将分配的 XID
-- xip_list: 当前活跃(未提交)的事务 ID 列表

UPDATE 和 DELETE 的实现

UPDATE = DELETE + INSERT

PostgreSQL 的 UPDATE 不是原地修改，而是将旧元组标记为删除并插入新元组：

-- 查看 UPDATE 的效果
-- 更新前
SELECT ctid, xmin, xmax, * FROM users WHERE id = 2;
-- (0,2) | 101 | 0 | 2 | Bob

-- 执行 UPDATE
BEGIN;
UPDATE users SET name = 'Bobby' WHERE id = 2;

-- 更新后（同一事务内可见新版本）
SELECT ctid, xmin, xmax, * FROM users WHERE id = 2;
-- (0,5) | 103 | 0 | 2 | Bobby
COMMIT;

DELETE

DELETE 只是将元组的 xmax 设为当前事务 ID，并不物理删除数据。

-- 删除只是标记 xmax
BEGIN;  -- txid = 105
DELETE FROM users WHERE id = 1;
-- 元组变为: xmin=100, xmax=105
COMMIT;
-- 物理删除等待 VACUUM

VACUUM 进程

由于旧版本元组一直存在于数据页中，需要 VACUUM 来回收"死元组"(dead tuple)占用的空间。

VACUUM 类型

类型	说明	锁级别
VACUUM	标记死元组空间为可重用	不阻塞读写
VACUUM FULL	重写整张表，回收空间	排他锁(ACCESS EXCLUSIVE)
VACUUM ANALYZE	VACUUM + 更新统计信息	不阻塞读写

autovacuum 配置

-- autovacuum 是否启用
ALTER SYSTEM SET autovacuum = on;

-- 触发 VACUUM 的阈值
-- 死元组数 > autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × 行数
ALTER SYSTEM SET autovacuum_vacuum_threshold = 50;
ALTER SYSTEM SET autovacuum_vacuum_scale_factor = 0.2;

-- 触发 ANALYZE 的阈值
ALTER SYSTEM SET autovacuum_analyze_threshold = 50;
ALTER SYSTEM SET autovacuum_analyze_scale_factor = 0.1;

-- autovacuum worker 数量
ALTER SYSTEM SET autovacuum_max_workers = 3;

-- 速率限制（防止影响业务）
ALTER SYSTEM SET autovacuum_vacuum_cost_limit = 200;
ALTER SYSTEM SET autovacuum_vacuum_cost_delay = '2ms';

-- 表级别配置
ALTER TABLE big_table SET (
    autovacuum_vacuum_scale_factor = 0.01,
    autovacuum_analyze_scale_factor = 0.005
);

HOT Update (Heap-Only Tuple)

HOT 是 PostgreSQL 对 UPDATE 的重要优化：当更新的列不在任何索引中，且新元组能存放在同一数据页时，可以避免更新索引。

HOT 生效条件：

1. 更新的列不属于任何索引
2. 新元组能放入与旧元组相同的页中
3. 表的 fillfactor 留有足够空间

-- 设置 fillfactor 为 HOT 预留空间
ALTER TABLE users SET (fillfactor = 80);
-- 每页只填充 80%，留 20% 给 HOT UPDATE

-- 查看 HOT 更新统计
SELECT
    relname,
    n_tup_upd,
    n_tup_hot_upd,
    ROUND(100.0 * n_tup_hot_upd / NULLIF(n_tup_upd, 0), 2) AS hot_pct
FROM pg_stat_user_tables
WHERE n_tup_upd > 0
ORDER BY n_tup_upd DESC;

事务 ID 回卷 (XID Wraparound)

PostgreSQL 的事务 ID 是 32 位无符号整数(约 42 亿)。当事务 ID 快要溢出时，需要通过 VACUUM FREEZE 将旧元组的 xmin 标记为"frozen"。

-- 查看当前 XID 和冻结年龄
SELECT datname,
       age(datfrozenxid) AS frozen_age,
       current_setting('autovacuum_freeze_max_age') AS freeze_max
FROM pg_database;

-- frozen_age 不应超过 autovacuum_freeze_max_age (默认 2 亿)
-- 超过时 autovacuum 会强制执行 anti-wraparound VACUUM

-- 紧急情况：手动 VACUUM FREEZE
VACUUM FREEZE big_table;

PostgreSQL vs MySQL MVCC 对比

维度	PostgreSQL	MySQL InnoDB
旧版本存储位置	表的数据页中(堆表)	Undo Log(独立空间)
UPDATE 方式	新增元组 + 标记旧元组	原地更新 + Undo Log 保存旧值
空间回收	需要 VACUUM	Purge Thread 自动清理 Undo
表膨胀	可能膨胀(需要 VACUUM)	不会膨胀(Undo 空间独立)
索引维护	UPDATE 可能更新所有索引(除 HOT)	仅更新变更列的索引
回滚速度	快(旧版本已在表中)	需要从 Undo Log 恢复
读性能	扫描可能遇到死元组	只存活数据，无死元组
写放大	较高(全行复制)	较低(仅记录变更)

监控与诊断

-- 查看表的死元组情况
SELECT
    schemaname,
    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,
    last_vacuum,
    last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

-- 查看表膨胀率（需要 pgstattuple 扩展）
CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT * FROM pgstattuple('users');

-- 查看长事务（阻止 VACUUM 回收）
SELECT
    pid,
    xact_start,
    age(now(), xact_start) AS duration,
    state,
    query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY xact_start;

总结

• PostgreSQL MVCC 将所有版本存储在表中，通过 xmin/xmax 判断可见性
• UPDATE 是"标记删除 + 插入新元组"，写放大较大但回滚更快
• VACUUM 是 PostgreSQL 运维的核心工作，必须正确配置 autovacuum
• HOT Update 通过避免索引更新显著提升 UPDATE 性能
• XID 回卷是严重的运维风险，需要监控 frozen age
• 与 MySQL 相比，PostgreSQL 的 MVCC 更简洁但需要更多的空间管理

贡献者

withesse

更新日志

2026/3/14 13:09

查看所有更新日志

• 9f6c2-feat: organize wiki content and refresh site setup于 2026/3/14

编辑此页