PostgreSQL / 系统课时
理解索引膨胀成因
理解索引膨胀成因
本课解决一个常见误区:索引文件变大并不等于业务数据真的增长了同样多。你将从 UPDATE、DELETE 和持续写入出发,理解无效索引项、页面利用率下降以及正常增长与异常膨胀的区别。
学习目标
- 解释 UPDATE、DELETE 如何留下需要清理的索引项
- 区分索引正常增长、空间复用不足与异常膨胀
- 用 PostgreSQL 指标和 SQL 判断索引是否值得进一步治理
为什么写入会产生膨胀
PostgreSQL 使用 MVCC 保存事务可见性。对已经存在的行执行 UPDATE 时,通常会产生新的行版本;如果更新无法采用 HOT 更新,相关索引还需要建立新的索引项。DELETE 则会让原有索引项最终成为可回收的无效项。
因此,索引膨胀并不是“索引里保存了重复业务数据”这么简单,而是旧版本、页面空间和后续写入模式共同造成的物理空间利用变化。持续 UPDATE 或 DELETE 的表尤其容易出现这种现象。

HOT 更新为什么重要
并非所有 UPDATE 都会直接增加索引负担。若更新满足 HOT 条件,例如未修改被索引列,并且目标页面有足够空间,PostgreSQL 可以在同一数据页维护版本链,而不必为这次更新修改索引。写入频繁的表中,HOT 比例会直接影响索引增长速度。
可以先从统计信息观察更新模式,再结合表与索引大小判断是否存在持续增长压力。下面的查询只做诊断,不修改数据。
SELECT
relname,
n_tup_ins,
n_tup_upd,
n_tup_del,
n_tup_hot_upd
FROM pg_stat_user_tables
WHERE relname = 'orders';预期结果
relname | n_tup_ins | n_tup_upd | n_tup_del | n_tup_hot_upd
---------+-----------+-----------+-----------+---------------
orders | 100000 | 250000 | 80000 | 180000
(1 row)这里的数值仅用于示例,实际值会随业务运行而变化。若 UPDATE 很多而 HOT 更新长期偏低,应进一步检查被频繁修改的列是否建立了索引,以及页面是否缺乏可用空间。
正常增长与异常膨胀
索引变大本身不是故障。新增行持续增加、索引列基数上升、业务数据规模扩大,都可能让索引文件正常增长。真正需要关注的是:数据量变化与索引体积变化是否匹配,以及清理后能否有效复用已有页面空间。
判断时不要只看 pg_relation_size() 的绝对值。应同时观察表大小、索引大小、写入删除模式、HOT 更新情况和业务访问特征。一个大型且持续增长的索引可能完全正常;一个数据量基本稳定却长期扩大、利用率持续下降的索引,才更值得进一步做膨胀检测。
用尺寸和写入统计建立基线
可以先列出目标表及其索引大小,建立一个简单的基线。这个查询不会告诉你“膨胀比例”,但能帮助发现明显异常的体积变化。
SELECT
indexrelname AS index_name,
pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE relname = 'orders'
ORDER BY pg_relation_size(indexrelid) DESC;预期结果
index_name | index_size
-----------------------+------------
orders_pkey | 320 MB
idx_orders_customer | 280 MB
idx_orders_status | 96 MB
(3 rows)实际输出的索引名称和大小取决于数据库结构。基线的价值在于后续可以与写入量、表大小和治理操作前后的数据进行对照,而不是单凭一个尺寸值下结论。
什么时候应进一步检查膨胀
当表经历大量 UPDATE、DELETE、批量修正或周期性清理时,索引空间利用率下降的风险会明显增加。此时应进一步检查清理是否及时、索引是否长期难以复用空间,以及业务查询是否承受了额外的 I/O。
要特别避免“看到索引很大就重建”的做法。先建立数据量与写入模式基线,再验证空间是否真的存在异常浪费,才能判断需要普通 VACUUM、调整写入方式,还是进入更强的索引整理方案。膨胀治理的第一步不是缩文件,而是证明存在与业务规模不匹配的空间浪费。