PostgreSQL CREATE INDEX CONCURRENTLY 全表扫描两次原因分析 - 教程

CREATE INDEX CONCURRENTLY 在 PostgreSQL 中会完整地扫描两次表，并可能进行第三次扫描来验证，这是由其设计目标–在创建索引时不阻塞表的读写操作所决定的。

下面详细解释这几次扫描的目的和过程：

核心原因：无锁并发构建

为了在不获取强锁的情况下构建索引，PostgreSQL 必须解决一个核心矛盾：表的实时信息在索引构建期间正在被其他事务修改。为了得到一个与表数据最终一致的索引，它需要通过多次扫描来“捕捉”所有变化。

三次扫描的详细流程

假设我们执行 CREATE INDEX CONCURRENTLY idx_name ON table_name (column_name);

第一次扫描：初始化索引构建
  1. 目的：创建一个空的、尚未就绪的索引结构，并填充大部分初始数据。
  2. 过程：
    • 首先，它获取一个 ShareLock 在表上。这个锁会阻止其他 DROP TABLE 或 ALTER TABLE 等DDL操作，但不会阻塞普通的 INSERT、UPDATE、DELETE 操作。
    • 然后，它扫描表（例如，依据顺序扫描），将扫描到的每一行资料对应的索引条目添加到这个新的索引中。
  3. 问题：在第一次扫描期间，其他事务可能正在修改数据。比如：
    • 扫描到第100页时，一个事务更新了第50页的一行数据。该更新可能发生在第一次扫描之后，因此第一次扫描构建的索引中不包含这个更新的新值。
    • 这就导致了索引丢失了在第一次扫描开始后发生的更新。
第二次扫描：捕捉“第一次扫描”期间遗漏的更改
  1. 目的：捕获在第一次扫描开始后并发事务所做的所有修改，并将这些修改应用到索引中，确保索引能反映一个一致性快照。
  2. 过程：
    • 第二次扫描开始时，PostgreSQL 会记下当前时刻的事务ID，作为这次扫描的“逻辑时间点”。
    • 它再次扫描整个表。对于每一行，它会检查该行是否在第一次扫描开始后、第二次扫描开始前被修改过（通过检查行的 xmin 事务ID）。
    • 如果发现这样的行，说明它在第一次扫描时可能被遗漏了（或者当时是旧版本），现在需将其最新的索引条目插入到索引中。
  3. 为什么需要第二次扫描？因为第一次扫描不是一个原子操作，它持续了一段时间。第二次扫描通过一个“时间点”来补录所有在此期间发生的变更。
第三次“扫描”：验证与等待（关键步骤）

这是最微妙的一步，严格来说它不是一次完整的表扫描，但逻辑上可以视为一次“验证扫描”。

  1. 目的：确保索引对于表中的所有活跃事务完整的。就是都
  2. 过程：
    • 索引现在包含了截至第二次扫描开始时的所有数据。
    • ，在第二次扫描就是只开始后，仍然可能有新的事务在修改数据。PostgreSQL 现在需要等待所有这些在第二次扫描开始前就存在的老事务结束。
    • 它通过检查所有当前活跃的事务，并等待那些在第二次扫描开始时还在活跃的事务（Old Snapshots）全部提交或回滚。
    • 对于这些老事务，如果在它们提交时产生了新的索引条目（例如，它们修改了数据），这些条目会被索引的普通操作机制（INSERT/UPDATE 触发索引插入）自动加入到索引中。
    • 验证100%完整的。就是：一旦所有老事务结束，索引就必须包含所有这些事务可能提交的、对表有影响的更改。此时，PostgreSQL 会进行一次快速的完整性检查，确保索引对于表的最新状态
  3. 为什么需要第三次验证？为了保证没有任何一个在索引构建期间活跃的事务，其数据变更会丢失在索引之外。这是实现真正事务一致性的关键。

总结与类比

你可以把这个过程想象成一个聪明的图书管理员在不停业的的情况下为图书馆编制一份新的索引卡：

  1. 第一次扫描：他快速走遍所有书架，把大部分书籍的信息抄到新索引卡上。
  2. 第二次扫描：他再走一遍，专门查找并记录下在他第一次走过之后，读者们放回书架或移动位置的书籍。
  3. 第三次验证：他等待所有在他开始工作前就在图书馆里的读者都离开。因为这些读者可能在他编制索引期间从书架上取走或放回了书，这些操作必须最终反映在索引卡上。等他们都走了，他再快速核对一下，确保索引卡和书架上的书完全对应。

注意事项

  • 性能影响：虽然不阻塞读写，但两次全表扫描会带来大量的 I/O 和 CPU 开销，可能对系统性能产生影响。
  • 失败可能性：因为整个过程很长且与并发操作交互，如果中途出现问题（如唯一约束冲突、事务回滚等），CONCURRENTLY 操作可能会失败，并且会留下一个 INVALID 的索引。你需要手动删除这个无效索引。
  • 与普通 CREATE INDEX 对比：普通的 CREATE INDEX 只需要一次表扫描，因为它会锁住表以防止任何写入，从而保证数据在扫描期间是静态的、一致的。

正是通过此种看似“笨拙”但极其严谨的多次扫描和验证机制，PostgreSQL 才能在最小化锁的情况下，安全地在线创建索引。

posted @ 2025-12-13 08:46  gccbuaa  阅读(18)  评论(0)    收藏  举报