PostgreSQL 索引膨胀复盘：为什么表很小，索引却很大

🌐 Language: English Version | 中文版

一次生产库容量排查中，我们遇到一个很典型的 PostgreSQL 索引膨胀问题：几张业务表的数据量并不大，有的表甚至已经没有有效数据，但索引文件仍然占用了大量磁盘空间。普通 VACUUM 已经运行过，n_dead_tup 看起来也不高，但库文件大小没有明显下降。

这类问题很容易被误判为“数据库该清理了”，但真正的关键不在清理命令本身，而在判断问题到底发生在 heap 还是 index。本文重点整理判断过程：为什么会发生、为什么 VACUUM 没解决、短期怎么止血，以及长期应该改哪些架构和流程。

TL;DR

• PostgreSQL 的普通 VACUUM 可以清理 heap 中的 dead tuple，让空间可复用，但不会自动把已经膨胀的 B-tree 索引文件缩小。
• 表数据很小、索引很大时，要优先区分是 heap 膨胀还是 index bloat。只看 n_dead_tup 容易误判。
• 高频删除、重建、批量更新的“运行态派生表”，如果长期承载在多索引主业务表上，会持续制造索引写入放大。
• 短期治理通常是删除确认无用的索引，再用 REINDEX CONCURRENTLY 在线重建膨胀索引。
• 长期治理必须回到写入模型：用 staging + diff 替代整片 DELETE + bulk_create，并建立索引生命周期评审和监控。

1. 现象：表数据很小，索引却很大

排查的入口通常是库容量异常。第一步不是直接执行清理脚本，而是拆分表数据和索引空间。

SELECT
    c.relname AS table_name,
    pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size,
    pg_size_pretty(pg_relation_size(c.oid)) AS table_data,
    pg_size_pretty(pg_total_relation_size(c.oid) - pg_relation_size(c.oid)) AS indexes,
    c.reltuples::bigint AS approx_rows
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
  AND n.nspname = 'public'
ORDER BY pg_total_relation_size(c.oid) DESC;

问题大致长这样：

表类型	总大小	表数据	索引	估算行数
周期派生类表	50+ GB	几 MB	50+ GB	几万
规则明细类表	数 GB	几百 KB	数 GB	几千
执行记录类表	数百 MB	0 bytes	数百 MB	0
扩展属性类表	数百 MB	几百 KB	数百 MB	几千

这个形态已经说明问题不在“业务数据本身很多”，而在历史写入行为把索引文件撑大了。

继续看 autovacuum 统计：

SELECT
    relname,
    n_live_tup,
    n_dead_tup,
    last_autovacuum,
    last_autoanalyze,
    autovacuum_count,
    autoanalyze_count,
    vacuum_count
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

这里容易出现一个误判：有些表的 n_dead_tup 已经是 0，于是认为膨胀已经被 autovacuum 处理掉了。但空间仍然没有下降，说明问题主要不在 heap，而在索引。

再看索引大小和使用情况：

SELECT
    relname AS table_name,
    indexrelname AS index_name,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 30;

常见现象是：

• 单个 B-tree 索引膨胀到十几 GB；
• 某些索引 idx_scan = 0，但仍占用大量空间；
• 一些表已经空了，索引文件还在；
• 主键索引、时间索引、外键默认索引都可能被历史 churn 放大。

这时再继续跑普通 VACUUM (ANALYZE)，通常不会得到预期效果。

2. 为什么普通 VACUUM 没有缩小索引

PostgreSQL 使用 MVCC。一次 UPDATE 不是原地覆盖，而是写入新行版本，再把旧行版本标记为不可见；DELETE 也不是立即把物理空间归还给操作系统，而是先产生可回收空间。

普通 VACUUM 的核心作用是清理 heap 里的 dead tuple，让这些空间在同一个表文件里复用。它也会清理索引中指向 dead tuple 的条目，但它不会把已经扩张的 B-tree 文件自动缩回很小。

所以会出现这种看起来矛盾的状态：

• n_dead_tup = 0；
• 表数据只有几 MB；
• 索引仍然几十 GB；
• 继续 VACUUM，磁盘空间基本不变。

这不是 VACUUM 没执行，而是它解决的问题和我们当前遇到的问题不是同一个。

如果目标是缩小已经膨胀的索引文件，通常需要：

• REINDEX INDEX CONCURRENTLY index_name；
• REINDEX TABLE CONCURRENTLY table_name；
• 或用 pg_repack 一类工具重建对象。

VACUUM FULL 也能重写表并回收空间，但它锁表重，在线业务通常不作为首选。PostgreSQL 12 之后支持 REINDEX CONCURRENTLY，对在线治理会友好很多，但仍然会消耗 IO，并且新旧索引会短时间共存，需要提前评估磁盘余量。

