PG并发创建索引（CONCURRENTLY）

简介

创建索引可能会干扰数据库的常规操作。通常 PostgreSQL会锁住要被索引的表，让它不能被写入， 并且用该表上的一次扫描来执行整个索引的创建。其他事务仍然可以读取表 ， 但是如果它们尝试在该表上进行插入、更新或者删除，它们会被阻塞直到索引创建完成。如果系统是一个生产数据库，这可能会导致严重的后果。索引非常 大的表可能会需要很多个小时，而且即使是较小的表，在创建索引过程中阻塞 写入者一段时间在生产系统中也是不能接受的。

PostgreSQL支持创建索引时不阻塞写入。这种方法通过 指定CREATE INDEX的CONCURRENTLY选项 实现。当使用这个选项时，PostgreSQL必须执行该表的 两次扫描，此外它必须等待所有现有可能会修改或者使用该索引的事务终止。因此这种方法比起标准索引创建过程来说要做更多工作并且需要更多时间。不过，由于它允许在创建索引时继续普通操作，这种方式对于在生产环境中增加新索引很有用。 当然，由索引创建带来的额外 CPU 和 I/O 开销可能会拖慢其他操作。

当使用了CONCURRENTLY这个选项时，PostgreSQL在创建索引时 不会取得任何会阻止该表上并发插入、更新或者删除的锁。而标准的索引创建将会把表锁住以阻止对表的写（但不阻塞读），这种锁定会持续到索引创建完毕。

对于临时表，CREATE INDEX始终是非并发的，因为没有其他会话可以访问它们，并且创建非并发索引的成本更低。

在并发索引创建中，索引实际上在一个事务中被录入到系统目录，然后在两个 事务中发生两次表扫描。在每一次表扫描之前，索引创建必须等待已经修改了 表的现有事务终止。在第二次扫描之后，索引创建必须等待任何持有早于第二 次扫描的快照（见第 13 章）的事务终止。然后该索引最终 能被标记为准备好使用，并且CREATE INDEX命令终止。 不过即便那样，该索引也不是立刻可以用于查询：在最坏的情况下，只要早于 索引创建开始时存在的事务存在，该索引就无法使用。

如果在扫描表时出现问题，例如死锁或者唯一索引中的唯一性被违背， CREATE INDEX将会失败，但留下一个“不可用” 的索引。这个索引会被查询所忽略，因为它可能不完整。不过它仍将消耗更新 开销。psql的\d命令将把这类索引报告为 INVALID：

这种情况下推荐的恢复方法是删除该索引并且尝试再次执行CREATE INDEX CONCURRENTLY。 （另一种可能性是用REINDEX INDEX CONCURRENTLY重建该索引）。

如果是索引重建，可以再在原基础上建立一个不同名的相同索引，然后取消老的索引。

并发创建一个唯一索引时需要注意的另一点是，当第二次表扫描开始时，唯一约束 已经被强制在其他事务上。这意味着在该索引变得可用之前，其他查询中可能就会 报告该约束被违背，或者甚至在索引创建最终失败的情况中也是这样。还有，如果在 第二次扫描时发生失败，“无效的”索引也会继续强制它的唯一性约束。

表达式索引和部分索引的并发创建也被支持。在这些表达式计算过程中发生的 错误可能导致和上述唯一约束违背类似的行为。

常规索引创建允许在同一个表上同时创建其他常规索引，但是在一个表上同时 只能有一个并发索引创建发生。在两种情况下，在索引被创建时不允许表的模式修改。另一个不同是，一个常规CREATE INDEX 命令可以在一个事务块中执行，但是 CREATE INDEX CONCURRENTLY不行。

当前不支持在分区表上并发生成索引。 然而，你可以在每个分区上单独的并发创建索引，然后最终以非并发的方式创建分区索引，以减少对分区表的写入被锁定的时间。 在这种情况下，生成分区索引仅是元数据操作。

示例

PG从较早的版本就支持在线并发创建索引（不长时间阻塞DML），这对于如今停机时间越来越少的OLTP系统来说是非常重要的特性之一：

1、先不使用concurrently参数创建索引，此时insert操作会阻塞，但是创建索引所消耗的时间较短：

表数据量约为500万

可见创建索引消耗时间为19秒。

但是在创建索引的过程中，另开一个窗口来执行的insert操作会被阻塞，直到索引创建完成才会执行：

