索引维护

实用指南 · 上游译文 #53

适用版本以正文为准，未声明统一最低版本。

来源与验证说明

原题: Index maintenance
来源: Nikolay Samokhvalov / Postgres.ai
中文翻译与校订: xiongcc
验证状态: 上游内容已核对；未逐条运行所有示例。（2026-09-10）

原作者：Nikolay Samokhvalov · 英文原文 · 中文翻译与校订：xiongcc

本页目录

在大型项目中，索引维护是不可避免的。越早建立维护流程，性能表现会越好。

分析索引健康状况

索引健康分析包括：

识别无效索引
膨胀分析
查找未使用的索引
查找冗余索引
检查索引损坏情况

无效索引

查找无效索引非常简单：

nik=# select indexrelid, indexrelid::regclass, indrelid::regclass
from pg_index
where not indisvalid;
 indexrelid | indexrelid | indrelid
------------+------------+----------
      49193 | t_id_idx   | t1
(1 row)

在 Postgres DBA 中可以找到更全面详细的查询。

在分析这个列表时，请记住，如果索引正在通过 CREATE INDEX CONCURRENTLY 或 REINDEX CONCURRENTLY 创建或重建，无效索引可能是正常情况，因此还值得检查 pg_stat_activity 以识别此类进程。

其他无效索引必须重建 (REINDEX CONCURRENTLY) 或删除 (DROP INDEX CONCURRENTLY)。

膨胀索引

如何分析索引膨胀：参照 Day 46: How to deal with bloat

膨胀程度较高的索引 (真实的或预估的) 超过 50%，必须通过 REINDEX CONCURRENTLY 进行重建。

由于随着时间的推移，索引健康状况会因更新而下降，因此需要重新索引。以下方法可以帮助减缓这种下降：

升级到 Postgres 14 或更新的版本 (以利用 btree 优化)。
优化工作负载和架构，以使用更多的 HOT (Heap Only Tuple)。
调整 autovacuum，使其更积极地执行清理 (但请记住，VACUUM 不会重新平衡 btree 索引)。

因此，你需要定期对已知膨胀的索引使用 REINDEX CONCURRENTLY。理想情况下，这个过程应该自动化。例如：pg_auto_reindexer。

未使用的索引

可以在 pg_stat_user_indexes 中找到使用信息。Postgres DBA 中提供了一个用于分析的查询示例。

在查找未使用的索引时，请保持谨慎，避免错误：

确保统计信息已经足够长时间没有重置。例如，如果统计信息几天前才被重置，你认为某个索引未被使用，这可能是一个错误 — 如果需要这个索引来支持下个月 1 号的一些报告。
不要忘记收集承接工作负载的所有节点的统计信息 — 包括主节点和所有副本。在主节点上看似未使用的索引，可能在副本上是必需的。
如果有多个系统安装，请确保分析了所有系统或至少一部分具有代表性的系统。

一旦可靠地识别出未使用的索引，那么应该使用 DROP INDEX CONCURRENTLY 进行删除。

我们能否软删除索引 (将其"隐藏"以确保计划器行为不变，如果没有问题，再执行真正的删除，否则快速恢复至原始状态)？不幸的是，这里没有简单的答案：

HypoPG 1.4.0 有一个"隐藏"索引的功能 — 这非常有用，但需要安装它。更重要的是，在整个工作负载中使用它可能具有挑战性，因为你需要调用 hypopg_hide_index(oid)。
有些人使用将 indisvalid 设置为 false 的技巧来隐藏索引 — 但有可靠的观点认为这不是一个安全的做法；请参见 Peter Geoghegan's Tweet:

基本上，这是不安全的。尽管很难说它破坏的可能性有多大。我知道的一个风险是：通常这种更新可能会破坏并发的 pg_index.indcheckxmin = true 检查。它实际上会"改变受影响行的 xmin"，使检查混淆。