索引膨胀检测

索引不是「建好就不用管」的静态结构。随着增删改不断发生，B-Tree 的页面会逐渐碎片化：已删除条目留下的空洞迟迟不能被新条目填满，整页被删空后也未必立刻归还，最终索引文件远大于它「真正需要」的大小。这种「占用空间远超有效数据」的状态，就叫索引膨胀（Index Bloat）。

膨胀不会让查询报错，它只是安静地拖慢一切：同样的索引用更多的页，扫描产生更多 I/O，缓存命中率下降，备份与重建时长增加。本节讲清楚如何量化膨胀——从官方的 pgstatindex() 到无需扩展的启发式估算——以及如何判断「到什么程度才值得重建」。

前置知识

阅读本节前，建议先了解：REINDEX 重建索引、表膨胀检测与处理、VACUUM 清理

基础概念

膨胀的本质：空间利用率下降

B-Tree 的每个叶子页有 8 KB（BLCKSZ 默认 8 kB），能放多少索引项取决于键的宽度。理想情况下页面填充率接近 fillfactor（默认 90%）。膨胀时，页面填充率持续走低，出现三种可观测现象：

现象	物理表现	指标
页内空洞	页上只有少量存活键，其余是被删项残留的空间	avg_leaf_density（平均叶子密度）低
逻辑碎片	逻辑上相邻的键散落在物理不相邻的页上	leaf_fragmentation（叶子碎片率）高
空页堆积	整页键全被删除，但尚未被回收复用	empty_pages / deleted_pages 偏高

健康的叶子层（密度 ~90%）
[■■■■■■■■■ ] [■■■■■■■■■ ] [■■■■■■■■■ ]

膨胀后的叶子层（密度 ~55%，含空页）
[■■■    ■  ] [■     ■■■ ] [        ] [■■  ■    ]
                          ↑ 空页，等待回收

索引膨胀与表膨胀不同

很多人把索引膨胀当成表膨胀的一部分，其实两者成因与处理方式都不同：

维度	表膨胀（Table Bloat）	索引膨胀（Index Bloat）
根因	MVCC 死元组（dead tuples）堆积	索引项删除后的页面空洞与碎片
谁来清理	VACUUM（可回收死元组空间）	VACUUM 只能清扫，不能整理页内布局
精确检测	pgstattuple() / pgstattuple_approx()	pgstatindex()（B-Tree）
根治手段	VACUUM FULL / pg_repack	REINDEX / REINDEX CONCURRENTLY
观测频率	每次 VACUUM 后	按周期巡检

关键区别：VACUUM 能大幅回收表空间，却几乎无法降低已存在的索引碎片。 索引要想「压实」，只能重建。

膨胀从哪里来

① 频繁删除：大批 DELETE 后，页上留下零散空洞
② 随机更新：非 HOT 更新会在索引中插入新项、标记旧项死亡
③ 随机插入：小值频繁改写，页面分裂后填充率参差
④ fillfactor 过高：默认 90% 几乎没给更新留空间，热点页反复分裂
⑤ 单调递增键 + 大量删除：尾部页面反复写满再删空

其中 ④、⑤ 最容易被忽视：fillfactor = 100 的索引在写密集场景下分裂最快；而自增主键索引在「插入 + 定期清理旧数据」的模式下，会在尾部留下大量半空页。

膨胀的代价

膨胀不是「浪费点磁盘」这么简单，它有明确的性能代价：

-- 对比：同一查询在不同膨胀程度下的缓冲页读取数
EXPLAIN (ANALYZE, BUFFERS) SELECT count(*) FROM events WHERE user_id = 42;

 Index Only Scan using idx_events_user on events
   (actual time=0.031..2.104 rows=30 loops=1)
   Index Cond: (user_id = 42)
   Heap Fetches: 0
   Buffers: shared hit=18          ← 膨胀前

后台重建索引后，同样的查询页读取数可能降到 8～10 页。差异来自三个环节：