2、使用concurrently参数，并发创建索引：

可见所消耗的时间约为21秒，相比非concurrently方式来说，时间变长。

但是在创建索引的过程中，另开一个窗口来执行的insert操作等均是无阻塞的：

两种方法创建的索引大小并无区别：

3、在使用concurrently选项时，若强行取消创建操作，那么会留下一个无效的索引。

这种情况下推荐的恢复方法是

1、删除该索引并且尝试再次执行CREATE INDEX CONCURRENTLY。 如果是索引重建，可以再在原基础上建立一个不同名的相同索引，然后删除老的索引。

2、用REINDEX INDEX CONCURRENTLY重建该索引。

记得，需要删除INVALID状态的索引。

总结

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

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

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

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

从pg12开始，REINDEX语句带有CONCURRENTLY参数

这个的实现原理是通过临时建立一个新索引({index_name}_ccnew)随后用old_index名字替换的方式进行的.

本功能实现了在不阻塞读和写的情况下rebuild index

REINDEX CONCURRENTLY 语句会等待会潜在使用该索引的已经存在的事务结束，因此，时间比标准的index rebuild的时间要长

REINDEX CONCURRENTLY 语句在执行时，不阻塞写也不阻塞读。标准的index rebuild阻塞写不阻塞读，

REINDEX CONCURRENTLY 语句会消耗额外的cpu、memroy、io资源，

需要注意:

1.针对temporary tables，reindex总是non-concurrent

2.REINDEX SYSTEM命令不支持CONCURRENTLY

3.REINDEX CONCURRENTLY不能在事务块内执行，但是REINDEX TABLE和REINDEX INDEX可以在事务块内执行

4.常规的index builds允许在同一个表上进行其他的常规index builds，但是一次只能在一个表上进行一个concurrent index build，在这两种情况下，都不允许同时在表上进行其他类型的schema modification

5.排除约束的索引不能reindex concurrently。如果reindex concurrently的是此种索引，则会引发错误。

如果同时对具有排除约束索引的表或数据库运行reindexed concurrently，则将跳过这些索引。（可以在不使用该CONCURRENTLY选项的情况下重新建立此类索引。）

6.REINDEX CONCURRENTLY的过程，会执行2次table的扫描

REINDEX CONCURRENTLY 语句的执行步骤如下，需要注意：每个步骤是运行在一个transaction中，如果有多个索引需rebuid，那么，每个步骤对所有的index进行循环，然后再转移到下一个步骤。

1.一个新的临时索引的定义被加到pg_index系统表中，该定义将会被用来取代old index。

一个session级别的SHARE UPDATE EXCLUSIVE锁会加在该index和table上，用来防止任何schema的修改

2.第一次scan是用于每个新index建立index完成。一旦建立新索引完成，pg_index.indisready这个标志列会改为true，以表明该索引已经准备好用于inserts语句，

一旦这个build index的事务执行完成，让该新index对其他session可见。本步骤是“1个index对应1个transaction”的模式

3.第二次scan是执行add tuple，这些tuple是第一点scan过程中产生的。本步骤是“1个index对应1个transaction”的模式

4.参考索引的所有constraint更改为参考新的index定义，并且索引名字被改变。

此时，新索引的pg_index.indisvalid 标志列会改为true，老索引的pg_index.indisvalid 标志列会改为false

还做了cache invalidation操作，该操作会将引用了old index的所有session invalidated

5.在引用老索引的query完成之后，老索引的pg_index.indisready标志列会改为false，以防止新tuple的插入。

6.老索引被drop掉，index和table上的session级的SHARE UPDATE EXCLUSIVE被释放

当rebuild index出现问题时，比如在一个unique index中的 uniqueness violation，reindex命令会失败，这会导致invalid索引和已经存在的索引共存。

invalid索引会被SQL语句(翻译成优化器)忽略，因为invalid索引可能不完整，但是invalid索引会消耗update的负载。

psql的\d命令可以看到该表上的索引：

如果标记有INVALID的索引是以ccnew结尾的，这个索引就表示是在concurrent操作期间建立的临时索引，推荐的恢复方法是drop 掉该invalid索引，并重新运行REINDEX CONCURRENTLY

如果标记有INVALID的索引是以ccold结尾的，这个索引就表示是没有drop掉的索引，推荐的恢复方法是drop这个ccold结尾的invalid的索引。

实验如下：