3. 根因一：高频变化数据被当成长生命周期主表

这类问题的根本原因通常不是一个数据库参数，而是业务写入模型和存储模型错配。

以报表结果、规则命中明细、任务执行窗口、临时计算结果这类派生数据为例，它们的业务特征是：

• 上游数据会定期刷新；
• 配置后台会批量调整；
• 历史周期会过期；
• 某些计算规则变化后，未来一段时间的数据需要重算。

这些数据天然是高 churn 的运行态派生数据。但在很多系统里，它们被设计成长期主业务表，并叠加大量索引，最后写入模式变成：

删除某个对象未来一段时间的派生记录
批量创建新的周期记录
批量创建明细记录
批量创建关联状态记录

或者：

计算规则变化
清空旧的派生结构
按新配置重新生成

在小数据量阶段，这种做法简单直接，也容易保证业务正确。但在 PostgreSQL MVCC 下，长期运行后会持续制造 dead tuple 和索引垃圾。更麻烦的是，每多一棵索引，删除、插入和更新都要多维护一次 B-tree。业务层以为自己只是在重建一片派生结果，数据库层实际承受的是多倍写入放大。

这也是为什么单纯调高 autovacuum 不够。autovacuum 可以缓解堆积，但它不能消除“把高 churn 派生数据写成多索引长期主表”的根因。

4. 根因二：ORM 默认索引和业务索引叠加

另一个常见来源是索引不是统一设计出来的，而是逐步叠加出来的。

例如：

• 外键字段被 ORM 默认建了单列索引；
• 业务查询又加了复合索引；
• 排序字段加了 db_index=True；
• 唯一约束自带索引；
• 为了某个历史接口加过 LIKE 查询索引；
• 后来查询逻辑变了，但索引没有下线。

最终一张高写入表可能同时存在：

(entity_id)
(rule_id)
(effective_date)
(entity_id, rule_id)
(entity_id, effective_date)
(entity_id, status, effective_date)
PRIMARY KEY (id)
UNIQUE (entity_id, attribute_id, value_id)

其中有些索引确实在服务高频查询，有些只是历史遗留，有些已经被复合索引前缀覆盖。

判断冗余索引时，不能只看 idx_scan = 0 就马上删除。至少要同时检查：

-- 统计信息是否刚被重置
SELECT datname, stats_reset
FROM pg_stat_database
WHERE datname = current_database();

还要确认：

• 是否是唯一约束或主键约束背后的索引；
• 是否被外键维护依赖；
• 是否有低频但关键的后台任务使用；
• 是否被 ORM 某些隐式查询路径使用；
• 是否能被已有复合索引覆盖；
• 删除后关键 SQL 的 EXPLAIN 是否劣化。

更稳妥的做法是把候选索引观察一段时间，结合慢查询、接口访问路径、EXPLAIN 和业务任务周期判断。确认无用后，再用 DROP INDEX CONCURRENTLY 分批删除。

5. 短期止血：删除无用索引和在线重建

短期治理可以分成三步。

第一步，记录执行前状态。

SELECT
    relname,
    pg_size_pretty(pg_total_relation_size(oid)) AS total_before
FROM pg_class
WHERE relkind = 'r'
ORDER BY pg_total_relation_size(oid) DESC
LIMIT 20;

同时检查长事务和无效索引：

SELECT
    pid,
    usename,
    state,
    xact_start,
    now() - xact_start AS xact_age,
    query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start;

SELECT
    c.relname AS index_name,
    i.indisvalid,
    i.indisready
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
WHERE NOT i.indisvalid OR NOT i.indisready;

第二步，删除确认无用的索引。

DROP INDEX CONCURRENTLY IF EXISTS idx_unused_column;

CONCURRENTLY 可以降低对线上读写的影响，但仍然会消耗资源。每次只处理少量索引，执行后观察业务指标和数据库 IO。

第三步，在线重建膨胀索引。

REINDEX INDEX CONCURRENTLY idx_large_active_index;

如果一张表多个索引都膨胀，也可以评估：

REINDEX TABLE CONCURRENTLY high_churn_table;

需要注意几点：

• REINDEX CONCURRENTLY 不能放在事务块里执行；
• 失败时可能留下 _ccnew 之类无效索引，需要清理后重试；
• 重建期间新旧索引共存，要预留额外磁盘空间；
• 重建会带来 IO 压力，应该放在低峰窗口分批执行；
• 重建后要重新对比对象大小和关键查询计划。

治理后验证仍然使用同一组 SQL，不要换口径：

