postgreSQL 并行创建索引(CONCURRENTLY)

       通常 PostgreSQL会给要创建索引的表加锁，让它不能被写入(update|delete|insert)， 并且用该表上的一次扫描来执行整个索引的创建。其他事务仍然可以读取表 (select)， 但是如果它们尝试在该表上进行插入、更新或者删除，它们会被阻塞直到索引创建完成。

       为了解决PostgreSQL创建索引不阻塞其他DML事务的问题，通过 指定CREATE INDEX的CONCURRENTLY选项实现。当使用这个选项时，PostgreSQL必须执行该表的 两次扫描，此外它必须等待所有现有可能会修改或者使用该索引的事务终止。

       对于临时表，CREATE INDEX始终是非并发的，因为没有其他会话可以访问它们，并且创建非并发索引的成本更低。

       如果在创建索引的过程中发生了意外或异常终止，这个未被创建成功的索引不会自动撤销，而是保留在系统中，被标记为INVALID状态，需要手工处理。\d  tabname可以查看索引的是否处于INVALID状态。

      如果遇到INVALID状态的索引，有两个处理办法：

     a>  drop index idx_name;create index concurrently;

     b>  reindex index concurrently

创建表、造数、创建索引示例：

postgres=# create table test_idx(id serial primary key, note text);
postgres=# insert into test_idx(note) select generate_series(1,5000000);
postgres=# create index idx_test_idx on test_idx(note);
postgres=# create index concurrently idx_test_idx2 on test_idx(note);

查看索引的大小：
postgres=# select pg_size_pretty(pg_relation_size('idx_test_idx')),pg_size_pretty(pg_relation_size('idx_test_idx2'));

查看表的基本信息：
ostgres=# \d test_idx
                            Table "public.test_idx"
 Column |  Type   | Collation | Nullable |               Default
--------+---------+-----------+----------+--------------------------------------
 id     | integer |           | not null | nextval('test_idx_id_seq'::regclass)
 note   | text    |           |          |
Indexes:
    "test_idx_pkey" PRIMARY KEY, btree (id)

REINDEX重建索引：
postgres=# REINDEX INDEX CONCURRENTLY idx_test_idx6;

       

后记：

    CREATE INDEX  CONCURRENTLY 的基本原理需要进一步深入了解。