作者
digoal
日期
2019-03-30
标签
PostgreSQL , Delete empty pages during GiST VACUUM
背景
Delete empty pages during GiST VACUUM
VACUUM时，如果一个PAGE为EMPTY PAGE，支持将这个PAGE delete（非物理删除），只是从索引树中剥离。
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=7df159a620b760e289f1795b13542ed1b3e13b87
```
Delete empty pages during GiST VACUUM.
To do this, we scan GiST two times. In the first pass we make note of
empty leaf pages and internal pages. At second pass we scan through
internal pages, looking for downlinks to the empty pages.
Deleting internal pages is still not supported, like in nbtree, the last
child of an internal page is never deleted. That means that if you have a
workload where new keys are always inserted to different area than where
old keys are removed, the index will still grow without bound. But the rate
of growth will be an order of magnitude slower than before.
Author: Andrey Borodin
Discussion: https://www.postgresql.org/message-id/B1E4DF12-6CD3-4706-BDBD-BF3283328F60@yandex-team.ru
```
清理过程将分两个步骤，第一个步骤，VACUUM并扫描索引PAGE。记录EMPTY PAGE。第二个步骤，锁定EMPTY PAGE并将EMPTY关联的上下游PAGE断开，重组TREE。
DELETE的PAGE，回收为空闲页，下次有数据写入时，可以被重复使用。
从而降低索引膨胀概率。
https://git.postgresql.org/gitweb/?p=postgresql.git;a=blobdiff;f=src/backend/access/gist/README;h=84a4961d0c455cca815e891882b42121d0607f7a;hp=02228662b81248f26c72725997f159236416c499;hb=7df159a620b760e289f1795b13542ed1b3e13b87;hpb=df816f6ad532ad685a3897869a2e64d3a53fe312
```
+Bulk delete algorithm (VACUUM)
+------------------------------
+
+VACUUM works in two stages:
+
+In the first stage, we scan the whole index in physical order. To make sure
+that we don't miss any dead tuples because a concurrent page split moved them,
+we check the F_FOLLOW_RIGHT flags and NSN on each page, to detect if the
+page has been concurrently split. If a concurrent page split is detected, and
+one half of the page was moved to a position that we already scanned, we
+"jump backwards" to scan the page again. This is the same mechanism that
+B-tree VACUUM uses, but because we already have NSNs on pages, to detect page
+splits during searches, we don't need a "vacuum cycle ID" concept for that
+like B-tree does.
+
+While we scan all the pages, we also make note of any completely empty leaf
+pages. We will try to unlink them from the tree in the second stage. We also
+record the block numbers of all internal pages; they are needed in the second
+stage, to locate parents of the empty pages.
+
+In the second stage, we try to unlink any empty leaf pages from the tree, so
+that their space can be reused. In order to delete an empty page, its
+downlink must be removed from the parent. We scan all the internal pages,
+whose block numbers we memorized in the first stage, and look for downlinks
+to pages that we have memorized as being empty. Whenever we find one, we
+acquire a lock on the parent and child page, re-check that the child page is
+still empty. Then, we remove the downlink and mark the child as deleted, and
+release the locks.
+
+The insertion algorithm would get confused, if an internal page was completely
+empty. So we never delete the last child of an internal page, even if it's
+empty. Currently, we only support deleting leaf pages.
+
+This page deletion algorithm works on a best-effort basis. It might fail to
+find a downlink, if a concurrent page split moved it after the first stage.
+In that case, we won't be able to remove all empty pages. That's OK, it's
+not expected to happen very often, and hopefully the next VACUUM will clean
+it up.
+
+When we have deleted a page, it's possible that an in-progress search will
+still descend on the page, if it saw the downlink before we removed it. The
+search will see that it is deleted, and ignore it, but as long as that can
+happen, we cannot reuse the page. To "wait out" any in-progress searches, when
+a page is deleted, it's labeled with the current next-transaction counter
+value. The page is not recycled, until that XID is no longer visible to
+anyone. That's much more conservative than necessary, but let's keep it
+simple.
+
Authors:
Teodor Sigaev teodor@sigaev.ru
```
参考
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=7df159a620b760e289f1795b13542ed1b3e13b87
https://git.postgresql.org/gitweb/?p=postgresql.git;a=blobdiff;f=src/backend/access/gist/README;h=84a4961d0c455cca815e891882b42121d0607f7a;hp=02228662b81248f26c72725997f159236416c499;hb=7df159a620b760e289f1795b13542ed1b3e13b87;hpb=df816f6ad532ad685a3897869a2e64d3a53fe312
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