环节	膨胀如何放大开销
索引扫描	逻辑相邻的键在物理上分散，随机 I/O 增加
缓存	更多页挤占 shared_buffers，挤掉其它热数据
维护	备份、REINDEX、复制、逻辑解码都要处理更多页

语法与用法

检测依赖 pgstattuple 扩展。先在目标库安装：

CREATE EXTENSION IF NOT EXISTS pgstattuple;

CREATE EXTENSION

最核心的诊断函数是 pgstatindex()，它只接受 B-Tree 索引：

pgstatindex(index regclass) → record
    返回 B-Tree 索引的版本、层级、页统计与密度指标

pgstattuple(table regclass) → record
    返回关系的物理长度、元组数、死元组与空闲空间

pgstattuple_approx(table regclass) → record
    基于可见性映射的近似统计（跳过全可见页，快得多）

pgstatginindex(index regclass) → record
    GIN 索引统计（version / pending_pages / pending_tuples）

pgstathashindex(index regclass) → record
    Hash 索引统计（bucket_pages / overflow_pages / ... / free_percent）

pg_relpages(relation regclass) → bigint
    关系占用的页面数

最小可运行示例：

DROP TABLE IF EXISTS metrics;

CREATE TABLE metrics (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  device_id  bigint      NOT NULL,
  reading    numeric(10,2) NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX idx_metrics_device ON metrics (device_id);

INSERT INTO metrics (device_id, reading)
SELECT (random() * 50000)::bigint, random() * 100
FROM generate_series(1, 500000);

ANALYZE metrics;

-- 一次性查看 B-Tree 的全部统计字段
SELECT * FROM pgstatindex('idx_metrics_device');

 version | tree_level | index_size | root_block_no | internal_pages | leaf_pages | empty_pages | deleted_pages | avg_leaf_density | leaf_fragmentation
---------+------------+------------+---------------+----------------+------------+-------------+---------------+------------------+--------------------
       4 |          2 |   15654912 |             3 |             12 |       1895 |           0 |             0 |            89.83 |               1.02

详细说明

pgstattuple 扩展与权限

pgstattuple 是官方附加模块，不属于核心，必须显式安装。安装后，函数 EXECUTE 权限默认只授予 pg_stat_scan_tables 预定义角色，普通用户调用会报权限不足：

-- 授权给具体角色
GRANT EXECUTE ON FUNCTION pgstatindex(regclass) TO monitoring_role;

-- 或把角色加入预定义角色
GRANT pg_stat_scan_tables TO monitoring_role;

GRANT

两点使用须知：

• 只取读锁。 这些函数对目标关系加 ACCESS SHARE 锁，不会阻塞写入，因此结果是近似快照而非一致性快照——并发写入期间统计值会有抖动。
• 有 I/O 成本。 pgstatindex() 与 pgstattuple() 需要读取整个关系的所有页面，相当于一次全索引扫描。在大索引上频繁调用会明显增加 I/O，务必按周期而非高频调用。

-- 先看体积，决定是否值得为它付出一次扫描
SELECT pg_size_pretty(pg_relation_size('idx_metrics_device')) AS size,
       pg_relation_size('idx_metrics_device') / 8192            AS pages;

  size   | pages
---------+-------
 15 MB   |  1911

pgstatindex：B-Tree 膨胀的权威指标

pgstatindex() 返回的每个字段都有明确含义，理解它们才能正确判断膨胀：

字段	含义	膨胀信号
version	B-Tree 版本号（当前为 4）	—
tree_level	树的层级（根在 0/1，叶子层为最大值）	密度低时层级可能虚高
index_size	索引总字节数（约等于所有页 × 8 kB 加上元页）	与「理论最小大小」的比值
root_block_no	根页所在块号	—
internal_pages	内部（非叶子）页数	通常占比很小
leaf_pages	叶子页数	—
empty_pages	完全为空的页数	偏高说明大量页已删空未回收
deleted_pages	已被标记删除、等待回收的页数	偏高说明回收滞后
avg_leaf_density	叶子页平均填充百分比（理想 90% 左右）	低于 65%～70% 值得关注
leaf_fragmentation	逻辑顺序与物理顺序的偏离程度（0 为完美）	持续偏高说明碎片严重

SELECT (pgstatindex('idx_metrics_device')).avg_leaf_density     AS density,
       (pgstatindex('idx_metrics_device')).leaf_fragmentation   AS frag,
       (pgstatindex('idx_metrics_device')).empty_pages          AS empty,
       (pgstatindex('idx_metrics_device')).deleted_pages        AS deleted;

 density | frag | empty | deleted
---------+------+-------+---------
   89.83 | 1.02 |     0 |       0

读法小结：

• avg_leaf_density 高 + leaf_fragmentation 低 → 健康，不需要动。
• avg_leaf_density 低 → 大量页只装了少量键，索引明显「虚胖」。
• leaf_fragmentation 高 → 顺序扫描该索引时随机 I/O 多，范围查询尤其吃亏。

理论最小页数 ≈ 有效条目数 × 平均条目宽度 / (8 kB × 90%)
若实际 pages / 理论最小页数 明显大于 1.3 → 存在可观膨胀

其它访问方法的膨胀检测

不同索引方法有各自的膨胀形态，检测函数也不同：

-- GIN（如 jsonb / 数组 / 全文索引）：关注待处理条目积压
SELECT * FROM pgstatginindex('idx_docs_body_gin');

 version | pending_pages | pending_tuples
---------+---------------+----------------
       2 |             0 |              0

-- Hash 索引：关注溢出页与空闲比例
SELECT * FROM pgstathashindex('idx_sessions_token');

 version | bucket_pages | overflow_pages | bitmap_pages | unused_pages | live_items | dead_items | free_percent
---------+--------------+----------------+--------------+--------------+------------+------------+--------------
       1 |         4096 |             27 |            1 |           12 |     262144 |          0 |        12.31

overflow_pages 高 → 哈希冲突严重或膨胀
unused_pages   高 → 有整页未被使用，可考虑 REINDEX
free_percent   低 → 页面接近写满，后续更新会触发分裂

对于表本身的膨胀（不是索引），用 pgstattuple() 或更快的 pgstattuple_approx()：

SELECT * FROM pgstattuple('metrics');

 table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent
-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------
  48234496 |      500000 |  30000000 |         62.20 |                0 |              0 |               0.00 |   17895424 |        37.10

-- 基于可见性映射的近似统计：跳过全可见页，快得多；dead 统计精确，live/free 为近似
SELECT scanned_percent,
       approx_tuple_count,
       approx_free_percent
FROM pgstattuple_approx('metrics');

 scanned_percent | approx_tuple_count | approx_free_percent
-----------------+--------------------+---------------------
            4.20 |             500000 |               36.98

pgstattuple_approx 只适用于表

近似统计靠可见性映射跳过页面，索引没有对应的堆页面可见性映射，因此该函数不能用于索引；检测索引请用 pgstatindex() 等专用函数。

全库巡检：先按体积排序

日常巡检的第一步不是逐个跑 pgstatindex()（太贵），而是先按体积与使用率筛出「值得深查」的索引：

SELECT schemaname,
       relname                              AS table_name,
       indexrelname                         AS index_name,
       idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size,
       pg_relation_size(indexrelid) / 8192  AS pages
FROM pg_stat_user_indexes
WHERE pg_relation_size(indexrelid) > 100 * 1024 * 1024    -- 只看 100 MB 以上
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 20;

 schemaname | table_name |     index_name      | idx_scan |  size  | pages
------------+------------+---------------------+----------+--------+-------
 public     | events     | idx_events_user     |  8129341 | 112 MB | 14336
 public     | logs       | idx_logs_created_at |    41233 |  96 MB | 12288

「体积大 + idx_scan 低」是最该处理的一类：既占空间又不被使用。这类索引的第一选择往往不是重建，而是直接删掉。

-- 找出「大而无用」的索引
SELECT indexrelname AS index_name,
       idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

     index_name      | idx_scan |  size
---------------------+----------+--------
 idx_logs_created_at |        0 |  96 MB

不用扩展的启发式估算

生产环境常常不允许安装扩展。此时可以用「行数 × 平均键宽」估算理论大小，与实测大小对比。它不精确，但足以筛选出可疑对象：

SELECT i.indexrelid::regclass AS index_name,
       pg_size_pretty(pg_relation_size(i.indexrelid)) AS actual_size,
       pg_size_pretty((tbl.reltuples * (coalesce(k.avg_key_width, 16) + 8))::bigint) AS naive_estimate
FROM pg_index i
JOIN pg_class     tbl ON tbl.oid = i.indrelid
JOIN pg_namespace n   ON n.oid = tbl.relnamespace
JOIN LATERAL (
  SELECT sum(coalesce(s.avg_width, 16)) AS avg_key_width
  FROM unnest(i.indkey::int2[]) WITH ORDINALITY AS ik(attnum, ord)
  LEFT JOIN pg_attribute a ON a.attrelid = i.indrelid AND a.attnum = ik.attnum
  LEFT JOIN pg_stats s
         ON s.schemaname = n.nspname
        AND s.tablename  = tbl.relname
        AND s.attname    = a.attname
  WHERE ik.ord <= i.indnkeyatts
) k ON true
WHERE i.indrelid = 'events'::regclass
ORDER BY pg_relation_size(i.indexrelid) DESC;

     index_name         | actual_size | naive_estimate
------------------------+-------------+----------------
 idx_events_user        | 112 MB      | 71 MB
 idx_events_created_at  | 88 MB       | 80 MB

这个估算有三个已知偏差，务必记住：

偏差来源	影响
忽略页头、行指针、页内空闲	估算通常偏小
表达式索引（attnum = 0）取不到列统计	用默认 16 字节兜底，误差大
忽略 INCLUDE 列（只算 indnkeyatts 个键列）	覆盖索引的估算会明显偏小

因此：它只用来「怀疑」，不用来「确诊」。 确诊必须用 pgstatindex()。

到什么程度才算膨胀

没有官方统一阈值，但可以用经验区间分层决策：

avg_leaf_density	leaf_fragmentation	建议
≥ 85%	< 10%	健康，无需处理
70% ~ 85%	10% ~ 30%	观察，纳入巡检；若体积占缓存大头可考虑重建
50% ~ 70%	30% ~ 50%	建议在低峰期 REINDEX CONCURRENTLY
< 50%	> 50%	明显膨胀，优先重建；同时排查是否有异常写入模式

判断时还应叠加三个现实因素：

① 体积绝对值：小索引（< 10 MB）膨胀了也无所谓
② 使用频率：idx_scan 很高的索引，收益最大
③ 缓存压力：shared_buffers 装不下的库，缩减体积收益最大

单看百分比容易误判——一个 2 MB 的索引密度只有 40%，也远不如一个 112 MB、密度 70% 的索引值得重建。

fillfactor 与膨胀的关系

fillfactor 决定页面预留多少空间给后续更新。默认 90% 对只读为主的数据够用，对写密集的表则过于激进：

场景	推荐 fillfactor	理由
只读 / 极少更新	100	页面装满，体积最小
常规读写混合	90（默认）	平衡
频繁更新热点页	70 ~ 85	给更新留空间，降低分裂频率
追加为主（自增键）	100	尾部单点写入，无需预留

-- 对写密集的索引降低 fillfactor，然后重建让它生效
ALTER INDEX idx_metrics_device SET (fillfactor = 80);
REINDEX INDEX CONCURRENTLY idx_metrics_device;

ALTER INDEX
REINDEX

fillfactor 只对新页面生效

修改参数不会重排已有页面，必须重建索引才能让新 fillfactor 覆盖全部页面。这也是「检测到膨胀后，先调参数再重建」这一组合拳的由来。

检测频率与监控集成

膨胀是缓慢累积的，不需要高频检测。推荐节奏：

对象	频率	手段
大索引（> 100 MB）	每周一次	pgstatindex() 抽样若干关键索引
全库索引体积趋势	每天	pg_stat_user_indexes + pg_relation_size() 快照
全库精确诊断	每月 / 变更前后	pgstatindex() 全量或按体积 Top N

-- 把结果落到监控表，长期看趋势
CREATE TABLE IF NOT EXISTS index_bloat_history (
  captured_at  timestamptz NOT NULL DEFAULT now(),
  index_name   text        NOT NULL,
  size_bytes   bigint      NOT NULL,
  pages        bigint      NOT NULL
);

INSERT INTO index_bloat_history (index_name, size_bytes, pages)
SELECT indexrelid::regclass::text,
       pg_relation_size(indexrelid),
       pg_relation_size(indexrelid) / 8192
FROM pg_stat_user_indexes
WHERE pg_relation_size(indexrelid) > 10 * 1024 * 1024;

INSERT 0 14

-- 与上周对比，找出增长异常的索引
SELECT index_name,
       max(size_bytes) - min(size_bytes) AS growth_bytes
FROM index_bloat_history
WHERE captured_at > now() - interval '14 days'
GROUP BY index_name
ORDER BY growth_bytes DESC
LIMIT 10;

   index_name    | growth_bytes
-----------------+--------------
 idx_events_user |      8388608

体积趋势比单点数值更有价值：一个密度 75% 但两年来稳定不涨的索引，通常不用管；一个密度 88% 但一周涨 30% 的索引，才是真正需要调查的对象。

检测到膨胀之后怎么办

诊断只是第一步，处置路径取决于「为什么膨胀」：

检测到膨胀
   │
   ├─ idx_scan ≈ 0 且体积大 ──→ 直接 DROP INDEX CONCURRENTLY（最划算）
   │
   ├─ 使用频繁 + 密度低 ───────→ REINDEX CONCURRENTLY
   │
   ├─ 写热点反复膨胀 ─────────→ 调整 fillfactor 后 REINDEX
   │
   ├─ 表本身也在膨胀 ─────────→ 先处理表（VACUUM / 分区），再重建索引
   │
   └─ 单调键 + 定期删旧数据 ──→ 考虑分区表，按分区独立维护

-- 最划算的处置：删掉从未被使用的索引
DROP INDEX CONCURRENTLY idx_logs_created_at;

DROP INDEX

-- 使用频繁但密度低：并发重建
REINDEX INDEX CONCURRENTLY idx_events_user;
ANALYZE events;

REINDEX
ANALYZE

实战示例

下面用 200 万行 metrics 表，从「制造膨胀」到「量化诊断」再到「验证治理效果」，走完一个完整闭环。

步骤一：制造可观测的膨胀

DROP TABLE IF EXISTS metrics;

CREATE TABLE metrics (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  device_id  bigint        NOT NULL,
  reading    numeric(10,2) NOT NULL,
  created_at timestamptz   NOT NULL DEFAULT now()
);

INSERT INTO metrics (device_id, reading)
SELECT (random() * 50000)::bigint, random() * 100
FROM generate_series(1, 2000000);

CREATE INDEX idx_metrics_device ON metrics (device_id);

-- 高频更新热点键：让索引页反复插入/标记删除
UPDATE metrics SET reading = reading + 1 WHERE device_id % 3 = 0;
UPDATE metrics SET reading = reading + 1 WHERE device_id % 3 = 0;
DELETE FROM metrics WHERE device_id % 11 = 0;

VACUUM ANALYZE metrics;

DROP TABLE
CREATE TABLE
INSERT 0 2000000
CREATE INDEX
UPDATE 666667
UPDATE 666667
DELETE 181818
VACUUM

步骤二：量化膨胀

CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT pg_size_pretty(pg_relation_size('idx_metrics_device'))          AS size,
       (pgstatindex('idx_metrics_device')).avg_leaf_density            AS density,
       (pgstatindex('idx_metrics_device')).leaf_fragmentation          AS frag,
       (pgstatindex('idx_metrics_device')).empty_pages                 AS empty_pages,
       (pgstatindex('idx_metrics_device')).deleted_pages               AS deleted_pages,
       (pgstatindex('idx_metrics_device')).leaf_pages                  AS leaf_pages;

  size  | density | frag  | empty_pages | deleted_pages | leaf_pages
--------+---------+-------+-------------+---------------+------------
 68 MB  | 61.42   | 44.18 |           9 |            34 |       8560

解读：

• avg_leaf_density 61% → 大量叶子页没装满，落在「建议重建」区间。
• leaf_fragmentation 44% → 逻辑相邻键物理分散，范围扫描会吃亏。
• deleted_pages 34 → 有 34 页已标记删除但尚未回收，VACUUM 没解决它们。
• 体积 68 MB，对比理论最小约 44 MB，虚胖约 35%。

步骤三：先调 fillfactor 再重建

-- 写热点表，给更新留出空间
ALTER INDEX idx_metrics_device SET (fillfactor = 80);

-- 并发重建，让新 fillfactor 覆盖全部页面
REINDEX INDEX CONCURRENTLY idx_metrics_device;

ANALYZE metrics;

ALTER INDEX
REINDEX
ANALYZE

步骤四：验证治理效果

SELECT pg_size_pretty(pg_relation_size('idx_metrics_device')) AS size,
       (pgstatindex('idx_metrics_device')).avg_leaf_density    AS density,
       (pgstatindex('idx_metrics_device')).leaf_fragmentation  AS frag,
       (pgstatindex('idx_metrics_device')).deleted_pages       AS deleted_pages;

  size  | density | frag | deleted_pages
--------+---------+------+---------------
 48 MB  | 78.10   | 2.40 |             0

体积从 68 MB 降到 48 MB（-29%），碎片率从 44% 降到 2.4%，deleted_pages 归零。注意密度没有回到 90%——这是 fillfactor = 80 带来的有意预留，属于正常现象，不要误判为「还没重建干净」。

-- 最终确认：查询计划里读取的页数变少
EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*) FROM metrics WHERE device_id = 12345;

 Aggregate  (actual time=1.884..1.885 rows=1 loops=1)
   Buffers: shared hit=11
   ->  Index Only Scan using idx_metrics_device on metrics
         Index Cond: (device_id = 12345)
         Heap Fetches: 0

