PostgreSQL 查询变慢原因分析（索引膨胀）

现象

PostgreSQL查询变慢，磁盘空间占用变多。

原因

无序索引

主键使用UUID存储。

B-tree索引在插入时，会根据主键值自动排序，以保持树的平衡。因UUID V4无序，在每次插入新记录时，新的键值可能分布在索引的任意位置。这可能导致频繁的页面分裂和重平衡操作。

当一个索引页面已满且需要插入一个新的无序主键时，B-tree索引会执行页面分裂。新页面会创建并将一些现有数据移到新页面，从而为新数据腾出空间。页面分裂增加了碎片化和未使用的空间。

频繁的插入、更新、删除操作

每次删除和更新操作只是将旧记录标记为“已删除”，新的数据会插入新的位置。

Vacuum 不彻底

VACUUM命令用于回收已标记为删除的数据行所占的空间。但VACUUM并不会合并和紧凑索引页面。

需要使用VACUUM FULL，但会全局锁表。

老化的索引

随着时间推移，索引中的碎片、空洞和未使用的空间会增加。这尤其在一些频繁变动的表和索引上表现显著。

需要定期对索引进行REINDEX。

解决方法

顺序主键

自增整数或基于时间顺序生成的值，以减少索引的页面分裂和膨胀。

批量插入处理

通过批量插入操作，可以减少单次插入带来的索引调整开销，从而优化索引的利用率。