PostgreSQL Daily Maintenance - reindex

reindex日志維護

随着dml的發生, 索引會出現碎片.  持續膨脹.  降低效率. 雖然postgresql有vacuum機制, 但是索引不像heap表, vacuum後的dead tuple占用的空間可以被馬上回收複用, 以b-tree索引為例, 一個b-tree page隻有當所有的item全部變成不可用後這個page才能被複用. 是以索引膨脹的機率比表大很多.

例一 :

第一次全量更新後, 表和索引都膨脹了1倍.

vacuum後表的垃圾會回收, 但是索引的不會被回收.

是以第二次全量更新, 表不會再膨脹了, 但是索引繼續膨脹.

第三次vacuum與第二次類似.

全量更新後, 表未膨脹, 索引膨脹.

例二 : 

建立表, 索引, 插入500萬測試資料 :

使用pgbench對這個表做更新操作 : 

第一批更新後對表做vacuum, 回收dead tuple占用的空間 : 

檢視目前表的大小和索引的大小, 比建立表時大了一些. tbl_pkey沒變大多少是因為hot機制造成的. 

hot的詳細介紹可以參考src/backend/access/heap/readme.hot

再次使用pgbench對測試表其進行更新 : 

再次執行vacuum,

目前表的膨脹幾乎停止, 因為第一次vacuum後回收了dead tuple的空間.

被更新列的索引繼續膨脹, 因為索引頁的複用需要這個頁上的tuple itempoint完全失效後才可以.

是以要經常給索引瘦身, 同時又不能影響資料庫的dml操作.

可以使用如下方法, 重建例一中的索引 :

建立索引的同時不影響表的 dml操作.

建立好後删除索引1.

建立的索引又瘦下去了.

對于primary key或者unique key也可以使用此方法.