前言

在日常运维PostgreSQL的过程中，我们经常会面临这么一个场景：开发发版，上线了新功能，结果由于新上线的SQL缺了某些关键索引采取了seq scan，并发上来最终导致雪崩。于是乎，急急忙忙去加索引，但是由于在PostgreSQL中，创建索引create index获取的是ShareLock，根据锁的冲突矩阵 👇

ShareLock和RowExclusiveLock是冲突的，所以create index会等待表上所有的增删改结束，对于实际的线上应用来说，几乎是不可能的，因为业务一直在运行，不断地增删改，所以create index就会一直处于锁队列中。假如幸运的话，create index获取到了锁，但是基表又是个大表，在v11以前，还不支持并行创建索引，所以会出现这么一个情况

1.create index获取到了锁，在慢吞吞的创建中...在v11以前的版本，只能干瞪眼（当然也可以调大maintenance_work_mem，创建索引必须对数据排序，如果要排序的数据在maintenance_work_mem中放不下，就会溢出到磁盘，效果不明显）2.由于第一步获取到了锁，后续进来的增删改全部阻塞，在等待索引创建完成释放锁3.不断堆积，然后导致pg_locks里面的条目越来越多，最终可能导致 out of shared memory

所以，为什么会一直强调在做DDL操作或需要重锁的时候，加上lock_timeout，就是为了防止类似的雪崩效应。

当然，还有一个方式便是采用create index concurrently了，根据锁的冲突矩阵，可以看到create index concurrently采用的是ShareUpdateExclusiveLock，这个和RowExclusiveLock不冲突，所以可以采用这个方式建索引，但是理想很美好，现实很骨干。

坑在哪里

汝之蜜糖，吾之砒霜。坑在哪里？先简单看下官网的介绍吧 

PostgreSQL supports building indexes without locking out writes. This method is invoked by specifying the CONCURRENTLY option of CREATE INDEX. When this option is used, PostgreSQL must perform two scans of the table, and in addition it must wait for all existing transactions that could potentially modify or use the index to terminate. Thus this method requires more total work than a standard index build and takes significantly longer to complete. However, since it allows normal operations to continue while the index is built, this method is useful for adding new indexes in a production environment. Of course, the extra CPU and I/O load imposed by the index creation might slow other operations.

In a concurrent index build, the index is actually entered into the system catalogs in one transaction, then two table scans occur in two more transactions. Before each table scan, the index build must wait for existing transactions that have modified the table to terminate. After the second scan, the index build must wait for any transactions that have a snapshot (see Chapter 13) predating the second scan to terminate, including transactions used by any phase of concurrent index builds on other tables, if the indexes involved are partial or have columns that are not simple column references. Then finally the index can be marked ready for use, and the CREATE INDEX command terminates. Even then, however, the index may not be immediately usable for queries: in the worst case, it cannot be used as long as transactions exist that predate the start of the index build.

首先根据介绍可以看到，create index concurrently需要两次扫描表，所以不难想到，会造成一些额外的CPU和IO。

另外一个容易被人忽略的场景是，通常情况下我们遇到的阻塞情况是针对同一个对象的，比如争抢同一个表的表锁，争抢同一个数据页的页锁，在create index concurrently中，不同表上的操作会阻塞吗？答案是会的。

in the worst case, it cannot be used as long as transactions exist that predate the start of the index build.

直接复现一下，这个也是之前线上同事找到我的时候的现象。

postgres=# create table test(id int);
CREATE TABLE
postgres=# create table test1(id int);
CREATE TABLE
postgres=# insert into test values(1);
INSERT 0 1
postgres=# insert into test1 values(1);
INSERT 0 1
postgres=# select pg_backend_pid();
 pg_backend_pid 
----------------
          23490
(1 row)

postgres=# begin;
BEGIN
postgres=*# select id from test for update;
 id 
----
  1
(1 row)

再开一个会话，也是获取test表的行锁

postgres=# select pg_backend_pid();
 pg_backend_pid 
----------------
          23492
(1 row)

postgres=# begin;
BEGIN
postgres=*# select id from test for update;
---被阻塞

此处被阻塞我们很好理解，同一行的行锁等待。不过多叙述。那么，再开个会话，使用concurrently创建索引。观察一下现象

