索引中的碎片是如何发生的？

Question

我有一个200 of的表，索引为49 of。只有insert和update操作发生在该表上。我删除了现有的索引，并在相同的列上创建了一个新的索引。新索引大小仅为6 6GB。我正在使用postgres数据库

有人能解释一下索引大小是如何从50 6GB减少到6 6GB的吗？

Answer 1

新创建的索引本质上是经过优化打包的排序数据。要将更多的数据放在中间的某个位置，同时仍然保持最佳的打包排序数据，平均每次插入都必须重写一半的索引。

这是不可接受的，因此数据库对索引使用了某种复杂而巧妙的格式(基于b-tree数据结构)，允许在不将索引块移动到磁盘上的情况下更改它们的顺序。但这样做的结果是，在中间插入一些数据后，一些索引数据块并没有100%打包。剩余的空间可以在将来使用，但前提是插入的值在排序方面与块匹配。

因此，根据您的使用模式，您可以很容易地拥有平均只有10%的索引块。

当您更新一行时，旧版本和新版本都必须同时出现在索引中，这一事实使情况变得更加复杂。如果对整个表进行批量更新，那么索引将必须扩展以包含两倍的行数，尽管时间很短。但它不会那么容易地收缩，因为这基本上需要全部重写。

索引大小倾向于先增长，然后在使用后稳定下来。但稳定的大小往往远不及新创建的大小。

您可能希望将自动真空调优为更具侵略性-以便更快地恢复表和索引中不再需要的空间，从而可以更快地重用。这可以让你的索引更快更小地稳定下来。也要尽量避免太大的批量更新，或者在大型更新之后执行vacuum full tablename。