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

秦时明月之君临天下

已于 2022-11-13 17:35:29 修改

阅读量2.9k

点赞数

分类专栏： PostgreSQL 文章标签： postgresql database

于 2022-04-10 12:23:20 首次发布

本文链接：https://blog.csdn.net/weixin_41287260/article/details/124075956

版权

PostgreSQL 专栏收录该内容

92 篇文章

订阅专栏

在PostgreSQL中，CREATE INDEX CONCURRENTLY选项允许在创建索引时避免阻塞DML操作，适合生产环境。此过程涉及两次表扫描并在事务中构建索引，可能增加额外的CPU和I/O开销。如果构建过程中出现问题，可能会创建一个无效的索引，需要删除后重新尝试。并发构建唯一索引时，可能在索引可用前就报告违反唯一性约束。并发重建索引从PostgreSQL 12版本开始支持。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

正常的 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