前言

今天中午在吃饭的时候，同事火急火燎地打电话过来，创建索引巨慢，又不知道进度如何，只能干瞪眼。

visualizing

假如你的版本是10以前的，那么很不幸，创建索引的过程完全透明，对于大表来说十分痛苦，吭哧吭哧完全不知道到了哪一步。

好在12版本里面，新增了一个pg_stat_progress_create_index视图，可以监控索引的创建进度。同时官方也给出了phase的详细说明

列	类型	描述
pid	integer	进程pid
datid	oid	数据库oid
datname	name	数据库名
relid	oid	表的oid
index_relid	oid	待创建的索引的oid，假如不是concurrently创建的话，则是0
command	text	执行的命令，包括create index、create index concurrently、reindex或者reindex concurrently
phase	text	当前创建索引所处的阶段
lockers_total	bigint	当前正在等待的锁数量
lockers_done	bigint	已经获取的锁数量
current_locker_pid	bigint	当前正在等待的进程pid
blocks_total	bigint	当前阶段要处理的数据块总数
blocks_done	bigint	当前阶段已经处理的数据块数量
tuples_total	bigint	当前阶段要处理的元组总数
tuples_done	bigint	当前阶段已经处理的元组数量
partitions_total	bigint	在分区表上创建索引时，该列被设置为要在其上创建索引的分区总数
partitions_done	bigint	当在分区表上创建索引时，该列被设置为在其上完成索引创建的分区数

相信读者已经晕乎，一大串英文，完全不知所以然的解释，简直要疯了！怎么办？莫慌，容笔者一层一层来揭开迷雾。

normal create index

先看看常规建索引的流程，非CIC

postgres=# select pg_size_pretty(pg_relation_size('test_big2'));
 pg_size_pretty 
----------------
 5744 MB
(1 row)

postgres=# select reltuples,relpages from pg_class where relname = 'test_big2';
 reltuples | relpages 
-----------+----------
     1e+08 |   735275
(1 row)

postgres=# create index myidx on test_big2(id);
---创建索引中

新开一个窗口观察进度

SELECT
    now(),
    query_start AS started_at,
    now() - query_start AS query_duration,
    format('[%s] %s', a.pid, a.query) AS pid_and_query,
    index_relid::regclass AS index_name,
    relid::regclass AS table_name,
    (pg_size_pretty(pg_relation_size(relid))) AS table_size,
    phase,
    nullif (wait_event_type, '') || ': ' || wait_event AS wait_type_and_event,
    current_locker_pid,
    (
        SELECT
            nullif (
            LEFT (query, 150), '') || '...'
        FROM
            pg_stat_activity a
        WHERE
            a.pid = current_locker_pid) AS current_locker_query,
    format('%s (%s of %s)', coalesce((round(100 * lockers_done::numeric / nullif (lockers_total, 0), 2))::text || '%', 'N/A'), coalesce(lockers_done::text, '?'), coalesce(lockers_total::text, '?')) AS lockers_progress,
    format('%s (%s of %s)', coalesce((round(100 * blocks_done::numeric / nullif (blocks_total, 0), 2))::text || '%', 'N/A'), coalesce(blocks_done::text, '?'), coalesce(blocks_total::text, '?')) AS blocks_progress,
    format('%s (%s of %s)', coalesce((round(100 * tuples_done::numeric / nullif (tuples_total, 0), 2))::text || '%', 'N/A'), coalesce(tuples_done::text, '?'), coalesce(tuples_total::text, '?')) AS tuples_progress,
    format('%s (%s of %s)', coalesce((round(100 * partitions_done::numeric / nullif (partitions_total, 0), 2))::text || '%', 'N/A'), coalesce(partitions_done::text, '?'), coalesce(partitions_total::text, '?')) AS partitions_progress
FROM
    pg_stat_progress_create_index p
    LEFT JOIN pg_stat_activity a ON a.pid = p.pid;

也可以使用如下SQL