postgres=# select pg_backend_pid();
 pg_backend_pid 
----------------
          21452
(1 row)

postgres=# create index concurrently myidx on test1(id);
--此处阻塞

可以看到，create index concurrently被阻塞了，并且是两个不同的表，这个与我们日常运维中碰到的锁等待大相径庭。

postgres=# SELECT                                      
    blocked_locks.pid AS blocked_pid,
    blocked_activity.usename AS blocked_user,
    now() - blocked_activity.query_start AS blocked_duration,
    blocking_locks.pid AS blocking_pid,
    blocking_activity.usename AS blocking_user,
    now() - blocking_activity.query_start AS blocking_duration,
    blocked_activity.query AS blocked_statement,
    blocking_activity.query AS blocking_statement
FROM                                                       
    pg_catalog.pg_locks AS blocked_locks
    JOIN pg_catalog.pg_stat_activity AS blocked_activity ON blocked_activity.pid = blocked_locks.pid
    JOIN pg_catalog.pg_locks AS blocking_locks ON blocking_locks.locktype = blocked_locks.locktype
        AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
        AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
        AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
        AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
        AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
        AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
        AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
        AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
        AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
        AND blocking_locks.pid != blocked_locks.pid
    JOIN pg_catalog.pg_stat_activity AS blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE
    NOT blocked_locks.granted;
-[ RECORD 1 ]------+----------------------------------------------
blocked_pid        | 23497
blocked_user       | postgres
blocked_duration   | 00:01:30.006623
blocking_pid       | 23492
blocking_user      | postgres
blocking_duration  | 00:03:49.484082
blocked_statement  | create index concurrently myidx on test1(id);
blocking_statement | select id from test for update;
-[ RECORD 2 ]------+----------------------------------------------
blocked_pid        | 23492
blocked_user       | postgres
blocked_duration   | 00:03:49.484082
blocking_pid       | 23490
blocking_user      | postgres
blocking_duration  | 00:04:45.580139
blocked_statement  | select id from test for update;
blocking_statement | select id from test for update;

可以看到这个索引，是INVALID的状态，在pg_index系统表中，是indisvalid是false。

postgres=# \d test1
               Table "public.test1"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 id     | integer |           |          | 
Indexes:
    "myidx" btree (id) INVALID
    
postgres=# select * from pg_index where indexrelid = 'myidx'::regclass;
-[ RECORD 1 ]--+------
indexrelid     | 43748
indrelid       | 43739
indnatts       | 1
indnkeyatts    | 1
indisunique    | f
indisprimary   | f
indisexclusion | f
indimmediate   | t
indisclustered | f
indisvalid     | f
indcheckxmin   | f
indisready     | t
indislive      | t
indisreplident | f
indkey         | 1
indcollation   | 0
indclass       | 1978
indoption      | 0
indexprs       | 
indpred        | 

那么看下堆栈，阻塞在哪里了，我们关注的是

•DefineIndex()，创建新的索引，•WaitForOlderSnapshots()，等待持有老快照的事务结束

[postgres@xiongcc ~]$ pstack 23497
#0  0x00007f1a6eccad23 in __epoll_wait_nocancel () from /lib64/libc.so.6
#1  0x00000000007923ca in WaitEventSetWait ()
#2  0x000000000079270e in WaitLatch ()
#3  0x00000000007acd4e in ProcSleep ()
#4  0x00000000007a0d92 in WaitOnLock ()
#5  0x00000000007a20b2 in LockAcquireExtended ()
#6  0x00000000007a524c in VirtualXactLock ()
#7  0x00000000005e567c in WaitForOlderSnapshots ()
#8  0x00000000005ea1b0 in DefineIndex ()
#9  0x00000000007bc537 in ProcessUtilitySlow.isra.1 ()
#10 0x00000000007bb481 in standard_ProcessUtility ()
#11 0x00007f1a6270d907 in multi_ProcessUtility () from /usr/pgsql-14/lib/citus.so
#12 0x00000000007b9c7a in PortalRunUtility ()
#13 0x00000000007b9d99 in PortalRunMulti ()
#14 0x00000000007ba1f9 in PortalRun ()
#15 0x00000000007b60b7 in exec_simple_query ()
#16 0x00000000007b7533 in PostgresMain ()
#17 0x000000000048d5f5 in ServerLoop ()
#18 0x0000000000734bb2 in PostmasterMain ()
#19 0x000000000048e712 in main ()

