PostgreSQL 表膨胀和索引膨胀是什么？如何治理？

简化版

PostgreSQL 更新和删除会留下旧行版本，Vacuum 负责回收可复用空间；如果回收不及时或空间不能有效复用，就会出现表膨胀和索引膨胀。治理要先找长事务和 autovacuum 问题，再考虑调 autovacuum、降低 fillfactor、重建索引、VACUUM FULL 或在线重整。

详细版

表膨胀会让扫描更多页面，索引膨胀会让索引层级和缓存占用变大。常见原因：

长事务阻止旧版本回收。
大量 update/delete。
autovacuum 参数过保守。
HOT 比例低，索引频繁更新。
批量删除后没有合适重整。

排查可看表大小、死元组、autovacuum 时间、索引大小。治理时不要一上来 VACUUM FULL，它会重写表并需要较强锁，生产要谨慎。

完整版教学

一、膨胀来自 MVCC 的旧版本

PostgreSQL 更新和删除不会立刻物理移除旧行。旧版本要等没有事务需要它时，才能被 Vacuum 标记为可复用。

如果旧版本长期不能回收，表文件就会变大。即使后续 Vacuum 标记了可复用空间，文件大小也不一定立刻变小，只是内部空间可再利用。

update/delete -> old tuple 变 dead
vacuum -> 标记空间可复用
文件物理缩小 -> 需要重写或特殊操作

记忆钩子：Vacuum 通常回收“可复用空间”，不等于马上把磁盘文件变小。

二、表膨胀会拖慢扫描和缓存

表膨胀后，同样 100 万条有效数据可能分布在更多页面中。顺序扫描要读更多页，索引回表也可能访问更多 heap page，缓存命中率下降。

假设有效数据 10GB，但表总大小 30GB，就意味着有大量空间被旧版本或碎片占着。查询不是只看行数，也看需要读多少页面。

select pg_size_pretty(pg_total_relation_size('orders'));

这个函数能看到表、索引和 TOAST 的总大小，是排查空间问题的入口。

三、索引也会膨胀

更新索引列、删除大量数据、随机插入都可能让索引出现膨胀。索引膨胀后，索引页更多，缓存占用更大，扫描成本也更高。

普通 Vacuum 可以清理索引中的死亡条目，但不一定让索引物理变紧凑。严重时需要 REINDEX。

问题	影响	常见处理
表膨胀	扫描页变多	Vacuum、重写表
索引膨胀	索引缓存和扫描变重	REINDEX
TOAST 膨胀	大字段占用异常	Vacuum、拆分大字段

四、长事务是膨胀治理的头号敌人

Vacuum 是否能回收旧版本，取决于是否还有老快照可能看到它们。长事务会让旧版本不能被清理。

select pid, state, xact_start, query
from pg_stat_activity
where xact_start is not null
order by xact_start;

如果一个事务开了几个小时，期间大量表更新，旧版本就会堆积。治理膨胀前先找长事务，否则调 Vacuum 参数也效果有限。

五、autovacuum 参数要按表特征调整

autovacuum 默认参数不一定适合所有表。高频更新表可能需要更积极的 Vacuum，避免死元组积累。

可以按表设置参数：

alter table orders set (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_analyze_scale_factor = 0.01
);

如果一张表有 1 亿行，默认比例触发可能太晚。降低 scale factor 能让 autovacuum 更早介入，但也会增加后台维护负载。

六、VACUUM FULL 和 REINDEX 要谨慎

VACUUM FULL 会重写表，能释放磁盘空间，但锁较重，生产大表上风险高。REINDEX 用于重建索引，新版本 PostgreSQL 支持一些并发方式，但仍要评估影响。

reindex index concurrently idx_orders_created_at;

治理顺序通常是：先解决长事务和 autovacuum，再观察增长趋势；只有严重膨胀且影响性能或磁盘时，才安排重建或重写。

七、常见误区与追问

误区：delete 后磁盘空间应该马上下降。 普通 Vacuum 主要让空间可复用，不一定缩小物理文件。
误区：表膨胀只影响存储成本。 它还影响扫描页数、缓存命中和查询延迟。
误区：一膨胀就执行 VACUUM FULL。 VACUUM FULL 锁重，要先评估生产影响。
追问：长事务为什么阻止 Vacuum？ 老事务可能还需要看到旧版本，数据库不能提前清理。
追问：索引膨胀怎么处理？ 评估后使用 REINDEX，可优先考虑并发重建方式。
追问：如何减少膨胀产生？ 缩短事务、调 autovacuum、优化 HOT、减少无用索引和批量更新。

八、面试中可以这样落地

排查时先看表大小、死元组、最近 vacuum 时间和长事务。治理时按优先级处理：杀长事务风险、调 autovacuum、重建膨胀索引、必要时低峰重写表。

select relname, n_dead_tup, last_autovacuum, last_autoanalyze
from pg_stat_user_tables
order by n_dead_tup desc
limit 10;

这个回答能体现你知道膨胀不是单一 SQL 问题，而是事务、Vacuum、更新模式和索引设计共同作用。

九、加强记忆

表膨胀记住“旧版本、长事务、Vacuum、重写”。普通 Vacuum 让空间可复用，VACUUM FULL 才会重写释放文件但锁重；索引膨胀要考虑 REINDEX。面试时先讲原因，再讲排查，最后讲治理顺序，不要上来就给危险命令。

简化版

详细版