SELECT
    S.pid AS pid, S.datid AS datid, D.datname AS datname,
    S.relid AS relid,
    CAST(S.param7 AS oid) AS index_relid,
    CASE S.param1 WHEN 1 THEN 'CREATE INDEX'
                  WHEN 2 THEN 'CREATE INDEX CONCURRENTLY'
                  WHEN 3 THEN 'REINDEX'
                  WHEN 4 THEN 'REINDEX CONCURRENTLY'
                  END AS command,
    CASE S.param10 WHEN 0 THEN 'initializing'
                   WHEN 1 THEN 'waiting for writers before build'
                   WHEN 2 THEN 'building index' ||
                       COALESCE((': ' || pg_indexam_progress_phasename(S.param9::oid, S.param11)),
                                '')
                   WHEN 3 THEN 'waiting for writers before validation'
                   WHEN 4 THEN 'index validation: scanning index'
                   WHEN 5 THEN 'index validation: sorting tuples'
                   WHEN 6 THEN 'index validation: scanning table'
                   WHEN 7 THEN 'waiting for old snapshots'
                   WHEN 8 THEN 'waiting for readers before marking dead'
                   WHEN 9 THEN 'waiting for readers before dropping'
                   END as phase,
    S.param4 AS lockers_total,
    S.param5 AS lockers_done,
    S.param6 AS current_locker_pid,
    S.param16 AS blocks_total,
    S.param17 AS blocks_done,
    S.param12 AS tuples_total,
    S.param13 AS tuples_done,
    S.param14 AS partitions_total,
    S.param15 AS partitions_done
FROM pg_stat_get_progress_info('CREATE INDEX') AS S
    LEFT JOIN pg_database D ON S.datid = D.oid;

scanning table

building index: scanning table，不难理解，创建索引前，需要先将所有可见的元组先扫描一遍

-[ RECORD 1 ]--------+---------------------------------------------
now                  | 2022-03-31 16:18:26.278006+08
started_at           | 2022-03-31 16:18:25.128698+08
query_duration       | 00:00:01.149308
pid_and_query        | [11209] create index myidx on test_big2(id);
index_name           | -
table_name           | test_big2
table_size           | 5744 MB
phase                | building index: scanning table
wait_type_and_event  | IO: DataFileRead
current_locker_pid   | 0
current_locker_query | 
lockers_progress     | N/A (0 of 0)
blocks_progress      | 2.12% (15592 of 735275)
tuples_progress      | N/A (0 of 0)
partitions_progress  | N/A (0 of 0)

...
...

-[ RECORD 1 ]--------+---------------------------------------------
now                  | 2022-03-31 16:19:26.753023+08
started_at           | 2022-03-31 16:18:25.128698+08
query_duration       | 00:01:01.624325
pid_and_query        | [11209] create index myidx on test_big2(id);
index_name           | -
table_name           | test_big2
table_size           | 5744 MB
phase                | building index: scanning table
wait_type_and_event  | 
current_locker_pid   | 0
current_locker_query | 
lockers_progress     | N/A (0 of 0)
blocks_progress      | 99.23% (729596 of 735275)
tuples_progress      | N/A (0 of 0)
partitions_progress  | N/A (0 of 0)

sorting live tuples

building index: sorting live tuples，因为创建的是Btree索引，需要先对元组进行排序

-[ RECORD 1 ]--------+---------------------------------------------
now                  | 2022-03-31 16:19:28.758618+08
started_at           | 2022-03-31 16:18:25.128698+08
query_duration       | 00:01:03.62992
pid_and_query        | [11209] create index myidx on test_big2(id);
index_name           | -
table_name           | test_big2
table_size           | 5744 MB
phase                | building index: sorting live tuples
wait_type_and_event  | IO: BufFileRead
current_locker_pid   | 0
current_locker_query | 
lockers_progress     | N/A (0 of 0)
blocks_progress      | 100.00% (735275 of 735275)
tuples_progress      | N/A (0 of 0)
partitions_progress  | N/A (0 of 0)

...
...

-[ RECORD 1 ]--------+---------------------------------------------
now                  | 2022-03-31 16:19:55.00105+08
started_at           | 2022-03-31 16:18:25.128698+08
query_duration       | 00:01:29.872352
pid_and_query        | [11209] create index myidx on test_big2(id);
index_name           | -
table_name           | test_big2
table_size           | 5744 MB
phase                | building index: sorting live tuples
wait_type_and_event  | IO: BufFileWrite
current_locker_pid   | 0
current_locker_query | 
lockers_progress     | N/A (0 of 0)
blocks_progress      | 100.00% (735275 of 735275)
tuples_progress      | N/A (0 of 0)
partitions_progress  | N/A (0 of 0)

大概持续了25s，SQL的输出内容没有任何变化，这一步骤比较透明

loading tuples in tree