再看一下官网的说明

In a concurrent index build, the index is actually entered into the system catalogs in one transaction, then two table scans occur in two more transactions. Before each table scan, the index build must wait for existing transactions that have modified the table to terminate. After the second scan, the index build must wait for any transactions that have a snapshot (see Chapter 13) predating the second scan to terminate, including transactions used by any phase of concurrent index builds on other tables, if the indexes involved are partial or have columns that are not simple column references. Then finally the index can be marked ready for use, and the CREATE INDEX command terminates. Even then, however, the index may not be immediately usable for queries: in the worst case, it cannot be used as long as transactions exist that predate the start of the index build.

总结来说，流程就是

1.在系统表中插入索引的元数据，包括pg_class、pg_index，然后开启两个事务，进行两次扫描2.开启事务1，拿到当前snapshot1。3.扫描test1表前，等待所有修改过test1表（写入、删除、更新）的事务结束。4.扫描test1表，并建立索引。5.结束事务1。6.开启事务2，拿到当前snapshot2。7.再次扫描test1表前，等待所有修改过B表（写入、删除、更新）的事务结束。8.在snapshot2之后启动的事务对test1表执行的DML，会修改这个myidx的索引。9.再次扫描B表，更新索引。（从TUPLE中可以拿到版本号，在snapshot1到snapshot2之间变更的记录，将其合并到索引）10.上一步更新索引结束后，等待事务2之前开启的持有snapshot的事务结束。11.结束索引创建。索引可见。

我们这个场景卡在了第10步中，After the second scan, the index build must wait for any transactions that have a snapshot ，等待第二个会话结束。

