PostgreSQL MVCC 机制实战（第 2 篇）：一次 UPDATE 为什么留下两个行版本

订单表逻辑行数没有增长每天却执行数千万次状态刷新一段时间后 Heap 和索引都变大查询读到的仍然只有每个订单的最新一行。问题不在重复 INSERT而在 PostgreSQL 的UPDATE会写入新行版本旧版本要等到不再被任何相关快照需要后才能清理。MVCC 新版本解释表为什么变大HOT 只决定这次更新能否少维护一轮索引并不会把 UPDATE 变成原地覆盖。这篇文章不讨论 autovacuum 参数大全只证明三个结果逻辑一行为什么能对应多个物理版本、HOT 何时成立以及这些证据如何改变表和索引设计。第一组证据逻辑查询一行Heap Page 两个版本以下实验仅用于 PostgreSQL 18.6 测试库。pageinspect可以读取原始页面其中可能包含已经不可见的历史数据它通常需要较高权限不应让普通业务账号使用也不要对敏感生产表随意导出页面内容。CREATEEXTENSIONIFNOTEXISTSpageinspect;CREATETABLEorder_state(idbigintPRIMARYKEY,statustextNOTNULL,notetext)WITH(fillfactor70);INSERTINTOorder_stateVALUES(1,created,v1);SELECTctid,xmin,xmax,id,status,noteFROMorder_stateWHEREid1;这里的ctid是当前行版本在 Heap 中的物理位置由页号和页内 ItemId 组成它不是稳定业务主键。记录第一次查询的ctid再更新一个没有被索引引用的列UPDATEorder_stateSETnotev2WHEREid1;SELECTctid,xmin,xmax,id,status,noteFROMorder_stateWHEREid1;第二次查询仍只返回一行但ctid通常已经变化。普通 SELECT 按当前 MVCC 快照过滤不可见版本不能用它证明旧版本已被物理删除。现在读取当前行所在页面。实验表只有一行通常位于第 0 页若自行扩大数据量应先从可见行的ctid确定页号不能固定猜测页面SELECTlp,t_xmin,t_xmax,t_ctid,raw_flags,combined_flagsFROMheap_page_items(get_raw_page(order_state,0))AShCROSSJOINLATERAL heap_tuple_infomask_flags(h.t_infomask,h.t_infomask2)ASfWHEREh.t_infomaskISNOTNULLORDERBYlp;heap_page_items会展示页面上的所有元组不按当前快照隐藏旧版本。预期能看到更新前后两个行版本旧版本的t_xmax记录使其失效的事务新版本有新的t_xmin旧版本的t_ctid指向更新后的版本位置。不要机械解读某一位 flag。事务是否已提交、是否触发 hint bit、页面何时被再次访问都会影响具体标志。这个实验的硬证据是同一业务主键在页面上存在版本链而普通查询只返回当前可见版本。第二组证据有新行版本不一定有新索引项PostgreSQL 官方文档给出 HOT 的两个必要条件本次 UPDATE 没有修改任何普通索引引用的列核心内置访问方法中BRIN 作为 summarizing index 是例外旧行所在 Heap Page 有足够空间容纳新版本。刚才更新的是note表上只有主键索引且fillfactor70为页内更新预留空间所以它有机会成为 HOT Update。HOT 发生时索引仍通过原有 ItemId 进入 Heap再沿 HOT 链找到当前快照可见的版本不需要为新版本增加普通索引项。用累计统计观察而不是根据 SQL 形状猜测SELECTrelname,n_tup_upd,n_tup_hot_upd,round(100.0*n_tup_hot_upd/NULLIF(n_tup_upd,0),2)AShot_ratio_pctFROMpg_stat_user_tablesWHERErelnameorder_state;这些是累计且异步刷新的统计短实验中可能需要结束事务、重新查询或等待统计刷新。生产判断应记录时间窗口内的增量不能把数据库启动以来的累计比例直接当成当前发布效果。接着给status建索引并修改该列CREATEINDEXorder_state_status_idxONorder_state(status);UPDATEorder_stateSETstatuspaidWHEREid1;这次 UPDATE 修改了 B-tree 索引引用列不满足 HOT 条件即使原页面仍有空间也需要维护相应索引。再次读取pg_stat_user_tables应看到n_tup_upd增加而这次更新不增加n_tup_hot_upd。公平比较的共同前提是同一张表、同一行大小、同一页面空间和同一次单行更新只改变“被更新列是否被索引引用”。因此差异能归因于 HOT 资格而不是并发、缓存或数据规模变化。第三组证据fillfactor 提高机会不提供保证降低fillfactor会在装载页面时预留空间从而提高同页生成新版本的概率但不能保证每次 HOT行变大后可能仍放不进旧页页面空间可能被其他写入消耗更新任何普通索引引用列都会失去 HOT 资格表达式索引和部分索引同样可能引用业务列不能只看索引键名称更低 fillfactor 会让相同行数占用更多初始页面增加扫描与缓存压力。所以调低 fillfactor 不是“PostgreSQL 更新优化开关”。正确实验要比较同一更新负载下的 HOT 增量、表和索引尺寸、缓冲区读写、WAL、吞吐与尾延迟。从 UPDATE 到清理的最短机制链UPDATE 找到当前可见版本 → 在 Heap 写入新版本 → 旧版本记录 xmax新版本记录 xmin → 同页且不改普通索引引用列连接为 HOT 链 → 否则为新版本维护相关索引项 → WAL 记录变化 → 旧版本等待全局不再可见 → Page Pruning / VACUUM 清理可回收版本 → 空间优先留给关系内部复用HOT 带来两项收益避免为新版本新增普通索引项在一条 HOT 链多次更新时已经对所有事务不可见的中间版本可以在正常页面访问期间被剪枝不必全部等待周期性 VACUUM。它仍没有消除 MVCC 成本。新 Heap Tuple、WAL、版本可见性判断和最终清理依然存在。三个常见误判没有 DELETE就不会有 dead tuple错误。UPDATE 会使旧行版本过时。官方 VACUUM 文档明确把被 UPDATE 淘汰和被 DELETE 删除的版本都列为回收对象。HOT 比例低只要继续降低 fillfactor不一定。先检查更新列是否被主键、唯一索引、表达式索引或部分索引引用。如果资格条件已经失败页内空间再多也不会把这次更新变成 HOT。VACUUM 跑完文件就会缩小普通 VACUUM 的主要目标是清理不可见版本并把空间交给表内后续写入复用除文件尾部等特殊情况外不会把大部分空间归还操作系统。VACUUM FULL会重写整表并持有ACCESS EXCLUSIVE锁不能作为看到 dead tuple 后的默认动作。生产排查先用只读证据下面的查询读取累计统计不修改业务数据需要有权查看目标关系统计。它适合找候选热表不能单独证明物理膨胀也不能证明 autovacuum 已经失效。SELECTrelname,n_live_tup,n_dead_tup,n_tup_upd,n_tup_hot_upd,last_autovacuum,autovacuum_countFROMpg_stat_user_tablesWHEREn_tup_upd0ORDERBYn_dead_tupDESC;结果对应的下一步n_tup_upd高且 HOT 比例低核对索引定义和更新列再检查页空间dead tuple 持续净增长检查长事务、复制槽保留边界和 vacuum 吞吐HOT 比例高但索引仍增长继续检查删除、非 HOT 更新、索引类型和历史存量autovacuum 最近运行不代表清理成功结合日志中的 removable cutoff、仍不可删除版本和运行耗时。n_live_tup与n_dead_tup是估算不应用来做逐行对账。读取pageinspect比统计视图风险更高应留在隔离实验或经过审批的诊断中。设计动作必须按因果顺序识别真正高频更新列避免为低价值查询建立高写放大索引审查重复索引、表达式索引和部分索引谓词是否引用这些列在副本或压测环境比较不同 fillfactor不直接全表改参数后宣布成功让 autovacuum 的触发与吞吐追上旧版本生成速度治理长事务和异常复制槽否则旧版本可能根本尚不可回收同时验证业务写入结果、HOT 增量、索引增长、WAL、I/O 和尾延迟。修改 fillfactor 只影响后续页面填充策略不会自动重新组织已有页面。若为了立即改变现有物理布局而重写表必须单独评估锁、额外磁盘、WAL、复制追赶、灰度和回滚限制。实验边界与清理本实验能证明 UPDATE 产生新 Heap 版本以及更新索引引用列会失去 HOT 资格不能证明某个生产表膨胀只有这一种原因也不能给出通用 fillfactor 推荐值。测试结束后执行DROPTABLEorder_state;只有确认测试库中没有其他对象使用pageinspect时才考虑删除扩展DROPEXTENSION pageinspect;面试表达PostgreSQL UPDATE 会写新 Heap 版本旧版本按 MVCC 等待清理。未修改普通索引引用列且旧页有空间时可形成 HOT 链避免新增普通索引项但不会消除 Heap、WAL 和 VACUUM 成本。排障要联看索引引用列、HOT 增量、页空间、长快照和清理吞吐。官方资料Heap-Only TuplesDatabase Page LayoutpageinspectRoutine VacuumingVACUUMStatistics ViewsPostgreSQL 18.6 源码标签 REL_18_6