building index: loading tuples in tree，这一步就是在填充索引树了。

-[ RECORD 1 ]--------+---------------------------------------------
now                  | 2022-03-31 16:19:57.018012+08
started_at           | 2022-03-31 16:18:25.128698+08
query_duration       | 00:01:31.889314
pid_and_query        | [11209] create index myidx on test_big2(id);
index_name           | -
table_name           | test_big2
table_size           | 5744 MB
phase                | building index: loading tuples in tree
wait_type_and_event  | 
current_locker_pid   | 0
current_locker_query | 
lockers_progress     | N/A (0 of 0)
blocks_progress      | N/A (0 of 0)
tuples_progress      | 2.64% (2642014 of 100000000)
partitions_progress  | N/A (0 of 0)

...
...

-[ RECORD 1 ]--------+---------------------------------------------
now                  | 2022-03-31 16:21:15.708056+08
started_at           | 2022-03-31 16:18:25.128698+08
query_duration       | 00:02:50.579358
pid_and_query        | [11209] create index myidx on test_big2(id);
index_name           | -
table_name           | test_big2
table_size           | 5744 MB
phase                | building index: loading tuples in tree
wait_type_and_event  | 
current_locker_pid   | 0
current_locker_query | 
lockers_progress     | N/A (0 of 0)
blocks_progress      | N/A (0 of 0)
tuples_progress      | 100.00% (100000000 of 100000000)
partitions_progress  | N/A (0 of 0)

当进度达到100%了之后，索引也创建完成了。

postgres=# create index myidx on test_big2(id);
CREATE INDEX
postgres=# \d test_big2 
                                Table "public.test_big2"
   Column    |              Type              | Collation | Nullable |      Default      
-------------+--------------------------------+-----------+----------+-------------------
 id          | integer                        |           |          | 
 name        | character varying(64)          |           |          | 
 create_time | timestamp(0) without time zone |           |          | clock_timestamp()
Indexes:
    "myidx" btree (id)

可以看到，常规的建索引 (即create index)，流程还是十分清晰。流程中涉及到了元组排序，这一步骤会受到maintenance_work_mem的影响，maintenance_work_mem用于控制在创建索引时能使用的最大内存量。对于Btree，必须对数据进行排序，如果要排序的数据在maintenance_work_mem中放不下，就会溢出到磁盘中。所以在create index之前，可以会话级增大maintenance_work_mem。

假如是HASH索引的话，visualizing就要差一些了，全程phase都是building index，其他索引如GIN、GIST自行实验。

-[ RECORD 1 ]--------+--------------------------------------------------------
now                  | 2022-03-31 20:41:27.891049+08
started_at           | 2022-03-31 20:39:19.049117+08
query_duration       | 00:02:08.841932
pid_and_query        | [15967] create index myidx on test_big2 using hash(id);
index_name           | -
table_name           | test_big2
table_size           | 5744 MB
phase                | building index
wait_type_and_event  | 
current_locker_pid   | 0
current_locker_query | 
lockers_progress     | N/A (0 of 0)
blocks_progress      | 67.01% (492677 of 735275)
tuples_progress      | N/A (0 of 0)
partitions_progress  | N/A (0 of 0)

...
...

-[ RECORD 1 ]--------+--------------------------------------------------------
now                  | 2022-03-31 20:42:24.260003+08
started_at           | 2022-03-31 20:39:19.049117+08
query_duration       | 00:03:05.210886
pid_and_query        | [15967] create index myidx on test_big2 using hash(id);
index_name           | -
table_name           | test_big2
table_size           | 5744 MB
phase                | building index
wait_type_and_event  | 
current_locker_pid   | 0
current_locker_query | 
lockers_progress     | N/A (0 of 0)
blocks_progress      | 100.00% (735275 of 735275)
tuples_progress      | 3.08% (3076938 of 100000000)
partitions_progress  | N/A (0 of 0)

哈希索引的结构如下所示

• Meta page — 第0页，包含关于索引内内容的信息。
• Bucket pages — 索引页，存储 Hash code 和 TID
• Overflow pages — 结构与Bucket pages相同，当一个页不足以容纳一个桶时使用。
• Bitmap pages — which keep track of overflow pages that are currently clear and can be reused for other buckets.

索引每增加一次，PostgreSQL就会立即创建两倍于上次创建的桶(也就是页面)。为了避免一次性分配大量的页面，版本10使大小的增加更加平滑。至于Overflow pages，它们在需要时就被分配，并在位图页面中进行跟踪，位图页面也在需要时被分配。

