PostgreSQL非阻塞性创建索引CREATE INDEX CONCURRENTLY

正常的 create index 是会阻塞 dml 操作的，在生产环境需要添加 concurrently 参数。

CREATE INDEX CONCURRENTLY idx_index_name ON TABLE_NAME ( COLUMN_NAME );

CONCURRENTLY参数说明

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

并发构建索引

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

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

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

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

postgres=# \d tab
       Table "public.tab"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 col    | integer |           |          | 
Indexes:
    "idx" btree (col) INVALID

这种情况下推荐的恢复方法是删除该索引并且尝试再次执行 CREATE INDEX CONCURRENTLY（另一种可能性是用 REINDEX重建该索引。不过，由于 REINDEX不支持并发构建，这种选择不那么有吸引力）。

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

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

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

版本支持

并发创建索引是从比较早的版本就已经支持了的（9.3之前吧，具体版本未知）。

而，重建索引，是从pg12开始支持并发重建的。

详见：http://www.postgres.cn/docs/12/sql-reindex.html#SQL-REINDEX-CONCURRENTLY

---

参考：

• https://blog.csdn.net/ctypyb2002/article/details/108506941
• https://www.postgresql.org/docs/8.2/sql-createindex.html
• http://www.postgres.cn/docs/10/sql-createindex.html

本文作者： 荷塘月色
发布时间： 2022-04-10
最后更新： 2023-06-05
本文标题： PostgreSQL非阻塞性创建索引CREATE INDEX CONCURRENTLY
本文链接： https://www.yansheng.fun/article/f22c1d8c.html
版权声明： 本作品采用 CC BY-NC-SA 4.0 许可协议进行许可。转载请注明出处！

赏

谢谢你请我吃糖果

支付宝

微信