PostgreSQL 的索引体积膨胀通常不是突然发生的，而是随着更新、删除和并发写入逐步累积。索引页在 B-tree 分裂、页面回收不及时的情况下会留下大量空洞，同时一个表上可能长期存在多个功能重叠的索引。如果只看表大小而忽略索引大小，磁盘容量很容易被隐形成本吃掉。要解决这个问题，需要先建立一套可重复执行的监控查询，再根据扫描频次和膨胀率决定删除或重建。
一、先定位体积大且使用率低的索引
PostgreSQL 提供了 pg_stat_user_indexes 视图，记录每个用户表索引的扫描次数 idx_scan、通过索引返回的元组数 idx_tup_read 和实际获取有效元组数 idx_tup_fetch。将它与 pg_relation_size 结合，就能按占用空间排序，找出最占磁盘的索引。
下面的查询列出当前数据库中所有用户索引的大小、扫描次数和每次扫描平均读取元组数：
SELECT
schemaname,
relname AS table_name,
indexrelname AS index_name,
pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size,
idx_scan,
idx_tup_read,
idx_tup_fetch
FROM pg_stat_user_indexes i
JOIN pg_class c ON c.oid = i.indexrelid
ORDER BY pg_relation_size(i.indexrelid) DESC
LIMIT 30;
这里不能仅凭 idx_scan 的绝对值判断，因为有些索引服务于报表类低频查询，扫描少但每次扫描都能大幅降低 IO。更合理的指标是体积与扫描次数的比值，以及 idx_tup_fetch 是否长期接近 0。比如一个几百 MB 的索引，一个月只被扫描一次，并且读取元组数非常少，基本可以进入删除候选名单。相反，一个几十 MB 的索引即使扫描次数不高，只要每次都能精准命中少量行，仍然值得保留。
需要注意，pg_stat_user_indexes 的统计信息会随着数据库重启、统计信息重置或版本升级而清零，所以不能只看短期数据。建议连续采集一周以上，最好记录到监控表里，再计算日均扫描次数。对于非常新的数据库，统计值低不代表索引无用，要结合业务查询计划一起判断。
二、用 pgstattuple 量化索引膨胀
找到大索引后，下一步是判断它到底是合理增长还是内部碎片膨胀。pgstattuple 扩展可以返回索引的物理统计信息，包括叶子页数、空闲空间、平均叶子密度等。先启用扩展：
CREATE EXTENSION IF NOT EXISTS pgstattuple;
然后对具体索引执行：
SELECT *
FROM pgstatindex('idx_orders_created_at');
输出中 avg_leaf_density 表示叶子页的平均填充率。对于 B-tree 索引，如果 avg_leaf_density 明显低于 50%，通常说明索引页存在较多空洞，重建可以显著缩小体积。leaf_fragmentation 表示叶子页的碎片率，数值越高说明页面逻辑顺序与物理顺序越不一致，范围扫描时更容易产生随机 IO。
不过 pgstattuple 需要扫描整个索引，对大索引执行可能耗时较长并占用 IO，建议在业务低峰期运行。对于 9.5 及以上版本，可以使用 pgstattuple_approx 快速估算，它返回近似膨胀率而不需要全量扫描，适合在监控系统中定期调用。若估算结果显示 dead_tuple_ratio 较高或 free_percent 偏大，再安排一次精确检查。
三、安全执行删除与重建
如果确认一个索引确实冗余，比如多个索引共享相同的前缀列，或者某个索引的列顺序与实际查询完全不匹配，那么可以直接删除。直接执行 DROP INDEX 会获取排他锁，业务写入会被阻塞；PostgreSQL 提供了 CONCURRENTLY 模式，在删除期间允许读写继续：
DROP INDEX CONCURRENTLY IF EXISTS idx_orders_status_created;
删除后需要观察应用查询计划是否变化。对于核心业务表，建议先在测试环境删除并跑一遍回归查询，确认没有隐藏的依赖。注意 DROP INDEX CONCURRENTLY 不能用在事务块中，也不能删除约束索引，比如主键或唯一约束背后的索引。这类索引需要先删除约束或使用 ALTER TABLE 配合 DROP CONSTRAINT 处理。
对于必须保留但膨胀严重的索引，重建比删除更合适。REINDEX INDEX CONCURRENTLY 会在不阻塞读写的情况下创建新索引并切换，旧索引在切换后自动删除。例如：
REINDEX INDEX CONCURRENTLY idx_orders_created_at;
重建会重新整理 B-tree 页面，显著降低索引占用的磁盘空间，同时更新统计信息。若整张表的索引普遍膨胀，可以按表重建所有索引，例如 REINDEX TABLE CONCURRENTLY orders; 但 CONCURRENTLY 重建要求表有主键或唯一索引，否则会失败。不要在事务中执行 CONCURRENTLY 重建。需要预留大约原索引大小的额外磁盘空间，因为新旧索引会短暂同时存在。
对于超大索引，建议一次只处理一个，并在业务低峰期执行。重建完成后立即观察磁盘水位和查询延迟，如果空间没有明显释放，说明膨胀并非来自索引内部碎片，而可能是索引本身设计过大，需要继续评估部分索引或删除方案。
四、通过部分索引和表达式索引从源头减小体积
很多大索引是因为给全表数据都建了索引，而实际查询只关心其中一小部分。例如订单表有 95% 的行状态为 finished，但应用只需要频繁查询 processing 和 pending 状态的订单。此时把全表索引改造成部分索引，只索引状态为 processing 和 pending 的行，体积可能缩小几十倍：
CREATE INDEX idx_orders_active_status
ON orders (created_at)
WHERE status IN ('processing', 'pending');
部分索引的维护成本更低，因为插入或更新 finished 状态的订单时不会修改这个索引。但它要求查询条件必须包含相同的 WHERE 谓词，或者优化器能够证明查询只涉及该部分数据，否则不会命中部分索引。改造前需要逐个确认所有相关 SQL 的条件是否匹配。
表达式索引用于查询条件中包含函数或表达式的情况。比如经常执行 WHERE lower(email) = lower($1) 时，如果只在 email 上建普通索引，函数包裹列会导致索引失效。可以创建表达式索引：
CREATE INDEX idx_users_lower_email ON users (lower(email));
如果某些查询只需要索引就能返回结果，不需要回表，可以使用 INCLUDE 包含额外列，形成覆盖索引。它不会增加索引键的长度，只把额外的列放在叶子节点中，能减少大量随机 IO，但也会占更多空间，所以要权衡是否真的能替代一个单独的窄索引。
五、建立持续监控与告警
单次清理只能解决当前问题，索引体积膨胀会随着业务运行再次出现。建议在监控库或当前库中定期执行下面的采集语句，把索引大小和扫描次数存入监控表：
CREATE TABLE index_metrics (
collected_at timestamptz DEFAULT now(),
schemaname text,
table_name text,
index_name text,
index_size_bytes bigint,
idx_scan bigint,
idx_tup_fetch bigint
);
INSERT INTO index_metrics
(schemaname, table_name, index_name, index_size_bytes, idx_scan, idx_tup_fetch)
SELECT
i.schemaname,
i.relname,
i.indexrelname,
pg_relation_size(i.indexrelid),
i.idx_scan,
i.idx_tup_fetch
FROM pg_stat_user_indexes i;
通过对比连续多天的数据，可以计算每个索引的日均增长量。对于体积超过 1 GB 且日均扫描次数低于 10 的索引，建议触发告警；对于体积超过 5 GB 且日均增长超过 100 MB 的索引，也应该检查写入模式和冗余情况。阈值可以根据实例磁盘容量灵活调整，关键是形成基线，避免凭感觉判断。
如果使用 Prometheus 或 Zabbix 等监控系统，可以把这个查询封装成指标采集脚本，按小时抓取并绘制趋势图。也可以在 PostgreSQL 内部用定时任务或 cron 调用 psql 执行采集，当表 index_metrics 中出现异常增长时通过邮件或告警通道通知。不要等到磁盘使用率达到 90% 再去查，那是应急而不是监控。
PostgreSQL索引监控索引瘦身pg_stat_user_indexes修改时间：2026-09-28 12:52:00