注意，哈希索引不能减少大小。如果我们删除一些已索引的行，那么一旦分配了页，就不会返回给操作系统，而只会在vacuum后被新数据重用。减少索引大小的唯一选择是使用REINDEX或VACUUM FULL命令从头重新创建。

create index concurrently

大多数生产中使用的都是CIC，即create index concurrently，可以不阻塞写入创建索引，CIC本身十分复杂，需要两次以上的扫描。

按照官网的说明，的确有不少阶段是针对CIC的，在开始分析前，我们需要简单回顾一下CIC的原理

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. 扫描test表前，等待所有修改过test表（写入、删除、更新）的事务结束。
4. 扫描test表，并建立索引。
5. 结束事务1。
6. 开启事务2，拿到当前snapshot2。
7. 再次扫描test表前，等待所有修改过test表（写入、删除、更新）的事务结束。
8. 在snapshot2之后启动的事务对test表执行的DML，会修改这个myidx的索引。
9. 再次扫描test表，更新索引。（从TUPLE中可以拿到版本号，在snapshot1到snapshot2之间变更的记录，将其合并到索引）
10. 上一步更新索引结束后，等待事务2之前开启的持有snapshot的事务结束。
11. 结束索引创建。索引可见。

记住这个流程，后面我们需要反复回到这一点上来！

waiting for writers before build

waiting for writers before build：CREATE INDEX CONCURRENTLY or REINDEX CONCURRENTLY is waiting for transactions with write locks that can potentially see the table to finish. This phase is skipped when not in concurrent mode. Columns lockers_total, lockers_done and current_locker_pid contain the progress information for this phase.

这个阶段表示等待持有写锁的还能看到该表的事务结束。也就是上面流程的第三步，扫描test表前，等待所有修改过test表（写入、删除、更新）的事务结束。

那么很好模拟，在执行CIC之前，对表进行写入即可

postgres=# begin;
BEGIN
postgres=*# select pg_backend_pid(),txid_current();
 pg_backend_pid | txid_current 
----------------+--------------
          16360 |         1033
(1 row)

postgres=*# select id from test_big2 limit 1;      
    id    
----------
 14127219
(1 row)

postgres=*# insert into test_big2 values(1);
INSERT 0 1

然后开启事务创建索引

postgres=# create index CONCURRENTLY myidx on test_big2(id);
---创建索引中

可以看到，在等待获取锁，SQL便是insert into test_big2 values(1)

-[ RECORD 1 ]--------+----------------------------------------------------------
now                  | 2022-03-31 21:03:42.083372+08
started_at           | 2022-03-31 21:02:04.166131+08
query_duration       | 00:01:37.917241
pid_and_query        | [16653] create index CONCURRENTLY myidx on test_big2(id);
index_name           | myidx
table_name           | test_big2
table_size           | 5744 MB
phase                | waiting for writers before build
wait_type_and_event  | Lock: virtualxid
current_locker_pid   | 16360
current_locker_query | insert into test_big2 values(1);...
lockers_progress     | 0.00% (0 of 1)
blocks_progress      | N/A (0 of 0)
tuples_progress      | N/A (0 of 0)
partitions_progress  | N/A (0 of 0)

building index

回滚写入事务之后，便是进入了正常的创建索引building index的流程，分为scanning table、sorting live tuples和loading tuples in tree。

postgres=*# insert into test_big2 values(1);
INSERT 0 1
postgres=*# rollback ;
ROLLBACK

scanning table

-[ RECORD 1 ]--------+----------------------------------------------------------
now                  | 2022-03-31 22:25:39.980006+08
started_at           | 2022-03-31 22:25:24.462161+08
query_duration       | 00:00:15.517845
pid_and_query        | [18708] create index CONCURRENTLY myidx on test_big2(id);
index_name           | myidx
table_name           | test_big2
table_size           | 6815 MB
phase                | building index: scanning table
wait_type_and_event  | 
current_locker_pid   | 0
current_locker_query | 
lockers_progress     | N/A (0 of 0)
blocks_progress      | 2.13% (18570 of 872299)
tuples_progress      | N/A (0 of 0)
partitions_progress  | N/A (0 of 0)

...
...

