PostgreSQL Dead Tuple 与索引膨胀深度解析

为什么一条简单的UPDATE会让数据库越来越慢一、根源MVCC 机制PostgreSQL 使用MVCC多版本并发控制Multi-Version Concurrency Control实现事务隔离。其核心思想是UPDATE 不是修改而是写一条新记录 将旧记录标记为死亡。这意味着每一次 UPDATE磁盘上都会多出一条数据。二、什么是 Dead Tuple执行一条 UPDATEUPDATEordersSETstatusdoneWHEREid1;PostgreSQL实际发生的事情【旧行】id1, statuspending → 标记为已删除dead tuple死元组 【新行】id1, statusdone → 插入一条全新的行旧行dead tuple并不会立刻从磁盘中抹除它依然静静地占据着存储空间等待后续被清理。Dead Tuple 的特征属性说明占用真实磁盘空间存在于数据页Page中不会自动消失对新事务不可见旧事务提交后新事务无法读取到它依赖 VACUUM 清理只有执行VACUUM操作后空间才会被回收为什么不直接删掉因为在并发场景下其他正在进行的事务可能仍然需要读取旧版本的数据快照隔离。PostgreSQL 必须保留旧行直到确认没有任何事务再需要它为止。三、什么是索引膨胀问题的产生每次 UPDATEPostgreSQL 除了在堆表Heap中写入新行还会在每一个相关索引中插入一条新的索引条目指向新行旧的索引条目指向 dead tuple不会立刻被清理继续占据索引空间。索引结构B-Tree示意 ├── id1 → 指向旧行dead tuple无效 └── id1 → 指向新行live tuple✅随着大量 UPDATE 的累积索引中堆满了指向 dead tuple 的无效条目索引文件体积持续膨胀。索引膨胀的危害问题说明索引文件变大占用更多磁盘空间查询性能下降扫描索引时需要跳过大量无效条目内存命中率降低Buffer Cache 被无效数据稀释有效数据被挤出写入速度变慢每次写入需要维护更庞大的索引结构四、大批量 UPDATE 为什么特别慢以下面这条 SQL 为例UPDATEmagellan_nkh_inboundSETversion_numberCONCAT(version_number,_zip)WHEREversion_number#{olderBatch}ANDNOTEXISTS(SELECT1FROMmagellan_nk_odh_inbound bWHEREb.version_number#{newerBatch}ANDb.sell_record_idmagellan_nk_odh_inbound.sell_record_id);一次性对千万级数据执行 UPDATE代价如下每更新 1 行 1 个 dead tuple 1 条新堆行 索引条目翻倍旧dead 新live 更新 1000 万行 1000 万 dead tuple 至少 2000 万个索引变动修改主键字段代价翻倍本例中(version_number, sell_record_id)是联合主键而 UPDATE 直接修改了version_number主键字段之一这导致主键索引需要全量重写等同于把整张表的主键索引翻了一遍所有依赖该主键的二级索引也需要同步更新。这是性能极差的根本原因。五、如何诊断与处理查看表的 Dead Tuple 情况SELECTschemaname,tablename,n_live_tupASlive_tuples,n_dead_tupASdead_tuples,ROUND(n_dead_tup*100.0/NULLIF(n_live_tupn_dead_tup,0),2)ASdead_ratio,last_autovacuum,last_vacuumFROMpg_stat_user_tablesWHEREtablenamemagellan_nk_odh_inbound;查看索引膨胀情况SELECTindexname,pg_size_pretty(pg_relation_size(indexrelid))ASindex_size,idx_scan,idx_tup_read,idx_tup_fetchFROMpg_stat_user_indexesWHEREtablenamemagellan_nk_odh_inboundORDERBYpg_relation_size(indexrelid)DESC;清理 Dead Tuple-- 普通清理不回收磁盘空间但标记空间可复用VACUUM magellan_nk_odh_inbound;-- 彻底清理并回收磁盘空间会锁表慎用VACUUMFULLmagellan_nk_odh_inbound;重建索引消除索引膨胀-- 不锁表重建推荐生产环境PostgreSQL 12REINDEXINDEXCONCURRENTLY idx_name;-- 重建表上所有索引不锁表REINDEXTABLECONCURRENTLY magellan_nk_odh_inbound;⚠️ 不带CONCURRENTLY的REINDEX会对表加排他锁生产环境请务必使用并发模式。六、总结一次 UPDATE 的完整代价 堆表Heap ├── 旧行 → [dead tuple占用空间] ←── VACUUM 清理后才释放 └── 新行 → [live tuple] 索引Index ├── 旧条目 → 指向 dead tuple ←── VACUUM 后才可回收 └── 新条目 → 指向 live tuple ✅ 结论 大量 UPDATE 大量 dead tuple 索引膨胀 表膨胀 查询/写入性能劣化千万级数据修改的最佳实践方案优点缺点一次性 UPDATE代码简单dead tuple 爆炸性能极差分批 DELETE INSERTdead tuple 可控性能稳定实现稍复杂推荐使用创建新表 数据迁移最彻底无碎片停机时间长适合离线操作核心原则千万级以上数据若需修改主键字段应优先考虑分批 DELETE INSERT并在每批次之间留出时间让autovacuum跟上清理节奏避免 dead tuple 堆积失控。