注意事项

• 别用 VACUUM 治索引膨胀。 VACUUM 回收堆表死元组空间，对已形成的索引页内碎片无能为力。

  -- 不推荐：以为 VACUUM 能压实索引
  VACUUM FULL metrics;             -- 表会被压实，但索引仍要靠 REINDEX

• pgstatindex() 只接受 B-Tree 索引。 对 GIN、Hash、GiST 调用会报错。

  -- 错误！非 B-Tree 索引请改用 pgstatginindex() / pgstathashindex()
  SELECT * FROM pgstatindex('idx_docs_body_gin');

• 这些函数会整表/整索引扫描，别在高频监控里跑。 每次调用都是真实的 I/O 成本，大索引上可能秒级甚至更久。

• 结果是近似快照，不是一致性快照。 并发写入期间多跑几次，数值会抖动，判断趋势要看多次而非单次。

• pgstattuple 默认普通用户无权限。 需要显式 GRANT EXECUTE 或授予 pg_stat_scan_tables 角色，否则监控脚本会静默失败。

• pgstattuple_approx 不能用于索引。 它依赖堆页的可见性映射，只对表有效。

• 密度不是唯一指标。 高密度 + 高碎片同样会让范围查询变慢；低密度但从不被扫描的索引则毫无影响。

  -- 结合使用率一起看，避免只盯密度
  SELECT indexrelname, idx_scan,
         pg_size_pretty(pg_relation_size(indexrelid)) AS size
  FROM pg_stat_user_indexes
  WHERE indexrelid = 'idx_metrics_device'::regclass;