/*
 * WaitForOlderSnapshots
 *
 * Wait for transactions that might have an older snapshot than the given xmin
 * limit, because it might not contain tuples deleted just before it has
 * been taken. Obtain a list of VXIDs of such transactions, and wait for them
 * individually. This is used when building an index concurrently.
...

具体细节可以参考这一篇源码分析《PostgreSQL create index concurrently原理分析》

第一阶段

将new_index的定义写入相关系统表，如pg_class、pg_index，在pg_index中把new_index的indisready标记（是否允许其它事务插入索引）、indisvalid标记（是否可以用来查询）设置为false。虽然此时new_index既不允许插入，也不可用，但是，其它事务见到new_index的定义之后，再对基表t进行修改时，必须保证HOT链满足new_index的定义。即，对某些更新而言，不能再使用HOT更新，而是需要产生新的HOT链。

获取一个session锁，防止第一个事务提交之后，表t或索引new_index被其它事务删除。

这个阶段需要处理的工作到这里就结束了，将第一个事务提交，开启一个新事务，开始第二阶段。

第二阶段

此时需要进行一段时间的等待，因为在开启第二阶段之前，必须保证整个系统中，new_index的定义对所有其它事务都可见。即，保证所有其它事务的HOT更新都满足new_index的定义。如何等待呢？等待其它正在写基表t的事务结束即可，PG使用函数WaitForLockers(heaplocktag, ShareLock, true)进行等待。

等待结束后，所有再需要打开基表t进行写入的事务，都可以看到new_index的定义，它们保证对基表t的HOT更新满足此索引的定义。

获取一个MVCC快照，对基表t进行一次全表扫描。把对此快照可见的所有元组插入new_index中，和不使用concurrently选项不同，使用MVCC快照并不索引recently-dead的元组。在这个阶段，其它事务对基表t进行写入时，并不维护索引（不向new_index中插入索引项），仅仅保证HOT更新满足new_index定义。

扫描结束后，将indisready标记为true，其它事务修改基表时，需要维护索引，但是indisvalid仍然为false（仍然不能用来查询）。

将第二个事务提交，开启第三个事务。

第三阶段

此时又需要进行一段时间的等待，因为，第三阶段开始后，必须保证后续其它事务都自己维护new_index。这里再次使用WaitForLockers(heaplocktag, ShareLock, true)进行等待。

等待结束后，所有其它事务都会自己维护new_index。我们只需要将在第二个阶段中其它事务对基表t的修改反应到索引上即可。

再获取一个新的MVCC快照，进行一次全表扫描，将索引中缺少的元组添加到索引中。添加数据的过程，类似merge join：将索引中heap的ctid找出来，排序；全表扫描，找出heap上可见元组的ctid；进行merge join。因为HOT的存在，这个merge join的过程有点复杂，详细过程参见validate_index_heapscan。

此时，又需要进行等待，这次等待之后，其它事务就可以用new_index进行查询了。这次等待的原因是：我们在第二、三阶段都没有将recently-dead的元组添加到索引中，如果某个事务持有一个很老的快照，这个事务使用new_index进行扫描时，就会少数据。因此，需要等待整个系统中持有太老快照的事务结束，这由WaitForOlderSnapshots(limitXmin, true)完成。

等待结束，将indisvalid标记为true，然后发送失效消息。索引就可以被后续的查询使用了。

在这个场景中，是这么一段话：我们在第二、三阶段都没有将recently-dead的元组添加到索引中，如果某个事务持有一个很老的快照，这个事务使用new_index进行扫描时，就会少数据。因此，需要等待整个系统中持有太老快照的事务结束，这由WaitForOlderSnapshots(limitXmin, true)完成。

源码中也有注解 because it might not contain tuples deleted just before it has been taken.，快照中可能没有包含所需的deleted tuples。

图解

可能到这里有些人已经懵了，包括我自己到这里的时候脑子也是一团浆糊，难以理解，我们再好好捋一下。

这个里面会涉及到HOT，也正是HOT的存在让create index concurrently变得更加复杂（后面以CIC缩写）

假设初始状态是这样的，有一个表tab，含有两列，在col1上面有一个索引，key = a

postgres=# \d tab 
                Table "public.tab"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 col1   | integer |           |          | 
 col2   | integer |           |          |

第一阶段

第一阶段，仅仅是插入系统表，pg_class、pg_index，不允许其他事务插入，因为创建了索引之后，在系统表中插入了，对于其他后端进程就可见了。但是对于正在运行的进程来说，是不知道的，所以得必须等待所有tab表上的事务完成，然后才能开始第二阶段。

第二阶段

假设这个时候，在col2上面新建另外一个索引。不用说，key = b1，因为对于这个事务来说，b1才是可见的（b不可见，HOT链之前的数据）。所以，此时这个新索引的版本是(a, b1, c1)，但是这个时候，indisready还是false，是不允许插入的，所以这个时候，假如有事务做了更新，首先做了HOT更新，变成了(a, b1, c2)，然后又做了一个非HOT更新，那么会插入一条新的索引项，(a, b2, c3)，对于第一个索引是OK的，但是对于第二个索引来说，里面还没有b2，假如此时按照HOT链来查找的话就出问题了，所以需要第三阶段来进一步处理。

第二阶段扫描结束后，会将pg_index.indisready标记为true，其它事务修改基表时，是需要维护索引的，但是indisvalid仍然为false（仍然不能用来查询）。至此第二阶段结束。

第三阶段

第三阶段，再次获取一次快照，插入对新快照可见但尚未在索引中的行，由于第二阶段结束之后，其他事务会维护索引了，所以我们处理一下第二阶段丢失的行即可。就变成了下面这个样子：

所以，这个时候索引基本就差不多了。其他后端进程也负责维护HOT。

但是，假设第二阶段或第三阶段存在某个事务较老，他是可以看到 (a, b, c)的话 ，那么新索引就出问题了，索引里压根没有b，所以，CIC还要等待所有这些类似的老事务结束。

危害

分析清楚三阶段之后，那么CIC的危害就呼之欲出了。会留下无效的索引！并且会继续影响插入，会占据大小。假如还是唯一索引的话，唯一性约束也会继续存在。表达式索引和部分索引也存在同样类似的行为。

postgres=# drop index myidx ;
DROP INDEX
postgres=# create unique index concurrently myidx on test1(id);
^CCancel request sent
ERROR:  canceling statement due to user request
postgres=# \d+ test1
                                          Table "public.test1"
 Column |  Type   | Collation | Nullable | Default | Storage | Compression | Stats target | Description 
--------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
 id     | integer |           |          |         | plain   |             |              | 
Indexes:
    "myidx" UNIQUE, btree (id) INVALID
Access method: heap

postgres=# select id from test1 where id = 1;
 id 
----
  1
(1 row)

postgres=# insert into test1 values(1);
ERROR:  duplicate key value violates unique constraint "myidx"
DETAIL:  Key (id)=(1) already exists.

还有一种糟糕的情况是，在创建的过程中，更改了数据，会话1

postgres=# create unique index concurrently myidx on test1(id);
---慢吞吞地创建中

第二个会话更改了数据

postgres=# update test1 set id = 99 where id = 9999999;
UPDATE 1

第一个会话就会报错了，因为违反了唯一性约束

postgres=# create unique index concurrently myidx on test1(id);
ERROR:  duplicate key value violates unique constraint "myidx"
DETAIL:  Key (id)=(99) already exists.

并且，这个索引还会继续保持

postgres=# select relpages from pg_class where relname = 'myidx';
 relpages 
----------
    27422
(1 row)

postgres=# insert into test1 values(generate_series(10000001,20000000));
INSERT 0 10000000
postgres=# analyze test1;
ANALYZE
postgres=# select relpages from pg_class where relname = 'myidx';
 relpages 
----------
   157182
(1 row)

后话

与CIC类似的，就是reindex concurrently了，以及分区表做reindex concurrently。

后面再找时间分析。不过可以看看源码的注释，挺清晰的。

/*
 * ReindexRelationConcurrently - process REINDEX CONCURRENTLY for given
 * relation OID
 *
 * 'relationOid' can either belong to an index, a table or a materialized
 * view.  For tables and materialized views, all its indexes will be rebuilt,
 * excluding invalid indexes and any indexes used in exclusion constraints,
 * but including its associated toast table indexes.  For indexes, the index
 * itself will be rebuilt.
 *
 * The locks taken on parent tables and involved indexes are kept until the
 * transaction is committed, at which point a session lock is taken on each
 * relation.  Both of these protect against concurrent schema changes.
 *
 * Returns true if any indexes have been rebuilt (including toast table's
 * indexes, when relevant), otherwise returns false.
 *
 * NOTE: This cannot be used on temporary relations.  A concurrent build would
 * cause issues with ON COMMIT actions triggered by the transactions of the
 * concurrent build.  Temporary relations are not subject to concurrent
 * concerns, so there's no need for the more complicated concurrent build,
 * anyway, and a non-concurrent reindex is more efficient.
 */
