PostgreSQL9.2Beta 对 drop index 命令增加 CONCURRENTLY 选项，当删除索引时，如果使用 CONCURRENTL 参数，那么在索引删除过程中不会阻塞其它 session。

Release 的说明

Add CONCURRENTLY option to DROP INDEX CONCURRENTLY (Simon Riggs)
This allows index removal without blocking other sessions.

Concurrently 参数说明

CONCURRENTLY
When this option is used, PostgreSQL will drop the index without taking any locks that prevent concurrent selects, inserts, updates, or deletes on the table; whereas a standard index drop waits for a lock that locks out everything on the table until it is done. Concurrent drop index is a two stage process. First, we mark the index both invalid and not ready then commit the change. Next we wait until there are no users locking the table who can see the index.

备注：意思是删除索引时如果指定 CONCURRENTLY 选项，那么删除索引的过程中不会阻塞当前发生在这张表上的的 Select, inserts, updates, 和 deletes 会话；而普通的删除索引操作，会获取排它锁，从而当删除索引过程中，会阻塞这张表上的任何操作，下面演示下；

Drop Index 举例

创建测试表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

francs=> create table test_dropindex (id integer ,name varchar(32));  
CREATE TABLE
francs=> insert into test_dropindex select generate_series(1,10000),'a';  
INSERT 0 10000
francs=> create index idx_test_dropindex_id on test_dropindex using btree (id);  
CREATE INDEX

francs=> /d test_dropindex  
 Table "francs.test_dropindex"  
Column | Type | Modifiers  
--------+-----------------------+-----------  
id | integer |  
name | character varying(32) |  
Indexes:  
 "idx_test_dropindex_id" btree (id)

开启删除索引事务

1
2
3
4
5
6
7
8
9
10

francs=> select pg_backend_pid();  
pg_backend_pid  
----------------  
 19301  
(1 row)
francs=> begin;  
BEGIN  

francs=> drop index idx_test_dropindex_id;  
DROP INDEX

备注：注意事务并没提交；

开另一会话

1
2
3
4
5

francs=> select locktype,database,relation,pid,mode,granted from pg_locks where relation=16547;  
locktype | database | relation | pid | mode | granted  
----------+----------+----------+-------+---------------------+---------  
relation | 16386 | 16547 | 19301 | AccessExclusiveLock | t  
(1 row)

备注：说明会话 19301 获取的是 “AccessExclusiveLock” 排它锁，这种类型索在获取的过程中会阻塞任何类型索的获取，换句话说在这个命令执行过程中，表上的所有操作将被阻塞。

注意事项

Drop Index Concurrently 使用注意以下：

drop index 命令可以在事务中执行，而 drop index concurrently 命令不可以；并且 CASCADE 选项也不支持； 那么 drop index concurrently获取的是什么类型的锁呢？由于 drop index concurrently 不支持在事务中执行，那么将不能模拟上述实验观察它获取锁的类型。

总结

drop index concurrently 特性适合业务繁忙的库，尽管删除索引的过程会很快，如果是比较大的表，并且是业务特别繁忙的库，那么删除索引过程时间可能较长，使用这个选项无疑是把利器，感谢 Simon Riggs 的贡献！

参考

• http://www.postgresql.org/docs/9.2/static/sql-dropindex.html