PostgreSQL / 系统课时
判断膨胀风险与趋势
判断膨胀风险与趋势
索引膨胀是否值得治理,不能只看某一次空间占用。需要把更新删除强度、索引大小、访问模式和时间趋势放在一起判断,区分暂时变大、持续增长与已经产生明显维护收益的对象。
学习目标
- 能用表级与索引级统计判断膨胀风险来源。
- 能区分一次性大索引、持续增长索引和真正需要治理的候选对象。
- 能通过周期快照判断趋势,而不是依赖单次观察。
先判断是否具备持续膨胀条件
最重要的信号是业务是否持续修改已有行。插入主要带来新增索引项,而更新和删除更容易产生旧版本、失效空间与后续维护压力;但更新并不必然导致索引膨胀,因为满足条件的 HOT 更新可以避免部分索引修改。
因此,看到 n_tup_upd 或 n_tup_del 较高时,只能先判定“存在较强写入活动”,不能直接判定“索引一定严重膨胀”。还要结合索引实际大小、访问频率以及连续多个时间点的变化。
用一次快照建立风险基线
可以先把表的更新删除量、死元组量与各索引大小放到同一份结果中。下面的查询只依赖内置统计视图,适合作为每日或每小时快照的起点。
SELECT
s.schemaname,
s.relname AS table_name,
s.n_tup_upd,
s.n_tup_del,
s.n_dead_tup,
s.indexrelname AS index_name,
pg_size_pretty(pg_relation_size(s.indexrelid)) AS index_size,
s.idx_scan,
s.idx_tup_read,
s.idx_tup_fetch
FROM pg_stat_user_indexes s
WHERE s.schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_relation_size(s.indexrelid) DESC
LIMIT 20;预期结果
schemaname | table_name | n_tup_upd | n_tup_del | n_dead_tup | index_name | index_size | idx_scan | idx_tup_read | idx_tup_fetch
------------+------------+-----------+-----------+------------+------------+------------+----------+--------------+--------------
app | orders | 82000 | 3000 | 4200 | ... | 180 MB | 125000 | ... | ...同一张表会对应多个索引,因此这些统计值会重复显示。idx_scan 是累计索引扫描次数,不是百分比形式的“使用率”;它必须结合采样周期和业务工作负载解释。
用趋势而不是单点判断
真正有价值的是保存同一查询的周期快照,例如记录时间、索引大小、idx_scan、表更新删除计数和 n_dead_tup。随后计算相邻快照的增量。
假设某索引从 2 小时内由 180 MB 增长到 260 MB,同时对应表持续发生大量 UPDATE,且增长没有随着业务低峰明显停止,那么它比“现在有一个 260 MB 的索引”更值得治理。
还要注意统计计数器可能因为统计信息重置、数据库重启或运维操作而发生突变。因此发现累计值突然下降时,应先确认统计生命周期,再解释业务趋势,不能把计数器归零误判成写入量骤降。
把风险分成三个层级
可以按下面的判断思路筛选治理候选,而不是给所有大索引统一做重建。
| 观察结果 | 更合理的判断 |
|---|---|
| 索引很大,但长期稳定且访问频繁 | 优先视为正常容量,暂不因“大”直接治理 |
| 索引持续增长,同时表持续 UPDATE/DELETE | 进入重点观察,结合更多膨胀指标确认 |
| 索引持续增长、访问较少且维护成本高 | 治理价值通常更高 |
| 写入很高,但索引大小长期稳定 | 可能存在良好的空间复用或 HOT 等因素,不宜仅凭写入量判断膨胀 |
其中“访问较少”也不等于“可以删除”。报表、月末任务和故障恢复路径可能是低频但关键的使用场景,因此最终治理动作必须结合业务依赖和查询计划验证。
用采样结果决定是否进入治理
如果只有一次空间快照,最多得到“当前对象有多大”;如果连续保存多个时间点,就能判断“是否持续变大”。当持续增长与高更新删除活动、较低利用率或明显维护成本同时出现时,才更适合进入膨胀确认与治理阶段。
实际治理前还应使用更专门的膨胀测量方法确认内部空洞或可回收空间,因为系统目录和统计视图适合做风险筛选,并不能单凭它们精确计算索引膨胀率。