PostgreSql 重建索引的操作

PostgreSql數據庫的重建索引時通過REINDEX命令來實現的,如reindexindex_name;

其語法是:

REINDEX { INDEX | TABLE | DATABASE | SYSTEM } name [ FORCE ];

下面解釋下說明情況下需要:

1、當由於軟件bug或者硬件原因導致的索引不再可用,索引的數據不再可用;

2、當索引包含許多空的或者近似於空的頁,這個在b-tree索引會發生。Reindex會騰出空間釋放哪些無用的頁(頁就是存放數據的一個單位,類似於block)。

3、PostgreSql數據庫系統修改瞭存儲參數,需要重建不然就會失效(如修改瞭fillfactor參數);

4、創建並發索引時失敗,遺留瞭一個失效的索引。這樣的索引不會被使用,但重構後能用。一個索引的重構不能並發的執行。

下面介紹下重構索引命令的參數:

1、INDEX 重構指定的索引;

2、TABLE 重構指定表的所有索引,包括下級TOAST表;

3、DATABASE重構指定數據庫的所有索引,系統共享索引也會被執行。需要註意的是這個級別的重構不能再一個事務塊中執行。

4、SYSTEM 重構這個系統的索引包含當前的數據庫。共享系統中的索引頁是被包含的,但是用戶自己的表是不處理的,同樣也不能在一個事務塊中執行。

5、Name 按照不同級別索引的名稱。

6、FORCE 已經被廢除即使寫瞭也是被忽略的。

示例:

REINDEX INDEX my_index;
REINDEX TABLE my_table;
REINDEX DATABASE broken_db;

另外需要註意的是:

1、重建索引不同的級別的重構需要不同的權限,比如table那麼就需要有這個表的權限即需要有操作索引的權限,如超級用戶postgres擁有這個權限。

2、重構索引的目的是為瞭當索引的數據不可信時,即對於成本的計算會出現偏差較大,無益於優化器得到最優的執行計劃以至於性能優化失敗。

3、重構索引類似於先刪除所有再創建一個索引,但是索引的條目是重新開始的。重構時當前索引是不能寫的,因為此時有排他鎖。

4、在8,1版本之前REINDEX DATABASE 隻包含系統索引,並不是期望的所有指定數據庫的索引。7.4版本之前REINDEX TABLE不會自動執行下級TOAST tables。

關於TOAST tables的含義:

TOAST直接翻譯的話就是切片面包(slicedbread)的意思,全稱是The Oversized-Attribute Storage Technique,

為什麼會有OVERSIZED-ATTRIBUTE呢?原因很簡單,因為在PostgreSQL,一條記錄不能跨PAGE存儲,

跨越PAGE的話必須使用TOAST(即unaligned,與原表分開存儲)存儲。

TOAST表不能獨立創建,隻有當普通表包含瞭main,extended或external存儲格式的字段時,系統會自動創建一個和普通表關聯的TOAST表。

當一條記錄(tuple)在存儲時(如果壓縮的話算壓縮後的大小)大於TOAST_TUPLE_THRESHOLD(通常是2kB)這個值時,會存儲到TOAST表。

而此時在普通表的該字段處包含瞭一個指向TOAST的tableoid和chunk_id的數據,從而能夠找到該字段的記錄。

補充:pg刪除索引遇到的坑

正常我們在刪除索引的時候會報如下錯誤,

然後隻需要設置索引路徑就ok

set search_path=bi_dpa;

以上為個人經驗,希望能給大傢一個參考,也希望大傢多多支持WalkonNet。如有錯誤或未考慮完全的地方,望不吝賜教。