如何在PostgreSQL中查找碎片索引并对其进行碎片整理？

Question

我在SQL Server中找到了解决此问题的方法（链接），但我该如何在PostgreSQL中实现？

- Roman Martyshchuk

Answer 1

通常情况下，您根本不需要担心这个。

但是，如果发生了大规模的删除或更新，或持续更改率非常高，以至于自动清理无法跟上，则可能会出现严重膨胀的索引。

用于确定这一点的工具是 pgstattuple 扩展程序：

CREATE EXTENSION pgstattuple;

你可以像这样检查索引膨胀：

SELECT * FROM pgstatindex('spatial_ref_sys_pkey');

-[ RECORD 1 ]------+-------
version            | 2
tree_level         | 1
index_size         | 196608
root_block_no      | 3
internal_pages     | 1
leaf_pages         | 22
empty_pages        | 0
deleted_pages      | 0
avg_leaf_density   | 64.48
leaf_fragmentation | 13.64

此索引品相完好（未被使用）：仅有14%的膨胀。

请注意，默认情况下使用90的fillfactor创建索引，也就是说，索引块不会被INSERT填充到超过90%。

很难说何时索引会出现膨胀，但如果leaf_fragmentation超过50-60，则情况不太好。

要重组索引，请使用REINDEX。

- Laurenz Albe

---

谨慎使用 REINDEX 处理大型索引，因为它会在父表上获取写入锁。一种替代策略是在相同的表和列上并发地构建一个具有不同名称的索引，然后删除原始索引并将新索引重命名。虽然这个过程比较长，但不需要对实时表进行任何长时间运行的锁定。 - Rohit Taneja

@RohitTaneja PostgreSQL v12有REINDEX CONCURRENTLY，这使得操作更加容易。 - Laurenz Albe

Answer 2

使用PostgreSQL，索引碎片化通常应由自动清理守护进程自动处理。如果您不使用自动清理守护进程，或者它无法跟上，您可以始终重新构建问题索引。

确定哪些索引可能严重碎片化并不是特别直截了当的，这在这篇博客文章和这篇PostgreSQL维基文章中得到了广泛讨论。

- Michael Powers