PostgreSQL在线创建索引你不得不注意的"坑"

Create index concurrently

我们知道数据库创建索引可能会锁住创建索引的表，并且用该表上的一次扫描来执行整个索引的构建，这样在创建索引时会影响在线业务，非常大的表创建索引可能会需要几个小时，这样阻塞业务是不被允许的。商业数据库一般都提供在线创建索引的能力，PostgreSQL作为开源数据库，也提供了这样的功能。我们在CREATE INDEX命令中新增CONCURRENTLY选项来实现索引的在线创建。

但是concurrently在线创建索引也并不是那么完美，当使用这个选项时，PostgreSQL必须执行该表的两次扫描，此外它必须等待所有现有可能会修改或者使用该索引的事务终止，甚至它可能会等待一个不相干的事务终止。因此这种方法比起普通的索引创建过程来说要做更多工作并且需要更多时间。同时，索引的创建会带来较大的CPU和I/O消耗。甚至在极端情况下，如果数据库存在长事务，我们发现create index命令根本无法结束。

从官方文档中我们可以了解到如下信息，在并发（concurrently）索引构建中，索引实际上是在事务中被构建的，它在两个事务中发生两次表扫描。在每一次表扫描之前，索引构建必须等待对该表做过修改的现有事务终止。在第二次扫描之后，索引构建必须等待任何持有早于第二次扫描的快照的事务终止。然后该索引最终能被标记为可用，CREATE INDEX命令完成。

创建过程

在PG源码src/backend/catalog/index.c文件中记录了并发创建索引的过程，大致分为如下几个步骤：

1.开启第一个事务，拿到当前快照snapshot1

2.等待所有修改过该表的事务结束

3.扫描该表，第一次创建索引

4.结束第一个事务

5.开启第二个事务，拿到当前快照snapshot2

6.等待所有修改过该表的事务结束

7.第二次扫描该表，将两次快照之间变更的记录，合并到索引

8.上一步更新索引结束后，等待snapshot2之前开启的所有事务结束

9.结束索引创建，索引变为可用

那么这里有个疑问，为什么需要两次扫描、两次创建索引？其实想想也很好解释。因为在第一次创建索引的时候不阻塞读写，这段时间内发生的变更需要在第二次扫描的时候合并更新进索引。

“坑”在哪里

如果在扫描表的过程中出现问题，例如死锁或者唯一索引中的唯一性被违背， CREATE INDEX将会失败，这样会留下一个“invalid” 的索引。这个索引会被查询所忽略，因为它可能不完整。不过它仍将消耗更新开销，所以对于这类索引我们应该将它删除重建或者在变更窗口执行reindex。

还有另一点需要注意的是，在第一次扫描后创建的索引，该索引的约束其实已经开始对其他事务生效。这在该索引经过第二阶段变成可用之前，其他事务的查询中可能就会报告该约束被违反，甚至在索引后续阶段发生错误造成最终构建失败变为invalid的情况下，该索引的唯一性约束依然有效。

普通的create index操作会获取sharelock 5级锁，该锁是非自排他的，所以pg允许在同一个表上同时构建其他常规索引，但是create index concurrently操作会获取shareupdateexclusivelock 4级锁，该锁是自排他的，会和同样的锁类型冲突，所以在一个表上同时只能有一个并发索引构建。还有一个区别是，CREATE INDEX 命令可以在一个事务块中执行并且回滚，但是 CREATE INDEX CONCURRENTLY不能在事务块中执行。

实验验证

下面我们做两个实验验证一下长事务对并发创建索引的影响，创建两张表test1和test2

实验1：验证本表的长事务对并发创建索引的影响

会话1：

postgres=# begin;
BEGIN
postgres=# update test1 set id=2;
UPDATE 1

会话2：

postgres=# create index concurrently on test1(id);

发现会话2hang住，会话1事务结束后会话2完成。

实验2：验证其他表长事务对并发创建索引的影响

会话1：（使用copy from stdin模拟一个一直活动的事务）

postgres=# begin;
BEGIN
postgres=# copy test1 from stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself, or an EOF signal.
>>

会话2：

postgres=# create index concurrently on test2(id);

发现会话2hang住，会话1结束后会话2完成。按理说在会话1只操作了test1表，不会影响test2表上的索引创建，但是结果却是会话2 hang住了。这样验证了如果在snapshot2之前开启了数据库事务，那么索引的创建会等待该事务的结束。

小结

最后对并发创建索引需要注意的地方做一个总结：

1.并发创建索引需要扫描表两次，等待表事务三次，需要消耗更多的资源以及等待更长的时间。

2.在第二阶段索引构建过程中发生失败，那么第一阶段构建的索引会变为不可用，但是仍然会影响性能，同时唯一性约束依然生效，我们需要删除掉该索引进行重建。

3.并发创建索引可能由于长事务的原因造成索引创建一直等待，这个事务可能并非是该表上的事务，这是特别需要注意的一点。