PostgreSQL / 系统课时
建立索引健康监控
建立索引健康监控
本课解决一个实际问题:索引很多、写入持续增长时,如何判断哪些索引值得关注。最终形成一套可长期采集的基线,用大小、扫描次数、写入压力和维护状态共同判断索引健康度,而不是只盯着膨胀百分比。
学习目标
- 能从系统视图采集索引大小、扫描次数和表写入压力
- 能区分“很大但常用”和“很大且长期不用”等不同风险
- 能建立用于告警、治理和复盘的索引观察基线
监控指标怎么组合
单看索引大小很容易误判。一个业务核心索引即使很大,只要持续被查询使用,就可能是正常的容量结果;反过来,一个不大的索引如果几乎没有扫描、却持续承受写入,也可能是冗余索引候选。
建议至少建立四类指标:容量、访问、写入、维护。容量回答“占了多少空间”,访问回答“是否真的在服务查询”,写入回答“维护它要付出多少代价”,维护则回答“最近有没有做过治理以及治理后的状态”。pg_stat_all_indexes 提供单个索引的 idx_scan、idx_tup_read、idx_tup_fetch 等访问统计;统计值应结合业务时间窗口解读,而不是把某个绝对值直接当成阈值。:contentReference[oaicite:0]{index=0}
建立一份索引基线
先把索引元数据、磁盘大小和访问统计放到同一份查询里。示例只统计普通业务表索引,便于后续导出到监控系统。
SELECT
s.schemaname,
s.relname AS table_name,
s.indexrelname AS index_name,
pg_size_pretty(pg_relation_size(s.indexrelid)) AS index_size,
pg_relation_size(s.indexrelid) AS index_bytes,
s.idx_scan,
s.idx_tup_read,
s.idx_tup_fetch
FROM pg_stat_user_indexes AS s
ORDER BY pg_relation_size(s.indexrelid) DESC
LIMIT 20;预期结果
schemaname | table_name | index_name | index_size | index_bytes | idx_scan | idx_tup_read | idx_tup_fetch
------------+------------+-------------------+------------+-------------+----------+--------------+--------------
public | orders | orders_customer_i | 128 MB | 134217728 | 1842301 | 6210044 | 1759812
...这里真正值得监控的是趋势。例如每天记录 index_bytes 和 idx_scan,再观察表的写入量;这样才能识别“尺寸增长快、使用却不增长”的异常组合。idx_tup_read 和 idx_tup_fetch 也不能简单理解成相等计数,因为位图扫描、索引仅扫描以及死元组都会影响两者关系。:contentReference[oaicite:1]{index=1}
把写入压力纳入判断
索引治理不能脱离表的写入行为。INSERT、UPDATE、DELETE 越频繁,相关索引维护成本通常越高,因此建议把 pg_stat_all_tables 中的修改计数与索引大小、扫描次数放在同一时间窗口分析。
可以把索引分成几个观察状态:大且高频使用,通常优先做容量和查询性能评估;大且低频使用,优先检查是否重复或冗余;小但高写入关联,重点关注维护成本;长期未扫描且业务确认不用,才进入删除或合并候选。不要用“idx_scan = 0 就一定应该删除”这种单一规则直接执行变更。
监控维护状态与告警门槛
监控系统除了保存当前值,还应记录采样时间和治理动作,例如最近一次 REINDEX、最近一次 VACUUM/ANALYZE 以及治理前后的索引大小。这样故障或性能回退发生时,可以把指标变化与维护动作对应起来。
告警建议采用趋势和组合条件,而不是单指标报警。例如“索引大小持续增长 + 扫描增长停滞 + 表写入持续升高”比“索引超过某个固定 GB”更值得人工检查。阈值应由业务基线决定,因为不同表的容量和访问模式差异很大。
最终的健康监控不是给每个索引贴一个永远固定的健康分数,而是持续保存大小、使用、写入、维护四条证据。治理时优先处理证据同时指向高成本或高风险的索引,才能避免为了“看起来干净”而频繁重建索引。