-[ RECORD 1 ]--------+----------------------------------------------------------
now                  | 2022-03-31 22:26:42.370531+08
started_at           | 2022-03-31 22:25:24.462161+08
query_duration       | 00:01:17.90837
pid_and_query        | [18708] create index CONCURRENTLY myidx on test_big2(id);
index_name           | myidx
table_name           | test_big2
table_size           | 6815 MB
phase                | building index: scanning table
wait_type_and_event  | IO: DataFileRead
current_locker_pid   | 0
current_locker_query | 
lockers_progress     | N/A (0 of 0)
blocks_progress      | 84.29% (735274 of 872299)
tuples_progress      | N/A (0 of 0)
partitions_progress  | N/A (0 of 0)

sorting live tuples

-[ RECORD 1 ]--------+----------------------------------------------------------
now                  | 2022-03-31 22:26:44.382344+08
started_at           | 2022-03-31 22:25:24.462161+08
query_duration       | 00:01:19.920183
pid_and_query        | [18708] create index CONCURRENTLY myidx on test_big2(id);
index_name           | myidx
table_name           | test_big2
table_size           | 6815 MB
phase                | building index: sorting live tuples
wait_type_and_event  | IO: BufFileRead
current_locker_pid   | 0
current_locker_query | 
lockers_progress     | N/A (0 of 0)
blocks_progress      | 100.00% (872299 of 872299)
tuples_progress      | N/A (0 of 0)
partitions_progress  | N/A (0 of 0)

...
...

-[ RECORD 1 ]--------+----------------------------------------------------------
now                  | 2022-03-31 22:27:14.577956+08
started_at           | 2022-03-31 22:25:24.462161+08
query_duration       | 00:01:50.115795
pid_and_query        | [18708] create index CONCURRENTLY myidx on test_big2(id);
index_name           | myidx
table_name           | test_big2
table_size           | 6815 MB
phase                | building index: sorting live tuples
wait_type_and_event  | IO: BufFileRead
current_locker_pid   | 0
current_locker_query | 
lockers_progress     | N/A (0 of 0)
blocks_progress      | N/A (0 of 0)
tuples_progress      | 0.00% (0 of 100000000)
partitions_progress  | N/A (0 of 0)

loading tuples in tree

-[ RECORD 1 ]--------+----------------------------------------------------------
now                  | 2022-03-31 22:27:16.58802+08
started_at           | 2022-03-31 22:25:24.462161+08
query_duration       | 00:01:52.125859
pid_and_query        | [18708] create index CONCURRENTLY myidx on test_big2(id);
index_name           | myidx
table_name           | test_big2
table_size           | 6815 MB
phase                | building index: loading tuples in tree
wait_type_and_event  | 
current_locker_pid   | 0
current_locker_query | 
lockers_progress     | N/A (0 of 0)
blocks_progress      | N/A (0 of 0)
tuples_progress      | 3.86% (3856192 of 100000000)
partitions_progress  | N/A (0 of 0)

接下来，索引经过校验和扫描就完成了。分为index validation: scanning index和index validation: scanning table，这几个步骤主要在做收尾工作，比如合并索引，因为CIC涉及到了多次快照扫描，因此需要将两次快照间索引中缺少的元组添加到索引中（见下文）。添加数据的过程，类似merge join：将索引中heap的ctid找出来，排序；全表扫描，找出heap上可见元组的ctid；进行merge join。

-[ RECORD 1 ]--------+----------------------------------------------------------
now                  | 2022-03-31 22:28:00.838007+08
started_at           | 2022-03-31 22:25:24.462161+08
query_duration       | 00:02:36.375846
pid_and_query        | [18708] create index CONCURRENTLY myidx on test_big2(id);
index_name           | myidx
table_name           | test_big2
table_size           | 6815 MB
phase                | index validation: scanning index
wait_type_and_event  | 
current_locker_pid   | 0
current_locker_query | 
lockers_progress     | N/A (0 of 0)
blocks_progress      | 5.59% (13824 of 247180)
tuples_progress      | N/A (0 of 0)
partitions_progress  | N/A (0 of 0)

...
...

-[ RECORD 1 ]--------+----------------------------------------------------------
now                  | 2022-03-31 22:28:39.056016+08
started_at           | 2022-03-31 22:25:24.462161+08
query_duration       | 00:03:14.593855
pid_and_query        | [18708] create index CONCURRENTLY myidx on test_big2(id);
index_name           | myidx
table_name           | test_big2
table_size           | 6815 MB
phase                | index validation: scanning index
wait_type_and_event  | 
current_locker_pid   | 0
current_locker_query | 
lockers_progress     | N/A (0 of 0)
blocks_progress      | 97.87% (241904 of 247180)
tuples_progress      | N/A (0 of 0)
partitions_progress  | N/A (0 of 0)

