每天5分钟,PG聊通透 - 系列1 - 热门问题 - 链接、驱动、SQL - 第22期 - 为什么创建索引会堵塞DML? 如何在线创建索引?

作者

digoal

日期

2021-12-24

标签

PostgreSQL , 热门问题

• 问题说明(现象、环境)
• 分析原因
• 结论和解决办法

22、为什么创建索引会堵塞DML? 如何在线创建索引?

https://www.bilibili.com/video/BV1ER4y1g7RY/

https://www.postgresql.org/docs/14/explicit-locking.html

创建索引加载什么级别的锁?
- SHARE

DML(update,delete,insert)加载什么级别的锁?
- ROW EXCLUSIVE

冲突情况
- SHARE 与 ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE 冲突.

在线创建索引(CREATE INDEX CONCURRENTLY, REINDEX CONCURRENTLY). 加载什么级别的锁?
- SHARE UPDATE EXCLUSIVE
- Acquired by VACUUM (without FULL), ANALYZE, CREATE INDEX CONCURRENTLY, REINDEX CONCURRENTLY, CREATE STATISTICS, and certain ALTER INDEX and ALTER TABLE variants (for full details see the documentation of these commands).
- SHARE UPDATE EXCLUSIVE 与 SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE 冲突.
- 从上面的锁冲突情况分析: 同一个表不能同时使用CREATE INDEX CONCURRENTLY创建多个索引. 但是可以使用CREATE INDEX创建多个索引.

CREATE INDEX CONCURRENTLY分为多个阶段, 最初index是invalid的, 如果CREATE INDEX CONCURRENTLY失败, 这个索引的状态依旧是invalid的( pg_index.indisvalid = false), 需要drop index CONCURRENTLY清理.

相关代码:

src/backend/catalog/index.c src/backend/commands/indexcmds.c

        /*-----
         * Now we have all the indexes we want to process in indexIds.
         *
         * The phases now are:
         *
         * 1. create new indexes in the catalog
         * 2. build new indexes
         * 3. let new indexes catch up with tuples inserted in the meantime
         * 4. swap index names
         * 5. mark old indexes as dead
         * 6. drop old indexes
         *
         * We process each phase for all indexes before moving to the next phase,
         * for efficiency.
         */
        /*
         * Phase 1 of REINDEX CONCURRENTLY
         *
         * Create a new index with the same properties as the old one, but it is
         * only registered in catalogs and will be built later.  Then get session
         * locks on all involved tables.  See analogous code in DefineIndex() for
         * more detailed comments.
         */
        /*
         * Phase 2 of REINDEX CONCURRENTLY
         *
         * Build the new indexes in a separate transaction for each index to avoid
         * having open transactions for an unnecessary long time.  But before
         * doing that, wait until no running transactions could have the table of
         * the index open with the old list of indexes.  See "phase 2" in
         * DefineIndex() for more details.
         */
        /*
         * Phase 3 of REINDEX CONCURRENTLY
         *
         * During this phase the old indexes catch up with any new tuples that
         * were created during the previous phase.  See "phase 3" in DefineIndex()
         * for more details.
         */
        /*
         * Phase 4 of REINDEX CONCURRENTLY
         *
         * Now that the new indexes have been validated, swap each new index with
         * its corresponding old index.
         *
         * We mark the new indexes as valid and the old indexes as not valid at
         * the same time to make sure we only get constraint violations from the
         * indexes with the correct names.
         */
        /*
         * Phase 5 of REINDEX CONCURRENTLY
         *
         * Mark the old indexes as dead.  First we must wait until no running
         * transaction could be using the index for a query.  See also
         * index_drop() for more details.
         */
        /*
         * Phase 6 of REINDEX CONCURRENTLY
         *
         * Drop the old indexes.
         */

期望 PostgreSQL 增加什么功能?

PolarDB for PostgreSQL云原生分布式开源数据库

PostgreSQL 解决方案集合

德哥 / digoal's github - 公益是一辈子的事.