PostgreSQL / 技术文章
PostgreSQL 索引为什么会越用越大
PostgreSQL 索引持续增长,通常不是索引“失效”,而是更新、删除、MVCC、页面复用与物理文件扩展共同作用的结果。本文面向 DBA 与 SRE,从 B-tree 索引的页结构和版本可见性出发,解释为什么 VACUUM 后文件大小仍可能不变,并给出识别增长类型、判断风险和安排维护的分析方法。
索引为什么会越用越大
以常见的 B-tree 为例,索引文件由许多 page 组成。插入新键值时,系统需要找到目标叶子页;如果页面还有可用空间,记录可以落入已有页面,如果空间不足,就可能发生 page split,把一个页面拆成两个,并让树结构增加新的指针关系。
这里有一个容易忽略的区别:索引空间增长不等于“每条更新都会新增一份永久数据”。PostgreSQL 使用 MVCC 保存行版本,而索引项与表中元组版本之间还受到更新路径、索引列是否变化等因素影响。随着写入持续进行,历史版本产生的空间、页面碎片和 page split 的累计效应,最终可能让索引的物理文件逐渐变大。
对于高频写入、随机键更新、叶子页利用率不均匀的场景,增长尤其容易表现为长期缓慢爬升,而不是某一次操作突然翻倍。
UPDATE 为什么会推动索引膨胀
理解 UPDATE 是判断膨胀机制的关键。一次 UPDATE 并不意味着原来的行在原地被“改掉”。对于不能走 HOT 的更新,数据库需要创建新的元组版本,并为受影响的索引生成相应的新索引项;旧版本不会立即消失,而要等到没有事务再需要它之后,才能被清理。
因此,下面两类 UPDATE 的成本可能完全不同:
| 更新方式 | 典型结果 | 对索引空间的影响 |
|---|---|---|
| 只更新未被索引覆盖的列,且满足 HOT 条件 | 尽量在原页面维护版本链 | 通常更有利于减少新增索引项 |
| 更新了索引列 | 需要维护相关索引项 | 更容易产生额外索引空间需求 |
| 高频更新且长期存在旧版本 | 清理压力持续存在 | 可能出现可回收空间与文件增长并存 |
可以用实验数据观察单个表的 UPDATE 量与索引大小变化:
SELECT
relname,
pg_size_pretty(pg_relation_size(oid)) AS table_size
FROM pg_class
WHERE relname = 'orders';
SELECT
indexrelname,
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;预期结果会返回表及其索引的当前物理大小。重点不是某个固定数值,而是连续采样后比较增长斜率,并结合 UPDATE、DELETE 与 VACUUM 情况判断增长来源。
VACUUM 为什么清了空间,文件却没变小
VACUUM 的一个常见误解是“回收空间”就等于“把磁盘文件缩回去”。实际上,两者不是同一件事。
VACUUM 主要负责让已不可见的版本相关空间重新变得可用,并维护索引和表的可复用状态。已经分配给关系文件的 page,通常会留在文件中,之后的新写入可以复用这些页面。因此,执行 VACUUM 后观察 pg_relation_size(),发现数字没有明显下降,并不能直接说明 VACUUM 没有工作。
可以把这个过程理解成两种状态:
写入增长
↓
关系文件获得更多 page
↓
旧版本逐渐变得可清理
↓
VACUUM 让部分 page / 空间重新可用
↓
新写入优先复用已有空间
↓
物理文件大小可能保持不变只有当需要真正缩小关系文件时,才涉及重写、重建或其他能够改变物理布局的维护动作,而且这些动作的并发能力、锁影响与运行成本需要单独评估。
怎么区分正常增长、索引膨胀和表膨胀
“索引越来越大”本身不是足够的信息。DBA 更需要判断:数据集是否真的变大、旧空间是否已经可复用、还是索引结构因为工作负载而长期积累了低利用率页面。
可以先从关系大小、索引大小和扫描统计建立时间序列:
SELECT
c.relname,
pg_size_pretty(pg_relation_size(c.oid)) AS relation_size,
pg_size_pretty(pg_indexes_size(c.oid)) AS all_indexes_size
FROM pg_class AS c
JOIN pg_namespace AS n
ON n.oid = c.relnamespace
WHERE n.nspname = 'public'
AND c.relkind = 'r'
AND c.relname = 'orders';预期结果类似:
relname | relation_size | all_indexes_size
---------+---------------+-----------------
orders | 12 GB | 29 GB这个结果只能说明“索引占用较大”,不能单独证明存在严重膨胀。真正有价值的是把它与时间序列、写入量、VACUUM 进度、查询访问模式以及表中实际数据量放在一起看。
一个常见误区是只看索引占表大小的比例。某些业务本来就需要多个索引,索引总量较大并不自动意味着异常;反过来,小索引也可能因为持续 page split 和删除更新而存在明显的内部浪费。
从监控曲线判断下一步动作
对于 DBA 与 SRE,更实用的判断顺序是先看“增长是否来自业务数据”,再看“空间是否能够被复用”,最后才决定是否需要重建。
可以把长期监控拆成三条曲线:表数据规模、索引物理大小、写入与清理强度。
关系大小
↑
| ┌────── Index size
| ┌───┘
| ┌───┘
| ┌──┘ ┌────── Table data
|──┘──────┬──┘
+--------------------------→ 时间
同时观察:
INSERT / UPDATE / DELETE
VACUUM / autovacuum
查询扫描模式
磁盘剩余空间如果表和索引一起随数据量增长,优先把它看作业务规模增长问题;如果表规模变化不大而某个索引持续单边增长,就值得进一步检查更新模式、索引列变化、page split 与可回收空间;如果文件长期很大但增长已经停止,也要区分“历史占用高但可以复用”和“结构确实需要重建”。
这里的目标不是让 pg_relation_size() 永远下降,而是让磁盘增长、查询性能和维护成本处在可预测范围内。只有当现有空间无法有效复用、磁盘压力或性能代价已经达到维护阈值时,才进入 REINDEX、并发重建或索引设计调整的决策。
设计索引时就给膨胀留出余量
索引治理并不只是出现膨胀后执行一次 REINDEX。更重要的是在设计阶段识别哪些工作负载天然容易制造大量索引写放大。
对于更新频繁的表,可以重点检查:是否存在大量会变化的索引列、是否能形成 HOT 更新、是否创建了很少被访问但持续产生维护成本的索引,以及写入峰值是否与自动维护能力匹配。
一个实用的工程习惯是为每个核心索引记录四类指标:用途、大小、访问频率、写入维护成本。这样在磁盘增长时,DBA 可以先判断是数据增长、索引设计、更新模式还是维护节奏导致,而不是把所有“索引变大”都归因于同一个问题。
当理解了“物理文件变大”和“空间不可复用”之间的区别后,VACUUM、HOT、REINDEX 以及索引设计就能被放在同一条因果链上分析:先解释为什么长大,再判断能否复用,最后决定是否值得重建。