PostgreSQL建立索引並避免寫資料鎖定(並發的索引)

標籤：

關於並發建立索引：http://58.58.27.50:8079/doc/html/9.3.1_zh/sql-createindex.html

寫這篇blog源自一個帥哥在建索引發生了表鎖的問題。先介紹一下Postgresql的建索引文法： 
Version:9.1 

CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ name ] ON table [ USING method ]    ( { column | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )    [ WITH ( storage_parameter = value [, ... ] ) ]    [ TABLESPACE tablespace ]    [ WHERE predicate ]

這裡不解釋文法的諸多參數使用(排序，使用方法，填滿因數等)，主要說一下concurrently的使用情境。 
  
正常情況下Postgresql建立普通btree索引時會阻塞DML(insert,update,delete)操作，直到索引完成，期間讀操作不受阻塞。當只有一個使用者操作這當然沒問題，但是在生產環境，並發比較高的情況下，特別是大表建索引就不能這麼操作了，不然使用者要跳起來罵娘了，點個按鈕一天還沒反應過來。 

--使用 
Postgresql提供了一個參數，可以線上建立索引的時候避免因寫資料而鎖表，這個參數叫concurrently。使用很簡單，就是用create index concurrently來代替create index即可。 

--副作用 
當然了，使用這個參數是有副作用的，不使用這個參數建索引時DB只掃描一次表，使用這個參數時，會引發DB掃兩次表，同時等待所有潛在會讀到該索引的事務結束，有點類似增量索引，這麼一來，系統的CPU和IO，記憶體等會受一些影響，所以綜合考慮，仍然讓使用者自行選擇，而不是預設。 

--失敗 
在使用concurrently參數建索引時，有可能會遇到失敗的情況，比如建唯一索引索引探索資料有重複，又或者使用者發現建索引時建錯欄位的，取消建索引操作了。此時該表上會存在一個索引，這是因為帶這個參數的建索引命令一經發出，就首先會在系統的日誌表裡先插一個索引記錄進去，又因為這個索引最終建失敗了，所以會被標記一個INVALID的狀態，如下：

postgres=# \d t_kenyon       Table "public.t_kenyon" Column |  Type   | Modifiers --------+---------+----------- col    | integer | Indexes:    "idx" btree (col) INVALID

--重建 
遇到上述失效的索引重建時兩個辦法，一個是drop index index_name，然後再執行create index concurrently。還有一個是執行reindex index_name命令，但是後者不支援concurrently參數。 

--總結 
在生產上執行建立索引命令時最好帶上此參數，因為多消耗一點系統資源和時間來換取使用者的不間斷訪問更新是相對值得的，但是遇到緊急的任務或者超大的表還是要綜合考慮。 如果是索引重建，可以再在原基礎上建立一個不同名的相同索引，然後取消老的索引。 

PostgreSQL建立索引並避免寫資料鎖定(並發的索引)