PostgreSQL 索引操作与锁机制对比

PostgreSQL 索引操作与锁机制对比

一、 添加索引 (CREATE INDEX) 对比

操作方式	表级锁类型	是否阻塞读	是否阻塞写	适用场景
CREATE INDEX	SHARE	否	是	维护窗口，速度极快
CREATE INDEX CONCURRENTLY	SHARE UPDATE EXCLUSIVE	否	否	生产环境，不锁业务，但速度慢

---

二、 重建索引 (REINDEX INDEX) 核心对比

重建索引通常用于清理索引碎片（如大量 UPDATE/DELETE 导致的 B-tree 膨胀）。

特性	REINDEX INDEX (普通重建)	REINDEX INDEX CONCURRENTLY (并发重建)
是否阻塞读	是（依赖该索引的查询会被卡住）	否
是否阻塞写	是（INSERT/UPDATE/DELETE 会被卡住）	否
底层索引级锁	ACCESS EXCLUSIVE（极重锁）	SHARE UPDATE EXCLUSIVE（轻量锁）
构建速度	极快（单次扫描，原地覆盖）	较慢（需多次扫描表，等待老事务）
磁盘空间占用	几乎不额外占用（原地重写）	需要双倍空间（新老索引同时存在）
失败后的处理	自动回滚，无残留	会留下 INVALID 索引，需手动清理

为什么速度差异这么大？

• 普通重建（瞬间完成）：直接在索引上加排他锁，暴力清空文件并重写。不需要管并发事务，也不需要记录中间过程的 WAL 日志，属于“简单粗暴”。
• 并发重建（耗时较长）：为了不锁表，底层必须走“暗中偷梁换柱”流程：
  1. 全表扫描一次，建一个对业务不可见的新索引。
  2. 等待老事务结束，抓取这段时间内的数据变更（增量）补充到新索引。
  3. 再次等待新产生的事务结束，第二次抓取增量。
  4. 在极短的瞬间加锁，将新老索引名称互换。
     因为要扫两次表、等两次事务、且记录大量 WAL 日志，所以耗时成倍增加。

---

三、 生产环境避坑指南

1. 磁盘空间陷阱（高危）

• 普通重建：原地重写，不额外吃空间。
• 并发重建：在替换完成前，老索引和新索引是同时存在的。如果被重建的索引有 10GB，那么重建期间磁盘会瞬间多出 10GB 消耗。如果磁盘空间不足，极易导致重建失败甚至影响其他业务。

2. 失败后的残留处理

• 普通重建如果被 kill 或报错，数据库会自动回滚，干干净净。
• 并发重建如果中途失败（比如磁盘满），它不会自动删除新建了一半的索引。你会在表中看到一个状态为 INVALID 的残缺索引。
• 必须手动执行清理：DROP INDEX invalid_index_name;，否则它会一直白占磁盘空间。

3. 选型建议

• 能用普通重建就先用普通：在低峰期、允许停写几秒到几分钟的情况下，首选普通重建，省时省空间。
• 核心库白天抢救时才用并发：7x24 核心大表发生严重索引膨胀导致性能急剧下降时使用，使用前务必检查磁盘剩余空间是否大于最大索引的 1.5 倍。