Thu 31 Mar 2022 10:28:41 PM CST (every 2s)

-[ RECORD 1 ]--------+----------------------------------------------------------
now                  | 2022-03-31 22:28:41.071013+08
started_at           | 2022-03-31 22:25:24.462161+08
query_duration       | 00:03:16.608852
pid_and_query        | [18708] create index CONCURRENTLY myidx on test_big2(id);
index_name           | myidx
table_name           | test_big2
table_size           | 6815 MB
phase                | index validation: scanning table
wait_type_and_event  | 
current_locker_pid   | 0
current_locker_query | 
lockers_progress     | N/A (0 of 0)
blocks_progress      | 1.28% (11177 of 872299)
tuples_progress      | N/A (0 of 0)
partitions_progress  | N/A (0 of 0)

...
...

-[ RECORD 1 ]--------+----------------------------------------------------------
now                  | 2022-03-31 22:29:35.386138+08
started_at           | 2022-03-31 22:25:24.462161+08
query_duration       | 00:04:10.923977
pid_and_query        | [18708] create index CONCURRENTLY myidx on test_big2(id);
index_name           | myidx
table_name           | test_big2
table_size           | 6815 MB
phase                | index validation: scanning table
wait_type_and_event  | IO: DataFileRead
current_locker_pid   | 0
current_locker_query | 
lockers_progress     | N/A (0 of 0)
blocks_progress      | 84.29% (735274 of 872299)
tuples_progress      | N/A (0 of 0)
partitions_progress  | N/A (0 of 0)

waiting for writers before validation

接上文，细心的读者可能发现了，还没有出现waiting for writers before validation，那么waiting for writers before validation这个阶段在什么时候会出现呢？让我们再次回到CIC的原理中来，不难发现，也就是第7步，再次扫描test表前，需要等待所有修改过test表（写入、删除、更新）的事务结束。

那么在创建索引的过程中，新开一个会话插入数据即可模拟

postgres=# drop index myidx ;
DROP INDEX
postgres=# create index CONCURRENTLY myidx on test_big2(id);
---创建索引中

新开会话插入数据（尽可能多插入点，让loading tuples in tree结束之后还在插入）

postgres=# insert into test_big2(id,name) select n, n||'_vacuum' from generate_series(1,50000000) n;
---插入中

观察状态，可以看到，处于waiting for writers before validation，在等待插入结束。回到原理

1. 再次扫描test表前，等待所有修改过test表（写入、删除、更新）的事务结束。
2. 在snapshot2之后启动的事务对test表执行的DML，会直接修改这个myidx的索引。
3. 再次扫描test表，更新索引。（从TUPLE中可以拿到版本号，在snapshot1到snapshot2之间变更的记录，将其合并到索引）

-[ RECORD 1 ]--------+----------------------------------------------------------
now                  | 2022-03-31 22:39:15.916018+08
started_at           | 2022-03-31 22:36:00.395309+08
query_duration       | 00:03:15.520709
pid_and_query        | [18708] create index CONCURRENTLY myidx on test_big2(id);
index_name           | myidx
table_name           | test_big2
table_size           | 8046 MB
phase                | building index: loading tuples in tree
wait_type_and_event  | IO: DataFileImmediateSync
current_locker_pid   | 0
current_locker_query | 
lockers_progress     | N/A (0 of 0)
blocks_progress      | N/A (0 of 0)
tuples_progress      | 100.00% (100000000 of 100000000)
partitions_progress  | N/A (0 of 0)

Thu 31 Mar 2022 10:39:17 PM CST (every 2s)

-[ RECORD 1 ]--------+---------------------------------------------------------------------------------------------
now                  | 2022-03-31 22:39:17.927012+08
started_at           | 2022-03-31 22:36:00.395309+08
query_duration       | 00:03:17.531703
pid_and_query        | [18708] create index CONCURRENTLY myidx on test_big2(id);
index_name           | myidx
table_name           | test_big2
table_size           | 8081 MB
phase                | waiting for writers before validation
wait_type_and_event  | Lock: virtualxid
current_locker_pid   | 18545
current_locker_query | insert into test_big2(id,name) select n, n||'_vacuum' from generate_series(1,50000000) n;...
lockers_progress     | 0.00% (0 of 1)
blocks_progress      | N/A (0 of 0)
tuples_progress      | 100.00% (100000000 of 100000000)

