postgresql - 自动清理后的索引大小

Question

美好的一天。我正在阅读与 Vacuum 过程和 Reindex 例程相关的 Postgres 官方文档。有些句子对我来说不清楚，所以我想澄清一下。(Postgres 版本 12 的文档)

首先。我确实了解 autovacuum 检查表中是否有死元组，将它们的位置存储在名为“maintenance_work_mem”的特殊内存中，然后当该内存已满时，vacuum 删除所有引用这些位置的索引中的相应页面。有关重新索引的文档 says

B-tree index pages that have become completely empty are reclaimed forre-use. However, there is still a possibility of inefficient use ofspace: if all but a few index keys on a page have been deleted, thepage remains allocated

问题是。如果“页面保持分配状态”，那么是否意味着 autovacuum 不会将索引内已删除页面的物理空间返回给操作系统？例如索引需要 1 GB 内存。我从表中删除了除一行之外的所有内容并运行了vacuum。在这种情况下索引仍将占用 1 Gb 内存。我说得对吗？

Answer 1

src/backend/access/nbtree 中的README 有很多关于此的深入信息。此答案中的引用来自那里。

如果您确实删除表中除一行以外的所有行，则索引中的几乎所有页面都会被删除。

We consider deleting an entire page from the btree only when it's becomecompletely empty of items. (Merging partly-full pages would allow betterspace reuse, but it seems impractical to move existing data items left orright to make this happen --- a scan moving in the opposite directionmight miss the items if so.) Also, we never delete the rightmost pageon a tree level (this restriction simplifies the traversal algorithms, asexplained below). Page deletion always begins from an empty leaf page. Aninternal page can only be deleted as part of deleting an entire subtree.This is always a "skinny" subtree consisting of a "chain" of internal pagesplus a single leaf page. There is one page on each level of the subtree,and each level/page covers the same key space.

但是，该空间不会释放给操作系统:

Reclaiming a page doesn't actually change its state on disk --- we simplyrecord it in the shared-memory free space map, from which it will behanded out the next time a new page is needed for a page split.

树会变得“瘦”，因为索引的深度永远不会缩小。 PostgreSQL 对此进行了优化:

Because we never delete the rightmost page of any level (and in particularnever delete the root), it's impossible for the height of the tree todecrease. After massive deletions we might have a scenario in which thetree is "skinny", with several single-page levels below the root.Operations will still be correct in this case, but we'd waste cyclesdescending through the single-page levels. To handle this we use an ideafrom Lanin and Shasha: we keep track of the "fast root" level, which isthe lowest single-page level. The meta-data page keeps a pointer to thislevel as well as the true root. All ordinary operations initiate theirsearches at the fast root not the true root.

如果您在索引上运行 REINDEX INDEX 或在表上运行 VACUUM (FULL)，索引将被重建，并且空间将被释放。