• 小索引的膨胀不值得处理。 重建 3 MB 的索引只省几百 KB，收益抵不过操作风险。

• 启发式估算只能用来筛选。 它忽略页开销、表达式与 INCLUDE 列，误差可达数十个百分点，不能作为处置依据。

• 记得统计信息也要刷新。 重建索引后立刻 ANALYZE，否则优化器仍按旧分布选计划。

• fillfactor 修改不重建等于没改。 已有页面不会被重新布局。

最佳实践

• 建立「体积 + 使用率 + 密度」三位一体的巡检。 先按体积筛，再看 idx_scan，最后对可疑对象跑 pgstatindex()。

  SELECT indexrelname AS index_name,
         idx_scan,
         pg_size_pretty(pg_relation_size(indexrelid)) AS size
  FROM pg_stat_user_indexes
  ORDER BY pg_relation_size(indexrelid) DESC
  LIMIT 20;

• 优先清理「大而不用」的索引，而不是重建它们。 删除零扫描索引是收益最高、风险最低的治理动作。

  DROP INDEX CONCURRENTLY idx_logs_created_at;

• 把膨胀阈值写进巡检脚本，自动化报警。 例如密度低于 65% 且体积大于 100 MB 时告警。

  SELECT c.relname AS index_name,
         (pgstatindex(c.oid)).avg_leaf_density AS density
  FROM pg_class c
  JOIN pg_index i ON i.indexrelid = c.oid
  WHERE pg_relation_size(c.oid) > 100 * 1024 * 1024
    AND (pgstatindex(c.oid)).avg_leaf_density < 65;

