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

为什么一条简单的 UPDATE 会让数据库越来越慢？

一、根源：MVCC 机制

PostgreSQL 使用 MVCC（多版本并发控制，Multi-Version Concurrency Control） 实现事务隔离。

其核心思想是：

UPDATE 不是"修改"，而是"写一条新记录 + 将旧记录标记为死亡"。

这意味着每一次 UPDATE，磁盘上都会多出一条数据。

二、什么是 Dead Tuple？

执行一条 UPDATE

UPDATE orders SET status = 'done' WHERE id = 1;

PostgreSQL 实际发生的事情：

【旧行】id=1, status='pending' → 标记为"已删除"（dead tuple，死元组）
【新行】id=1, status='done' → 插入一条全新的行

旧行（dead tuple）并不会立刻从磁盘中抹除，它依然静静地占据着存储空间，等待后续被清理。

Dead Tuple 的特征

为什么不直接删掉？

因为在并发场景下，其他正在进行的事务可能仍然需要读取旧版本的数据（快照隔离）。PostgreSQL 必须保留旧行，直到确认没有任何事务再需要它为止。

三、什么是索引膨胀？

问题的产生

每次 UPDATE，PostgreSQL 除了在堆表（Heap）中写入新行，还会：

索引结构（B-Tree）示意：
├── id=1 → 指向旧行（dead tuple，无效）
└── id=1 → 指向新行（live tuple）✅

随着大量 UPDATE 的累积，索引中堆满了指向 dead tuple 的无效条目，索引文件体积持续膨胀。

索引膨胀的危害

四、大批量 UPDATE 为什么特别慢？

以下面这条 SQL 为例：

UPDATE magellan_nkh_inbound
SET version_number = CONCAT(version_number, '_zip')
WHERE version_number = #{olderBatch}
AND NOT EXISTS (
SELECT 1 FROM magellan_nk_odh_inbound b
WHERE b.version_number = #{newerBatch}
AND b.sell_record_id = magellan_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 情况

SELECT
schemaname,
tablename,
n_live_tup AS live_tuples,
n_dead_tup AS dead_tuples,
ROUND(n_dead_tup * 100.0 / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_ratio,
last_autovacuum,
last_vacuum
FROM pg_stat_user_tables
WHERE tablename = 'magellan_nk_odh_inbound';

查看索引膨胀情况

SELECT
indexname,
pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
idx_scan,
idx_tup_read,
idx_tup_fetch
FROM pg_stat_user_indexes
WHERE tablename = 'magellan_nk_odh_inbound'
ORDER BY pg_relation_size(indexrelid) DESC;

清理 Dead Tuple

— 普通清理（不回收磁盘空间，但标记空间可复用）
VACUUM magellan_nk_odh_inbound;

— 彻底清理并回收磁盘空间（会锁表，慎用）
VACUUM FULL magellan_nk_odh_inbound;

重建索引（消除索引膨胀）

— 不锁表重建（推荐生产环境，PostgreSQL 12+）
REINDEX INDEX CONCURRENTLY idx_name;

— 重建表上所有索引（不锁表）
REINDEX TABLE CONCURRENTLY magellan_nk_odh_inbound;

⚠️ 不带 CONCURRENTLY 的 REINDEX 会对表加排他锁，生产环境请务必使用并发模式。

六、总结

一次 UPDATE 的完整代价：

堆表（Heap）
├── 旧行 → [dead tuple，占用空间] ←── VACUUM 清理后才释放
└── 新行 → [live tuple]

索引（Index）
├── 旧条目 → 指向 dead tuple ←── VACUUM 后才可回收
└── 新条目 → 指向 live tuple ✅

结论：
大量 UPDATE = 大量 dead tuple = 索引膨胀 = 表膨胀 = 查询/写入性能劣化

千万级数据修改的最佳实践

核心原则：千万级以上数据若需修改主键字段，应优先考虑分批 DELETE + INSERT，并在每批次之间留出时间让 autovacuum 跟上清理节奏，避免 dead tuple 堆积失控。