手动CTRL + C结束之后，便又继续正常的步骤了index validation: scanning index和index validation: scanning table

-[ RECORD 1 ]--------+----------------------------------------------------------
now                  | 2022-03-31 22:40:32.885217+08
started_at           | 2022-03-31 22:36:00.395309+08
query_duration       | 00:04:32.489908
pid_and_query        | [18708] create index CONCURRENTLY myidx on test_big2(id);
index_name           | myidx
table_name           | test_big2
table_size           | 9301 MB
phase                | index validation: scanning index
wait_type_and_event  | 
current_locker_pid   | 0
current_locker_query | 
lockers_progress     | N/A (0 of 0)
blocks_progress      | 5.59% (13824 of 247180)
tuples_progress      | N/A (0 of 0)
partitions_progress  | N/A (0 of 0)

waiting for old snapshots

最后还剩下waiting for old snapshots了，那么这个阶段又是什么时候会出现呢？又双叒叕回到CIC的原理，按照说明，最极端的情况下，需要等待索引创建之前事务结束，索引才能使用

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.

也就是第10步

1. 上一步更新索引结束后，等待事务2之前开启的持有snapshot的事务结束。
2. 结束索引创建。索引可见。

看个例子

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=# drop index myidx ;
DROP INDEX
postgres=# create index CONCURRENTLY myidx on test_big2(id);
---创建索引过程中

前面的流程和正常的CIC一样，跳过，直接到最后

-[ RECORD 1 ]--------+----------------------------------------------------------
now                  | 2022-03-31 22:53:29.035191+08
started_at           | 2022-03-31 22:49:20.267304+08
query_duration       | 00:04:08.767887
pid_and_query        | [18708] create index CONCURRENTLY myidx on test_big2(id);
index_name           | myidx
table_name           | test_big2
table_size           | 8328 MB
phase                | index validation: scanning table
wait_type_and_event  | IO: DataFileRead
current_locker_pid   | 0
current_locker_query | 
lockers_progress     | N/A (0 of 0)
blocks_progress      | 68.98% (735274 of 1065920)
tuples_progress      | N/A (0 of 0)
partitions_progress  | N/A (0 of 0)

Thu 31 Mar 2022 10:53:31 PM CST (every 2s)

-[ RECORD 1 ]--------+----------------------------------------------------------
now                  | 2022-03-31 22:53:31.040815+08
started_at           | 2022-03-31 22:49:20.267304+08
query_duration       | 00:04:10.773511
pid_and_query        | [18708] create index CONCURRENTLY myidx on test_big2(id);
index_name           | myidx
table_name           | test_big2
table_size           | 8328 MB
phase                | waiting for old snapshots
wait_type_and_event  | Lock: virtualxid
current_locker_pid   | 18545
current_locker_query | select id from test for update;...
lockers_progress     | 0.00% (0 of 1)
blocks_progress      | 68.98% (735274 of 1065920)
tuples_progress      | N/A (0 of 0)
partitions_progress  | N/A (0 of 0)

可以看到，waiting for old snapshots，在等待select id from test for update;，并且是两个完全不同的对象，一个是test表，一个是test_big2表，和我们日常认知大相径庭！

至于为何会这样，主要和HOT机制有关。假设初始状态是这样的，有一个表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其实隐藏了很多的知识在里面，总而言之，小心长事务！虽说12以后引入的pg_stat_progress_create_index极大地方便了我们，不过其中的某些阶段十分费解，结合这一篇的内容，相信各位应该能够理解了。

至于剩下的文中没有提及的两个阶段waiting for readers before marking dead和waiting for readers before dropping是关于reindex的，由于这一期的内容我想太过于干货，不好消化，所以我准备放到下一期来写，拭目以待。

ITPub博客

创建索引的各个阶段，你真的搞懂了吗

前言

visualizing

normal create index

scanning table

sorting live tuples

loading tuples in tree

create index concurrently

waiting for writers before build

building index

scanning table

sorting live tuples

loading tuples in tree

waiting for writers before validation

waiting for old snapshots

第一阶段

第二阶段

第三阶段

小结

最新文章