很多人对 PostgreSQL 的 MVCC 的理解停留在“多版本并发控制”这七个字上。面试能背出“读不阻塞写、写不阻塞读”但一进生产环境看到死元组比例涨到 60%、表的 XID 年龄逼近 3 亿、一张频繁 update 的表从 2GB 膨胀到 8GB就完全不知道这些都和 MVCC 有关系。这几个看似分散的现象底层其实是同一套机制在起作用。PostgreSQL 的 MVCC 不是一个抽象概念而是一整套具体的数据结构t_xmin、t_xmax、t_ctid、事务快照、clog、visibility map、VACUUM……每一个字段、每一种状态位都会直接决定你的数据库是稳定运行还是某天早上突然报警说磁盘满了。我打算从最物理的层面把这条链路完整串一遍然后给出几组可以真正跑在生产库上的体检 SQL 和调参经验。这篇文章适合三类人频繁被锁等待和表膨胀困扰的运维/DBA、想搞懂数据库内核的开发者、以及正在准备数据库面试的候选人。看完之后你不仅能解释“为什么 PostgreSQL 读不阻塞写”还能自己定位“为什么我的表一直瘦不下来”这类问题。1. MVCC到底在解决什么问题读、写、竞争1.1 一个两行钱的例子假设有一张用户余额表 user_balance里面某个账户余额是 100。事务 A 开始读余额事务 B 此时把余额改成 80 并提交随后 A 又读了一次用于页面展示。如果没有 MVCCA 的读必须等 B 写完或者只能在持有读锁的前提下读而 B 的写又必须等 A 读完。在秒杀、交易这类读多写少的高并发场景下锁竞争会直接拖垮业务的响应时间。MVCC 的答案非常直接B 写的时候不修改那行原始的 100而是在旁边再生成一行数据值为 80A 从自己的事务快照里仍然看到 100。A 提交之后下一个事务再来读自然看到 80。这就是 MVCC 最核心的价值写操作不阻塞读操作读操作也不阻塞写操作读写可以真正并行。1.2 为什么只靠锁方案会卡死业务不用任何并发控制是最简单的但会产生脏读、不可重复读、幻读等一系列问题。全加锁又是最粗暴的读和写互相排队并发度趋近于零。传统悲观锁方案的问题在于它默认冲突很频繁所以在每次读和写之前都要小心翼翼地获取锁。可现实业务里很多操作根本没有冲突比如两个事务读同一行、一个事务读另一个事务写过的旧版本这些本可以并行完成却被锁硬生生串行化了。MVCC 的思路是读操作根本不加锁而是通过快照决定“我能看到哪些版本”。写操作之间才需要真正的行锁来解决冲突这一点 PostgreSQL 并没有省掉。也就是说MVCC 把锁竞争从每条 SQL 减少到了“真正写同一行”的时候。1.3 和其他数据库 MVCC 的关键差别很多人会用 MySQL InnoDB 的经验去套 PostgreSQL这就容易踩坑。数据库旧版本存在哪谁来清理典型风险PostgreSQL堆表自己的数据页里VACUUM / autovacuum表膨胀、索引膨胀MySQL InnoDBundo log 回滚段purge 线程undo 膨胀、历史版本链过长Oracleundo 表空间自动 undo retention 管理undo 表空间不足PostgreSQL 选择把旧版本留在表自身的页里而不是放到独立的回滚段。这个设计让读路径非常干净读一个 tuple 时直接在页面里判断它的版本状态不需要去另一个存储区域回溯一条变更链。代价就是所有被更新、删除后留下的旧版本必须靠 VACUUM 机制来回收空间。理解这个差异是后面所有内容的基础。你看到 MySQL 删除大量行后磁盘空间立刻释放而 PostgreSQL 删除大量行后表文件不一定变小就是因为两者 MVCC 的实现模型根本不一样并不是 PostgreSQL 出了什么 bug。2. 堆表里的版本人生t_xmin、t_xmax、t_ctid与版本链2.1 一行数据在磁盘上长什么样PostgreSQL 里一行数据叫 HeapTuple它由 Header 和 Data 两部分组成。Header 里藏着 MVCC 最关键的几个字段我每次分析问题都先盯着它们看t_xmin插入这个版本的事务 ID。你可以把它理解成这个版本“出生”时留下的登记。t_xmax删除或锁定这个版本的事务 ID。如果是 0 或者无效值代表这个版本还没有被任何事务标记删除。t_ctid指向当前版本自己的物理位置或者指向更新后的下一个版本。t_infomask一组状态标志位用来缓存事务的提交、中止、冻结等信息。t_xmin 和 t_xmax 看起来只是一个数字但它们构成了整个 MVCC 世界的骨架。t_xmin 负责回答“这个版本是谁产生的”t_xmax 负责回答“这个版本是不是已经被某个事务处理掉了”。2.2 UPDATE和DELETE如何留下新版本我们用一个具体的场景模拟 PostgreSQL 里一行的变化过程。假设有一行数据(id1, nameAlice, balance5000)由事务 T100 插入。插入后它的头部状态大概是tuple v1: t_xmin 100, t_xmax 0, t_ctid (0, 5)t_xmax 为 0说明没有任何事务删除或更新它。此时这行是“活着”的。接着事务 T200 执行了UPDATE user_balance SET balance 6000 WHERE id 1。PostgreSQL 并不会在这行上原地修改数据而是生成一个新的 tupletuple v1: t_xmin 100, t_xmax 200, t_ctid (1, 3) -- 旧版本指向新版本 tuple v2: t_xmin 200, t_xmax 0, t_ctid (1, 3) -- 新版本自己指向自己旧版本的 t_xmax 变成了 200表示“T200 这个事务把我标记成不是最新版本了”新版本把自己的 t_xmin 写成了 200。通过旧版本的 t_ctid可以找到新版本。如果事务 T300 又执行了 DELETE同样不需要物理删除任何东西只需要在最新版本上标记tuple v2: t_xmin 200, t_xmax 300, t_ctid (1, 3) -- 最新版本被T300标记删除这条数据在逻辑上已经不存在了但物理上仍然占着页面空间。要等 VACUUM 真正扫描到这个页面把 t_xmax 不为 0 且对应事务已提交的死版本清理掉空间才会被回收复用。注意t_xmax 不只是用来记录更新和删除SELECT FOR UPDATE 这类行级锁也会往 t_xmax 里写事务 ID。所以看到 t_xmax 非零先别急着判断这行被删了还要结合 t_infomask 和事务状态判断具体类型。2.3 索引、ctid和回表索引为什么不保存版本信息PostgreSQL 的索引条目基本只包含键值和 ctid。索引本身不存 t_xmin、t_xmax 这些版本信息它只知道“这个键值指向堆表的这个位置”。所以一条普通 SQL 在走索引时实际要经历两步在索引里找到匹配的 ctid。根据这个 ctid 回表读取 HeapTuple再在 HeapTuple 上做完整的可见性判断。这也是为什么 PostgreSQL 里有 Index-Only Scan 这种特殊执行方式只有当 visibility map 告诉你某个页面内所有 tuple 对所有事务都可见时才能跳过回表直接用索引里的数据返回。HOTHeap-Only Tuple更新机制也和 ctid 有关。如果一次更新不涉及任何索引列新版本又恰好能放进旧版本所在的同一页面PostgreSQL 会直接让旧版本的 t_ctid 指向新版本索引条目继续指向旧版本的 ctid。查询时通过旧版本 t_ctid 一路找到新版本。这样索引不需要增加新条目能省下大量索引膨胀空间。后面第四章会专门展开。3. 可见性判断快照、clog与hint bits3.1 一张“活跃事务名单”事务快照快照是 MVCC 判断可见性的核心依据。它本质上是一张在某个时间点拍下的“活跃事务快照”代表当前数据库里哪些事务仍在运行、哪些已经结束。快照结构里几个关键概念xmin所有活跃事务中最小的事务 ID。xmax当前已分配事务 ID 的下一个值可以理解成“快照之后出现的事务都从这里开始”。xip_list仍处于运行状态的活跃事务 ID 列表。当一个事务开启后第一次读取数据时会获取一个快照之后所有可见性判断都基于这个快照。判断规则的主干可以这样概括如果 tuple 的 xid 大于等于 xmax说明这个事务在快照创建之后才出现不可见。如果 xid 在 xip_list 里说明这个事务还在运行中不可见。如果 xid 小于 xmin说明这个事务在快照创建前已经结束再根据提交状态决定可见性。如果 xid 在 xmin 和 xmax 之间但不在活跃列表里说明它在快照创建前已经提交或中止同样查提交状态决定。这个“快照”不是把整张表的数据复制一份它只是记下了当时活跃事务的名单。真正的版本数据一直躺在堆表里快照决定了你能不能看到它们。3.2 tuple状态判定的完整流程当一个元组进入可见性判断流程时PostgreSQL 会依次检查 t_xmin 和 t_xmax。我把主干逻辑简化如下if t_xmin 对应事务还在运行: return 不可见 # 未提交的数据不允许被其他事务看到 if t_xmin 对应事务未提交: return 不可见 if t_xmin 对应事务已中止: return 不可见 if t_xmin 对应事务已提交: if t_xmax 0 或 t_xmax 是无效状态: return 可见 # 没有更新也没有删除 if t_xmax 对应事务还在运行: return 可见 # 删除/更新还没提交我看到的是旧版本 if t_xmax 对应事务未提交: return 可见 if t_xmax 对应事务已提交: return 不可见 # 这个版本已经死掉需要沿 ctid 找新版本真实代码里还要处理子事务、当前事务自身、冻结标记等细节但主干就是这个逻辑。实际判断时也并不是每次都完整走一遍很多结果会被 t_infomask 状态位快速短路。3.3 隔离级别如何影响快照获取时机快照的获取时机直接决定了你在不同隔离级别下能看到什么。隔离级别快照获取时机典型行为READ UNCOMMITTED等同 READ COMMITTEDPostgreSQL 不区分 RD实际不允许脏读READ COMMITTED每条 SQL 语句开始时每条语句都能看到最新已提交版本会出现不可重复读REPEATABLE READ事务首个快照语句执行时整个事务复用同一个快照读到的数据版本保持一致SERIALIZABLE同 RR额外加 SSI 检测通过快照隔离加读写依赖检测保证可串行化很多人注意不到的一点是PostgreSQL 的 REPEATABLE READ 比 MySQL InnoDB 的 REPEATABLE READ 在“读一致性”上要严格。InnoDB 在 RR 下使用当前读时能看到新版本而 PostgreSQL 的普通 SELECT 从头到尾就是同一个快照除非出现 UPDATE 冲突基本不会出现幻读。SERIALIZABLE 级别则是在 REPEATABLE READ 的快照基础上加上了 SSISerializable Snapshot Isolation技术。它不再靠大范围锁去阻止一切交叉而是让事务正常并行只在实际可能产生非串行化交叉时抛出类似could not serialize access due to read/write dependencies的错误让应用重试。3.4 hint bits与clog为什么第二次查询比第一次快事务本身的状态存在哪里PostgreSQL 里有专门的提交日志早期叫 clog现在叫 pg_xact它记录了每个事务的最终状态进行中、已提交、已中止。如果每条 tuple 判断可见性时都要去查一次 pg_xact性能会很差而且会引入全局共享内存锁竞争。所以 PostgreSQL 在断定某个事务状态后会直接把结果写在 tuple 头部的 t_infomask 里这些缓存位叫 hint bits。比如HEAP_XMIN_COMMITTED表示 t_xmin 对应事务已经提交HEAP_XMIN_INVALID表示 t_xmin 对应事务已中止HEAP_XMAX_INVALID表示 t_xmax 无效即没有删除发生。这带来的现象很直观第一次扫描一张大表每个 tuple 都可能要查 clog 并写 hint bits开销高等 hint bits 写完之后第二次扫描同一批数据很多 tuple 一进判断流程就直接短路返回速度会快不少。我们在生产环境里观察“同一张表第一次查询慢第二次快”除了缓存和统计信息因素外hint bits 也贡献了一部分功劳。4. 膨胀、VACUUM与XID回卷MVCC的代价4.1 死元组为什么会把表撑大MVCC 的另一个名字叫“多版本”但每个版本都是要付房租的。房租就是磁盘空间。一条 UPDATE 会产生一个新的 tuple 版本同时让旧版本变成死元组。如果一张表每小时更新几十万行而死元组又没有被及时回收表的大小就会持续增长而活性数据的实际占用可能只有一小部分。最典型的场景是业务代码里有人写了循环 update 同一个大表或者批量任务在事务里更新了千万行都不提交。事务存活期间这些旧版本全部必须保留因为其他事务可能还需要看见旧状态。等事务提交后才开始积累死元组再由 autovacuum 在后台慢慢清扫。VACUUM 做的事情就是清理死元组把页面里残留的空闲空间重新登记到 Free Space Map让后续 INSERT/UPDATE 可以复用。如果不清理新数据不断往表尾追加表文件自然越撑越大。普通 VACUUM 之后表文件大小通常不会明显下降因为空间被还给了表内部而不是还给操作系统。只有表文件尾部的整页空页可能被 truncate 掉。这就是很多人产生困惑的地方跑完 VACUUM 发现表大小还是那么大以为 VACUUM 没生效其实空间已经被标记为可复用了。4.2 HOT更新唯一不增加索引膨胀的更新路径频繁更新索引列是索引膨胀的常见原因。比如一个订单表上有user_id status联合索引如果业务频繁更新 status 字段而 status 正好是索引列那每次 UPDATE 不仅要产生新版本还要在索引中加入新 ctid 条目。旧索引条目指向死元组VACUUM 再从索引里清理一来一回索引文件不断变大。如果更新不涉及索引列比如只更新amountPostgreSQL 可以走 HOT 更新路径。条件是新版本和旧版本落在同一数据页。更新没有改变任何索引列的值。页面里有足够的空闲空间容纳新版本。满足这三个条件时新版本就在旧版本旁边索引条目继续指向旧版本的 ctid查询通过 t_ctid 链找到新版本。HOT 更新不仅省了索引写入开销也让 VACUUM 在清理旧版本时不用扫大量冗余索引项。所以在做表设计时尽量把频繁更新的字段设计成非索引列这句话不是玄学而是直接关系到 MVCC 和 VACUUM 的长期健康。4.3 两种VACUUM打扫房间和搬家重写日常我们遇到的 vacuum 操作可以分成两类。普通 VACUUM 是“打扫房间”扫描页面、清理死元组、更新空闲空间映射不锁表可以和生产并行。它的问题是不会主动压缩表文件不会把分散在页面内部的大量空闲空间收拢并还给操作系统。VACUUM FULL 是“搬家重写”拿 ACCESS EXCLUSIVE 锁重写整个表文件把有效数据紧凑地搬到新文件里再把旧文件删掉。效果是表文件大小明显缩小空间真正返还给操作系统但代价是表会被完全锁住业务 DML 全部阻塞。我的建议是VACUUM FULL 绝不放在工作日高峰期做。如果你一定要在线压缩表优先考虑 pg_repack 这类扩展它通过建立临时表和触发器捕获增量变更来实现在线重写能避免长时间阻塞读写。普通 VACUUM 则是可以随时跑尤其在大事务刚结束时手动补一次 VACUUM 能大幅降低死元组堆积的风险。4.4 XID回卷一颗需要提前拆除的雷事务 ID 是 32 位的理论上有限。PG 引入了一个被称为“冻结”的机制当事务 ID 足够老旧时直接把 tuple 标记成HEAP_XMIN_FROZEN之后在可见性判断中不再比较该事务 ID 的大小这样老数据就能安然跨越 XID 回卷边界。冻结主要靠 VACUUM 完成。如果 autovacuum 长期来不及跑或者某些表被特意关闭了 autovacuum库的age(datfrozenxid)会持续增大逼近回卷极限。一旦进入危险区数据库会频繁报警甚至为了自我保护而拒绝分配新事务 ID导致业务全面停摆。执行下面这条 SQL 就能看到每个库的 XID 年龄SELECT datname, age(datfrozenxid) AS xid_age, current_setting(autovacuum_freeze_max_age) AS freeze_max_age FROM pg_database ORDER BY 2 DESC;正常情况下应该让 xid_age 远远低于 freeze_max_age。如果某些库的 xid_age 超过 1.5 亿就该立刻排查 autovacuum 为什么没接管这些表。常见的坑是有人给大表设置了autovacuum_enabled off事后忘记打开这是生产事故级别的隐患。5. 生产环境MVCC体检五条SQL和几个常见坑5.1 巡检三连死元组、表大小、XID年龄我在日常运维里会固定跑几组 SQL相当于给数据库做一年一度的体检。第一组是看死元组。死元组不是实时精确值但持续增长本身就是信号。SELECT schemaname, relname, n_live_tup, n_dead_tup, round(100 * n_dead_tup / NULLIF(n_live_tup n_dead_tup, 0)) AS dead_pct, last_vacuum, last_autovacuum FROM pg_stat_user_tables WHERE n_dead_tup 1000 ORDER BY n_dead_tup DESC;当 dead_pct 长期高于 30%或者单表死元组达到几百万说明 autovacuum 没跟上或者存在长事务阻塞了清理。第二组是看实际空间和估算空间的偏差。安装 pgstattuple 扩展后CREATE EXTENSION IF NOT EXISTS pgstattuple; SELECT * FROM pgstattuple_approx(public.t_big_order);重点关注dead_tuple_percent和free_percent。同时用pg_size_pretty(pg_total_relation_size(public.t_big_order))看当前物理大小。两者一对比就能估算表膨胀到了什么程度。第三组就是上一节提到的 XID 年龄检查。这三组 SQL 都应该放到数据库监控周期里每周至少跑一次。5.2 长事务一个发呆事务把整个库拖下水MVCC 的死元组能不能被 VACUUM 回收取决于“所有活跃事务中最早的那个的 XID”和死元组版本的关系。只要有一个老事务一直开着它的事务快照可能还需要看到那些旧版本VACUUM 就不能清理哪怕这个事务只是在页面上发呆什么 SQL 都不跑。生产事故里最典型的场景是有人用 Navicat 打开了一个事务连接忘了关闭或者代码里开启了事务却忘记 commit连接池里一直占着一个老事务。表面上 CPU 和内存都正常但表的死元组越来越大vacuum 始终清不动磁盘空间开始缓慢上涨。排查命令SELECT pid, usename, state, now() - xact_start AS xact_age, left(query, 100) AS query FROM pg_stat_activity WHERE xact_start IS NOT NULL AND state idle ORDER BY xact_start;这条 SQL 能看出哪些会话的活跃事务时间特别长。对于应用层我还建议直接设置idle_in_transaction_session_timeout比如 60 秒让只开着事务不干活的连接被自动断开避免一个发呆连接拖垮全局。大事务同样值得警惕。一个事务里 update 千万行在事务提交前几乎不可见提交后瞬间产生千万级死元组即使 autovacuum 火力全开也需要一定时间才能消化。批量更新应该拆成十万行一批提交给 VACUUM 留出喘息空间。5.3 手动VACUUM的正确打开方式autovacuum 是默认开启的但它不是万能的。遇到下面几类情况手动 VACUUM 会更及时刚刚跑完一个超大事务死元组激增。业务低峰期你想主动控制清理时间避免 autovacuum 在高峰期抢 I/O。表已经被长事务锁了很长一段长事务刚释放。手动执行普通 VACUUM 的语法很简单VACUUM (VERBOSE, ANALYZE) public.t_big_order;普通 VACUUM 不锁表可以放心跑。但不要对它期望太高它不会显著缩小表文件。如果确实需要缩小表文件VACUUM FULL 必须放在维护窗口里。更推荐的做法是在 PostgreSQL 12 及以上版本把大表改成分区表把历史数据和热点数据分开分区的清理压力会小很多也更容易单独维护。5.4 索引膨胀诊断与在线重建索引膨胀不像堆表那么容易被发现但一旦发生影响比同等的堆表膨胀更隐蔽。因为查询即使只返回 100 行如果索引有 1 亿条死条目扫描成本也会很高。判断索引是否膨胀最简单的方式是同时看堆表和索引的物理大小SELECT t.relname AS tbl, i.indexrelname AS idx, pg_size_pretty(pg_relation_size(t.oid)) AS tbl_size, pg_size_pretty(pg_relation_size(i.indexrelid)) AS idx_size FROM pg_class t JOIN pg_stat_user_indexes i ON t.oid i.relid ORDER BY pg_relation_size(i.indexrelid) DESC LIMIT 20;如果某个索引的大小接近甚至超过堆表本身而业务逻辑又没有那么多唯一列就要留意是否因为频繁更新索引列导致索引膨胀。重建索引现在最稳妥的方式是REINDEX INDEX CONCURRENTLY idx_orders_user_id;REINDEX CONCURRENTLY 不会长时间阻塞 DML但代价是它会完整做两遍索引扫描I/O 和临时空间占用明显高于普通 REINDEX。所以最好放在半夜执行并确保磁盘有足够余量。重建过程中如果发生冲突导致索引损坏可以再用REINDEX修复但操作时最好做好监控。PG 14 以及之后的版本在 vacuum 的并行化、索引清理方面做了不少改进autovacuum 对大表的处理能力也更强。如果你还在 12 以下的版本并且长期被膨胀问题折磨升级带来的收益会非常直接。我在实际生产环境里踩过的坑几乎都绕不开一个共同原因理解 MVCC 时只记住了概念没有去追踪它落在地盘上的真实数据。最后再分享一个小技巧给核心表建一个定期巡检任务每次把 pg_stat_user_tables、pg_stat_activity、age(datfrozenxid) 这几组数据存档连续观察两周你会发现很多“偶发性”问题其实有非常清晰的增长曲线。先把这个曲线看懂再去做参数调优比单纯抄别人的 autovacuum 配置可靠得多。
PostgreSQL MVCC 原理与生产实践：版本链、表膨胀与XID回卷
很多人对 PostgreSQL 的 MVCC 的理解停留在“多版本并发控制”这七个字上。面试能背出“读不阻塞写、写不阻塞读”但一进生产环境看到死元组比例涨到 60%、表的 XID 年龄逼近 3 亿、一张频繁 update 的表从 2GB 膨胀到 8GB就完全不知道这些都和 MVCC 有关系。这几个看似分散的现象底层其实是同一套机制在起作用。PostgreSQL 的 MVCC 不是一个抽象概念而是一整套具体的数据结构t_xmin、t_xmax、t_ctid、事务快照、clog、visibility map、VACUUM……每一个字段、每一种状态位都会直接决定你的数据库是稳定运行还是某天早上突然报警说磁盘满了。我打算从最物理的层面把这条链路完整串一遍然后给出几组可以真正跑在生产库上的体检 SQL 和调参经验。这篇文章适合三类人频繁被锁等待和表膨胀困扰的运维/DBA、想搞懂数据库内核的开发者、以及正在准备数据库面试的候选人。看完之后你不仅能解释“为什么 PostgreSQL 读不阻塞写”还能自己定位“为什么我的表一直瘦不下来”这类问题。1. MVCC到底在解决什么问题读、写、竞争1.1 一个两行钱的例子假设有一张用户余额表 user_balance里面某个账户余额是 100。事务 A 开始读余额事务 B 此时把余额改成 80 并提交随后 A 又读了一次用于页面展示。如果没有 MVCCA 的读必须等 B 写完或者只能在持有读锁的前提下读而 B 的写又必须等 A 读完。在秒杀、交易这类读多写少的高并发场景下锁竞争会直接拖垮业务的响应时间。MVCC 的答案非常直接B 写的时候不修改那行原始的 100而是在旁边再生成一行数据值为 80A 从自己的事务快照里仍然看到 100。A 提交之后下一个事务再来读自然看到 80。这就是 MVCC 最核心的价值写操作不阻塞读操作读操作也不阻塞写操作读写可以真正并行。1.2 为什么只靠锁方案会卡死业务不用任何并发控制是最简单的但会产生脏读、不可重复读、幻读等一系列问题。全加锁又是最粗暴的读和写互相排队并发度趋近于零。传统悲观锁方案的问题在于它默认冲突很频繁所以在每次读和写之前都要小心翼翼地获取锁。可现实业务里很多操作根本没有冲突比如两个事务读同一行、一个事务读另一个事务写过的旧版本这些本可以并行完成却被锁硬生生串行化了。MVCC 的思路是读操作根本不加锁而是通过快照决定“我能看到哪些版本”。写操作之间才需要真正的行锁来解决冲突这一点 PostgreSQL 并没有省掉。也就是说MVCC 把锁竞争从每条 SQL 减少到了“真正写同一行”的时候。1.3 和其他数据库 MVCC 的关键差别很多人会用 MySQL InnoDB 的经验去套 PostgreSQL这就容易踩坑。数据库旧版本存在哪谁来清理典型风险PostgreSQL堆表自己的数据页里VACUUM / autovacuum表膨胀、索引膨胀MySQL InnoDBundo log 回滚段purge 线程undo 膨胀、历史版本链过长Oracleundo 表空间自动 undo retention 管理undo 表空间不足PostgreSQL 选择把旧版本留在表自身的页里而不是放到独立的回滚段。这个设计让读路径非常干净读一个 tuple 时直接在页面里判断它的版本状态不需要去另一个存储区域回溯一条变更链。代价就是所有被更新、删除后留下的旧版本必须靠 VACUUM 机制来回收空间。理解这个差异是后面所有内容的基础。你看到 MySQL 删除大量行后磁盘空间立刻释放而 PostgreSQL 删除大量行后表文件不一定变小就是因为两者 MVCC 的实现模型根本不一样并不是 PostgreSQL 出了什么 bug。2. 堆表里的版本人生t_xmin、t_xmax、t_ctid与版本链2.1 一行数据在磁盘上长什么样PostgreSQL 里一行数据叫 HeapTuple它由 Header 和 Data 两部分组成。Header 里藏着 MVCC 最关键的几个字段我每次分析问题都先盯着它们看t_xmin插入这个版本的事务 ID。你可以把它理解成这个版本“出生”时留下的登记。t_xmax删除或锁定这个版本的事务 ID。如果是 0 或者无效值代表这个版本还没有被任何事务标记删除。t_ctid指向当前版本自己的物理位置或者指向更新后的下一个版本。t_infomask一组状态标志位用来缓存事务的提交、中止、冻结等信息。t_xmin 和 t_xmax 看起来只是一个数字但它们构成了整个 MVCC 世界的骨架。t_xmin 负责回答“这个版本是谁产生的”t_xmax 负责回答“这个版本是不是已经被某个事务处理掉了”。2.2 UPDATE和DELETE如何留下新版本我们用一个具体的场景模拟 PostgreSQL 里一行的变化过程。假设有一行数据(id1, nameAlice, balance5000)由事务 T100 插入。插入后它的头部状态大概是tuple v1: t_xmin 100, t_xmax 0, t_ctid (0, 5)t_xmax 为 0说明没有任何事务删除或更新它。此时这行是“活着”的。接着事务 T200 执行了UPDATE user_balance SET balance 6000 WHERE id 1。PostgreSQL 并不会在这行上原地修改数据而是生成一个新的 tupletuple v1: t_xmin 100, t_xmax 200, t_ctid (1, 3) -- 旧版本指向新版本 tuple v2: t_xmin 200, t_xmax 0, t_ctid (1, 3) -- 新版本自己指向自己旧版本的 t_xmax 变成了 200表示“T200 这个事务把我标记成不是最新版本了”新版本把自己的 t_xmin 写成了 200。通过旧版本的 t_ctid可以找到新版本。如果事务 T300 又执行了 DELETE同样不需要物理删除任何东西只需要在最新版本上标记tuple v2: t_xmin 200, t_xmax 300, t_ctid (1, 3) -- 最新版本被T300标记删除这条数据在逻辑上已经不存在了但物理上仍然占着页面空间。要等 VACUUM 真正扫描到这个页面把 t_xmax 不为 0 且对应事务已提交的死版本清理掉空间才会被回收复用。注意t_xmax 不只是用来记录更新和删除SELECT FOR UPDATE 这类行级锁也会往 t_xmax 里写事务 ID。所以看到 t_xmax 非零先别急着判断这行被删了还要结合 t_infomask 和事务状态判断具体类型。2.3 索引、ctid和回表索引为什么不保存版本信息PostgreSQL 的索引条目基本只包含键值和 ctid。索引本身不存 t_xmin、t_xmax 这些版本信息它只知道“这个键值指向堆表的这个位置”。所以一条普通 SQL 在走索引时实际要经历两步在索引里找到匹配的 ctid。根据这个 ctid 回表读取 HeapTuple再在 HeapTuple 上做完整的可见性判断。这也是为什么 PostgreSQL 里有 Index-Only Scan 这种特殊执行方式只有当 visibility map 告诉你某个页面内所有 tuple 对所有事务都可见时才能跳过回表直接用索引里的数据返回。HOTHeap-Only Tuple更新机制也和 ctid 有关。如果一次更新不涉及任何索引列新版本又恰好能放进旧版本所在的同一页面PostgreSQL 会直接让旧版本的 t_ctid 指向新版本索引条目继续指向旧版本的 ctid。查询时通过旧版本 t_ctid 一路找到新版本。这样索引不需要增加新条目能省下大量索引膨胀空间。后面第四章会专门展开。3. 可见性判断快照、clog与hint bits3.1 一张“活跃事务名单”事务快照快照是 MVCC 判断可见性的核心依据。它本质上是一张在某个时间点拍下的“活跃事务快照”代表当前数据库里哪些事务仍在运行、哪些已经结束。快照结构里几个关键概念xmin所有活跃事务中最小的事务 ID。xmax当前已分配事务 ID 的下一个值可以理解成“快照之后出现的事务都从这里开始”。xip_list仍处于运行状态的活跃事务 ID 列表。当一个事务开启后第一次读取数据时会获取一个快照之后所有可见性判断都基于这个快照。判断规则的主干可以这样概括如果 tuple 的 xid 大于等于 xmax说明这个事务在快照创建之后才出现不可见。如果 xid 在 xip_list 里说明这个事务还在运行中不可见。如果 xid 小于 xmin说明这个事务在快照创建前已经结束再根据提交状态决定可见性。如果 xid 在 xmin 和 xmax 之间但不在活跃列表里说明它在快照创建前已经提交或中止同样查提交状态决定。这个“快照”不是把整张表的数据复制一份它只是记下了当时活跃事务的名单。真正的版本数据一直躺在堆表里快照决定了你能不能看到它们。3.2 tuple状态判定的完整流程当一个元组进入可见性判断流程时PostgreSQL 会依次检查 t_xmin 和 t_xmax。我把主干逻辑简化如下if t_xmin 对应事务还在运行: return 不可见 # 未提交的数据不允许被其他事务看到 if t_xmin 对应事务未提交: return 不可见 if t_xmin 对应事务已中止: return 不可见 if t_xmin 对应事务已提交: if t_xmax 0 或 t_xmax 是无效状态: return 可见 # 没有更新也没有删除 if t_xmax 对应事务还在运行: return 可见 # 删除/更新还没提交我看到的是旧版本 if t_xmax 对应事务未提交: return 可见 if t_xmax 对应事务已提交: return 不可见 # 这个版本已经死掉需要沿 ctid 找新版本真实代码里还要处理子事务、当前事务自身、冻结标记等细节但主干就是这个逻辑。实际判断时也并不是每次都完整走一遍很多结果会被 t_infomask 状态位快速短路。3.3 隔离级别如何影响快照获取时机快照的获取时机直接决定了你在不同隔离级别下能看到什么。隔离级别快照获取时机典型行为READ UNCOMMITTED等同 READ COMMITTEDPostgreSQL 不区分 RD实际不允许脏读READ COMMITTED每条 SQL 语句开始时每条语句都能看到最新已提交版本会出现不可重复读REPEATABLE READ事务首个快照语句执行时整个事务复用同一个快照读到的数据版本保持一致SERIALIZABLE同 RR额外加 SSI 检测通过快照隔离加读写依赖检测保证可串行化很多人注意不到的一点是PostgreSQL 的 REPEATABLE READ 比 MySQL InnoDB 的 REPEATABLE READ 在“读一致性”上要严格。InnoDB 在 RR 下使用当前读时能看到新版本而 PostgreSQL 的普通 SELECT 从头到尾就是同一个快照除非出现 UPDATE 冲突基本不会出现幻读。SERIALIZABLE 级别则是在 REPEATABLE READ 的快照基础上加上了 SSISerializable Snapshot Isolation技术。它不再靠大范围锁去阻止一切交叉而是让事务正常并行只在实际可能产生非串行化交叉时抛出类似could not serialize access due to read/write dependencies的错误让应用重试。3.4 hint bits与clog为什么第二次查询比第一次快事务本身的状态存在哪里PostgreSQL 里有专门的提交日志早期叫 clog现在叫 pg_xact它记录了每个事务的最终状态进行中、已提交、已中止。如果每条 tuple 判断可见性时都要去查一次 pg_xact性能会很差而且会引入全局共享内存锁竞争。所以 PostgreSQL 在断定某个事务状态后会直接把结果写在 tuple 头部的 t_infomask 里这些缓存位叫 hint bits。比如HEAP_XMIN_COMMITTED表示 t_xmin 对应事务已经提交HEAP_XMIN_INVALID表示 t_xmin 对应事务已中止HEAP_XMAX_INVALID表示 t_xmax 无效即没有删除发生。这带来的现象很直观第一次扫描一张大表每个 tuple 都可能要查 clog 并写 hint bits开销高等 hint bits 写完之后第二次扫描同一批数据很多 tuple 一进判断流程就直接短路返回速度会快不少。我们在生产环境里观察“同一张表第一次查询慢第二次快”除了缓存和统计信息因素外hint bits 也贡献了一部分功劳。4. 膨胀、VACUUM与XID回卷MVCC的代价4.1 死元组为什么会把表撑大MVCC 的另一个名字叫“多版本”但每个版本都是要付房租的。房租就是磁盘空间。一条 UPDATE 会产生一个新的 tuple 版本同时让旧版本变成死元组。如果一张表每小时更新几十万行而死元组又没有被及时回收表的大小就会持续增长而活性数据的实际占用可能只有一小部分。最典型的场景是业务代码里有人写了循环 update 同一个大表或者批量任务在事务里更新了千万行都不提交。事务存活期间这些旧版本全部必须保留因为其他事务可能还需要看见旧状态。等事务提交后才开始积累死元组再由 autovacuum 在后台慢慢清扫。VACUUM 做的事情就是清理死元组把页面里残留的空闲空间重新登记到 Free Space Map让后续 INSERT/UPDATE 可以复用。如果不清理新数据不断往表尾追加表文件自然越撑越大。普通 VACUUM 之后表文件大小通常不会明显下降因为空间被还给了表内部而不是还给操作系统。只有表文件尾部的整页空页可能被 truncate 掉。这就是很多人产生困惑的地方跑完 VACUUM 发现表大小还是那么大以为 VACUUM 没生效其实空间已经被标记为可复用了。4.2 HOT更新唯一不增加索引膨胀的更新路径频繁更新索引列是索引膨胀的常见原因。比如一个订单表上有user_id status联合索引如果业务频繁更新 status 字段而 status 正好是索引列那每次 UPDATE 不仅要产生新版本还要在索引中加入新 ctid 条目。旧索引条目指向死元组VACUUM 再从索引里清理一来一回索引文件不断变大。如果更新不涉及索引列比如只更新amountPostgreSQL 可以走 HOT 更新路径。条件是新版本和旧版本落在同一数据页。更新没有改变任何索引列的值。页面里有足够的空闲空间容纳新版本。满足这三个条件时新版本就在旧版本旁边索引条目继续指向旧版本的 ctid查询通过 t_ctid 链找到新版本。HOT 更新不仅省了索引写入开销也让 VACUUM 在清理旧版本时不用扫大量冗余索引项。所以在做表设计时尽量把频繁更新的字段设计成非索引列这句话不是玄学而是直接关系到 MVCC 和 VACUUM 的长期健康。4.3 两种VACUUM打扫房间和搬家重写日常我们遇到的 vacuum 操作可以分成两类。普通 VACUUM 是“打扫房间”扫描页面、清理死元组、更新空闲空间映射不锁表可以和生产并行。它的问题是不会主动压缩表文件不会把分散在页面内部的大量空闲空间收拢并还给操作系统。VACUUM FULL 是“搬家重写”拿 ACCESS EXCLUSIVE 锁重写整个表文件把有效数据紧凑地搬到新文件里再把旧文件删掉。效果是表文件大小明显缩小空间真正返还给操作系统但代价是表会被完全锁住业务 DML 全部阻塞。我的建议是VACUUM FULL 绝不放在工作日高峰期做。如果你一定要在线压缩表优先考虑 pg_repack 这类扩展它通过建立临时表和触发器捕获增量变更来实现在线重写能避免长时间阻塞读写。普通 VACUUM 则是可以随时跑尤其在大事务刚结束时手动补一次 VACUUM 能大幅降低死元组堆积的风险。4.4 XID回卷一颗需要提前拆除的雷事务 ID 是 32 位的理论上有限。PG 引入了一个被称为“冻结”的机制当事务 ID 足够老旧时直接把 tuple 标记成HEAP_XMIN_FROZEN之后在可见性判断中不再比较该事务 ID 的大小这样老数据就能安然跨越 XID 回卷边界。冻结主要靠 VACUUM 完成。如果 autovacuum 长期来不及跑或者某些表被特意关闭了 autovacuum库的age(datfrozenxid)会持续增大逼近回卷极限。一旦进入危险区数据库会频繁报警甚至为了自我保护而拒绝分配新事务 ID导致业务全面停摆。执行下面这条 SQL 就能看到每个库的 XID 年龄SELECT datname, age(datfrozenxid) AS xid_age, current_setting(autovacuum_freeze_max_age) AS freeze_max_age FROM pg_database ORDER BY 2 DESC;正常情况下应该让 xid_age 远远低于 freeze_max_age。如果某些库的 xid_age 超过 1.5 亿就该立刻排查 autovacuum 为什么没接管这些表。常见的坑是有人给大表设置了autovacuum_enabled off事后忘记打开这是生产事故级别的隐患。5. 生产环境MVCC体检五条SQL和几个常见坑5.1 巡检三连死元组、表大小、XID年龄我在日常运维里会固定跑几组 SQL相当于给数据库做一年一度的体检。第一组是看死元组。死元组不是实时精确值但持续增长本身就是信号。SELECT schemaname, relname, n_live_tup, n_dead_tup, round(100 * n_dead_tup / NULLIF(n_live_tup n_dead_tup, 0)) AS dead_pct, last_vacuum, last_autovacuum FROM pg_stat_user_tables WHERE n_dead_tup 1000 ORDER BY n_dead_tup DESC;当 dead_pct 长期高于 30%或者单表死元组达到几百万说明 autovacuum 没跟上或者存在长事务阻塞了清理。第二组是看实际空间和估算空间的偏差。安装 pgstattuple 扩展后CREATE EXTENSION IF NOT EXISTS pgstattuple; SELECT * FROM pgstattuple_approx(public.t_big_order);重点关注dead_tuple_percent和free_percent。同时用pg_size_pretty(pg_total_relation_size(public.t_big_order))看当前物理大小。两者一对比就能估算表膨胀到了什么程度。第三组就是上一节提到的 XID 年龄检查。这三组 SQL 都应该放到数据库监控周期里每周至少跑一次。5.2 长事务一个发呆事务把整个库拖下水MVCC 的死元组能不能被 VACUUM 回收取决于“所有活跃事务中最早的那个的 XID”和死元组版本的关系。只要有一个老事务一直开着它的事务快照可能还需要看到那些旧版本VACUUM 就不能清理哪怕这个事务只是在页面上发呆什么 SQL 都不跑。生产事故里最典型的场景是有人用 Navicat 打开了一个事务连接忘了关闭或者代码里开启了事务却忘记 commit连接池里一直占着一个老事务。表面上 CPU 和内存都正常但表的死元组越来越大vacuum 始终清不动磁盘空间开始缓慢上涨。排查命令SELECT pid, usename, state, now() - xact_start AS xact_age, left(query, 100) AS query FROM pg_stat_activity WHERE xact_start IS NOT NULL AND state idle ORDER BY xact_start;这条 SQL 能看出哪些会话的活跃事务时间特别长。对于应用层我还建议直接设置idle_in_transaction_session_timeout比如 60 秒让只开着事务不干活的连接被自动断开避免一个发呆连接拖垮全局。大事务同样值得警惕。一个事务里 update 千万行在事务提交前几乎不可见提交后瞬间产生千万级死元组即使 autovacuum 火力全开也需要一定时间才能消化。批量更新应该拆成十万行一批提交给 VACUUM 留出喘息空间。5.3 手动VACUUM的正确打开方式autovacuum 是默认开启的但它不是万能的。遇到下面几类情况手动 VACUUM 会更及时刚刚跑完一个超大事务死元组激增。业务低峰期你想主动控制清理时间避免 autovacuum 在高峰期抢 I/O。表已经被长事务锁了很长一段长事务刚释放。手动执行普通 VACUUM 的语法很简单VACUUM (VERBOSE, ANALYZE) public.t_big_order;普通 VACUUM 不锁表可以放心跑。但不要对它期望太高它不会显著缩小表文件。如果确实需要缩小表文件VACUUM FULL 必须放在维护窗口里。更推荐的做法是在 PostgreSQL 12 及以上版本把大表改成分区表把历史数据和热点数据分开分区的清理压力会小很多也更容易单独维护。5.4 索引膨胀诊断与在线重建索引膨胀不像堆表那么容易被发现但一旦发生影响比同等的堆表膨胀更隐蔽。因为查询即使只返回 100 行如果索引有 1 亿条死条目扫描成本也会很高。判断索引是否膨胀最简单的方式是同时看堆表和索引的物理大小SELECT t.relname AS tbl, i.indexrelname AS idx, pg_size_pretty(pg_relation_size(t.oid)) AS tbl_size, pg_size_pretty(pg_relation_size(i.indexrelid)) AS idx_size FROM pg_class t JOIN pg_stat_user_indexes i ON t.oid i.relid ORDER BY pg_relation_size(i.indexrelid) DESC LIMIT 20;如果某个索引的大小接近甚至超过堆表本身而业务逻辑又没有那么多唯一列就要留意是否因为频繁更新索引列导致索引膨胀。重建索引现在最稳妥的方式是REINDEX INDEX CONCURRENTLY idx_orders_user_id;REINDEX CONCURRENTLY 不会长时间阻塞 DML但代价是它会完整做两遍索引扫描I/O 和临时空间占用明显高于普通 REINDEX。所以最好放在半夜执行并确保磁盘有足够余量。重建过程中如果发生冲突导致索引损坏可以再用REINDEX修复但操作时最好做好监控。PG 14 以及之后的版本在 vacuum 的并行化、索引清理方面做了不少改进autovacuum 对大表的处理能力也更强。如果你还在 12 以下的版本并且长期被膨胀问题折磨升级带来的收益会非常直接。我在实际生产环境里踩过的坑几乎都绕不开一个共同原因理解 MVCC 时只记住了概念没有去追踪它落在地盘上的真实数据。最后再分享一个小技巧给核心表建一个定期巡检任务每次把 pg_stat_user_tables、pg_stat_activity、age(datfrozenxid) 这几组数据存档连续观察两周你会发现很多“偶发性”问题其实有非常清晰的增长曲线。先把这个曲线看懂再去做参数调优比单纯抄别人的 autovacuum 配置可靠得多。