• 先调 fillfactor 再重建，一次到位。 否则重建完不久又会照原样膨胀。

  ALTER INDEX idx_events_user SET (fillfactor = 85);
  REINDEX INDEX CONCURRENTLY idx_events_user;

• 重建一律走 CONCURRENTLY，并在重建后 ANALYZE。 这是生产环境的标准动作。

  REINDEX INDEX CONCURRENTLY idx_events_user;
  ANALYZE events;

• 记录基线，看趋势而非绝对值。 把体积与密度写入历史表，按周对比，才看得出「异常增长」。

  INSERT INTO index_bloat_history (index_name, size_bytes, pages)
  SELECT indexrelid::regclass::text,
         pg_relation_size(indexrelid),
         pg_relation_size(indexrelid) / 8192
  FROM pg_stat_user_indexes
  WHERE pg_relation_size(indexrelid) > 10 * 1024 * 1024;

• 对写热点表主动预留空间。 与其等它膨胀再重建，不如一开始就把 fillfactor 设到 75～85。

  CREATE INDEX idx_events_user ON events (user_id) WITH (fillfactor = 80);

• 超大表用分区，把膨胀限制在单个分区内。 这样重建只影响一个分区，维护窗口可控。

• 监控部署在只读副本上。 pgstatindex() 有 I/O 成本，放在副本执行可以避免影响主库。

• 把「膨胀检测」与「REINDEX 决策」解耦。 检测负责发现，处置走独立流程（含计划、审批、窗口），避免监控脚本自动触发重操作。

• 维护前确认磁盘余量。 并发重建期间新旧索引并存，需要足够临时空间。

  df -h /var/lib/postgresql/data

下一节

继续学习：索引使用监控

参考链接

• PostgreSQL 官方文档：pgstattuple 扩展
• PostgreSQL 官方文档：例行重建索引
• PostgreSQL 官方文档：REINDEX
• PostgreSQL 官方文档：索引简介
• PostgreSQL 官方文档：CREATE INDEX（fillfactor 等存储参数）
• PostgreSQL 官方文档：监控统计信息