PostgreSQL建立索引如何避免寫數(shù)據(jù)鎖定
寫這篇blog源自一個帥哥在建索引發(fā)生了表鎖的問題。先介紹一下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 ]
這里不解釋語法的諸多參數(shù)使用(排序，使用方法，填充因子等)，主要說一下concurrently的使用場景。
正常情況下Postgresql建立普通btree索引時會阻塞DML(insert,update,delete)操作，直到索引完成，期間讀操作不受阻塞。當只有一個用戶操作這當然沒問題，但是在生產環(huán)境，并發(fā)比較高的情況下，特別是大表建索引就不能這么操作了，不然用戶要跳起來罵娘了，點個按鈕一天還沒反應過來。
--使用
Postgresql提供了一個參數(shù)，可以在線建立索引的時候避免因寫數(shù)據(jù)而鎖表，這個參數(shù)叫concurrently。使用很簡單，就是用create index concurrently來代替create index即可。
--副作用
當然了，使用這個參數(shù)是有副作用的，不使用這個參數(shù)建索引時DB只掃描一次表，使用這個參數(shù)時，會引發(fā)DB掃兩次表，同時等待所有潛在會讀到該索引的事務結束，這么一來，系統(tǒng)的CPU和IO，內存等會受一點影響，所以綜合考慮，仍然讓用戶自行選擇，而不是默認。
--失敗
在使用concurrently參數(shù)建索引時，有可能會遇到失敗的情況，比如建唯一索引索引發(fā)現(xiàn)數(shù)據(jù)有重復，又或者用戶發(fā)現(xiàn)建索引時建錯字段的，取消建索引操作了。此時該表上會存在一個索引，這是因為帶這個參數(shù)的建索引命令一經發(fā)出，就首先會在系統(tǒng)的日志表里先插一個索引記錄進去，又因為這個索引最終建失敗了，所以會被標記一個INVALID的狀態(tài)，如下：
- postgres=# \d t_kenyon
- Table "public.t_kenyon"
- Column | Type | Modifiers
- --------+---------+-----------
- col | integer |
- Indexes:
- "idx" btree (col) INVALID
--重建
遇到上述失效的索引重建時兩個辦法，一個是drop index index_name，然后再執(zhí)行create index concurrently。還有一個是執(zhí)行reindex index_name命令，但是后者不支持concurrent參數(shù)。
--總結
在生產上執(zhí)行創(chuàng)建索引命令時最好帶上此參數(shù)，因為多消耗一點系統(tǒng)資源和時間來換取用戶的不間斷訪問更新是相對值得的。 如果是索引重建，可以再在原基礎上建立一個不同名的相同索引，然后取消老的索引。
英文原文： http://www.postgresql.org/docs/9.1/static/sql-createindex.html