static bool
ReindexRelationConcurrently(Oid relationOid, ReindexParams *params)
{

...
    /*-----
     * 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.
     */
...

小结

现在对于create index concurrently的原理和利害应该就清楚了吧。虽然不阻塞DML，但是也有自己的弊端，同时由于ShareUpdateExclusiveLock是自斥的，是不允许同时执行create index concurrently的，这个和create index不同，同时也不能放在事务中。

引用一下德哥的

•注意事项，为了减少等待的时间，需要1. 尽量避免创建索引过程中，两次SCAN之前对被创建索引表实施长事务，并且长事务中包含修改被创建索引的表。2. 在第二次SCAN前，尽量避免开启长事务。•最后的一次等待，应该还有改进的空间，减少等待。实际上最后一次等待是为了防止那些还存在的事务，在事务中可能查询B表，如果结束的话，这个索引实际上是SNAPSHOT2后的状态（可能有一些对snapshot2前的事务STALE的状态）。•注意，因为第一次扫描并建立中间状态的索引（INVALID）后，索引实际上就对后面的DML起作用了，所以如果是在第二SCAN阶段，索引创建失败了，这个索引会一直影响DML（性能、约束）。

另外需要注意的是

Concurrent builds for indexes on partitioned tables are currently not supported. However, you may concurrently build the index on each partition individually and then finally create the partitioned index non-concurrently in order to reduce the time where writes to the partitioned table will be locked out. In this case, building the partitioned index is a metadata only operation.

当前不支持在分区表上并发生成索引。然而，你可以在每个分区上单独的并发构建索引，然后最终以非并发的方式创建分区索引，以减少对分区表的写入被锁定的时间。在这种情况下，生成分区索引仅是元数据操作。不过，在14里面，reindex倒是实现了自动重建所有子分区的索引，方便了分区表的管理。