SELECT
    c.relname AS table_name,
    pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size,
    pg_size_pretty(pg_relation_size(c.oid)) AS table_data,
    pg_size_pretty(pg_total_relation_size(c.oid) - pg_relation_size(c.oid)) AS indexes
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
  AND n.nspname = 'public'
ORDER BY pg_total_relation_size(c.oid) DESC;

再用 EXPLAIN ANALYZE 抽样验证核心查询，确保删除索引没有换来明显的查询退化。

6. 中期治理：把索引纳入生命周期

索引治理不应该只在磁盘告警后发生。比较有效的中期措施包括四类。

第一，监控索引大小和使用率。

要长期记录：

• 单索引大小；
• 表数据大小与索引大小比例；
• idx_scan 增长情况；
• idx_tup_read / idx_tup_fetch；
• pg_stat_database.stats_reset 时间。

如果统计被重置，idx_scan = 0 只能说明“重置后未使用”，不能说明“长期未使用”。

第二，对高 churn 表单独调 autovacuum。

全局默认值通常偏保守。对频繁更新的表，可以使用表级 reloptions：

ALTER TABLE high_churn_table SET (
    autovacuum_vacuum_scale_factor = 0.05,
    autovacuum_vacuum_threshold = 1000,
    autovacuum_analyze_scale_factor = 0.02,
    autovacuum_vacuum_cost_limit = 1000
);

如果更新的列不在索引中，还可以评估 fillfactor，给 HOT update 留空间：

ALTER TABLE high_churn_table SET (
    fillfactor = 90
);

第三，建立低峰 REINDEX 流程。

对明确高 churn 且历史上膨胀过的索引，可以建立定期巡检和维护流程。不是所有索引都需要固定重建，但应该有一套判断标准：

• 膨胀比例超过阈值；
• 单索引超过容量阈值；
• 写入量和删除量持续高；
• 最近业务改造前需要先止血。

第四，清理临时备份表。

很多库里都会残留 _backup、_bak、_copy、日期后缀表。它们不一定导致索引膨胀，但会长期占用容量，也会干扰排查。临时备份表进入主库后，应该有保留期、负责人和清理机制。

7. 长期治理：减少写入放大

数据库层的 REINDEX 只能修复已经膨胀的文件，不能阻止问题复发。长期治理要回到写入模型。

对于周期性派生数据，更合理的模式是 staging + diff。

上游原始数据
        |
        v
staging 表或内存结构
        |
        v
与线上有效数据按业务键 diff
        |
        +-- 新增：insert
        +-- 变化：update
        +-- 消失：soft disable 或小批量清理
        +-- 不变：不写

关键点是：不要每次都删除未来一整片派生记录再重建。只有真正变化的记录才写入数据库。

配置后台也一样。保存配置时应该区分：

• 普通状态、阈值、优先级调整：按主键或业务唯一键增量更新；
• 不兼容结构变化：才允许清空并重建；
• 历史过期数据：走归档或分区清理，不在主表上无限累积。

如果数据天然按时间生命周期变化，可以评估分区：

• 当前和未来周期保留在活跃分区；
• 历史分区只读或归档；
• 超过保留期直接 DROP PARTITION；
• 避免在大主表上做持续大批量 DELETE。

最后，索引设计要进入开发评审流程。新增一个索引至少说明：

• 服务哪个接口或 SQL；
• 典型 WHERE、JOIN、ORDER BY 条件是什么；
• 是否被已有复合索引前缀覆盖；
• 是否位于高 churn 表；
• 上线后如何用 pg_stat_user_indexes 和 EXPLAIN 复核；
• 如果未来不用了，谁负责下线。

索引不是免费的。它用空间换读取性能，也会增加写入成本。对读多写少的主数据表，索引多一些通常可以接受；对高 churn 派生表，多一个索引就是持续的写入放大器。

8. 复盘

这次问题表面上是 PostgreSQL 索引膨胀，深层是业务写入模型、ORM 默认行为和运维流程共同作用的结果。

短期看，处理路径是：

定位大对象
区分 heap 和 index
确认普通 VACUUM 的边界
删除无用索引
REINDEX CONCURRENTLY 重建膨胀索引
验证容量和查询计划

长期看，真正需要改变的是：

• 不把高频重建的派生数据简单当成长生命周期主表；
• 不让 ORM 默认索引和业务索引无审查地叠加；
• 不把 idx_scan = 0 当成唯一判断依据；
• 不把数据库维护留到磁盘告警之后；
• 不用 delete + bulk_create 处理本可以 diff 的数据。

当一张几 MB 的表背着几十 GB 的索引时，问题已经不是“清理一下数据库”这么简单。它提醒我们：数据模型、写入方式、索引设计和运维流程必须一起设计，否则数据库迟早会把这些历史成本以容量、IO 和维护窗口的形式还回来。