PostgreSQL建立索引如何避免寫數據鎖定
寫這篇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命令，但是后者不支持concurrent參數。
--總結
在生產上執行創建索引命令時最好帶上此參數，因為多消耗一點系統資源和時間來換取用戶的不間斷訪問更新是相對值得的。 如果是索引重建，可以再在原基礎上建立一個不同名的相同索引，然后取消老的索引。
英文原文： http://www.postgresql.org/docs/9.1/static/sql-createindex.html
