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

2023-02-08

postgresql   shell  

原文地址 cloud.tencent.com

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