PostgreSQL / 系统课时

理解索引膨胀成因

理解索引膨胀成因

本课解决一个常见误区:索引文件变大并不等于业务数据真的增长了同样多。你将从 UPDATE、DELETE 和持续写入出发,理解无效索引项、页面利用率下降以及正常增长与异常膨胀的区别。

学习目标

  • 解释 UPDATE、DELETE 如何留下需要清理的索引项
  • 区分索引正常增长、空间复用不足与异常膨胀
  • 用 PostgreSQL 指标和 SQL 判断索引是否值得进一步治理

为什么写入会产生膨胀

PostgreSQL 使用 MVCC 保存事务可见性。对已经存在的行执行 UPDATE 时,通常会产生新的行版本;如果更新无法采用 HOT 更新,相关索引还需要建立新的索引项。DELETE 则会让原有索引项最终成为可回收的无效项。

因此,索引膨胀并不是“索引里保存了重复业务数据”这么简单,而是旧版本、页面空间和后续写入模式共同造成的物理空间利用变化。持续 UPDATE 或 DELETE 的表尤其容易出现这种现象。

UPDATE、DELETE 与索引空间利用率变化示意

HOT 更新为什么重要

并非所有 UPDATE 都会直接增加索引负担。若更新满足 HOT 条件,例如未修改被索引列,并且目标页面有足够空间,PostgreSQL 可以在同一数据页维护版本链,而不必为这次更新修改索引。写入频繁的表中,HOT 比例会直接影响索引增长速度。

可以先从统计信息观察更新模式,再结合表与索引大小判断是否存在持续增长压力。下面的查询只做诊断,不修改数据。

sql
SELECT
    relname,
    n_tup_ins,
    n_tup_upd,
    n_tup_del,
    n_tup_hot_upd
FROM pg_stat_user_tables
WHERE relname = 'orders';

预期结果

text
 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 更新情况和业务访问特征。一个大型且持续增长的索引可能完全正常;一个数据量基本稳定却长期扩大、利用率持续下降的索引,才更值得进一步做膨胀检测。

用尺寸和写入统计建立基线

可以先列出目标表及其索引大小,建立一个简单的基线。这个查询不会告诉你“膨胀比例”,但能帮助发现明显异常的体积变化。

sql
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;

预期结果

text
      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、调整写入方式,还是进入更强的索引整理方案。膨胀治理的第一步不是缩文件,而是证明存在与业务规模不匹配的空间浪费。

按自己的节奏,确认真正理解。

目录与阅读设置

记录一个想法

理解索引膨胀成因

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通