PG 12的新特性REINDEX CONCURRENTLY,详细描述如下: >This adds the CONCURRENTLY option to the REINDEX command. A REINDEX CONCURRENTLY on a specific index creates a new index (like CREATE INDEX CONCURRENTLY), then renames the old index away and the new index in place and adjusts the dependencies, and then drops the old index (like DROP INDEX CONCURRENTLY). The REINDEX command also has the capability to run its other variants (TABLE, DATABASE) with the CONCURRENTLY option (but not SYSTEM). The reindexdb command gets the --concurrently option. Author: Michael Paquier, Andreas Karlsson, Peter Eisentraut Reviewed-by: Andres Freund, Fujii Masao, Jim Nasby, Sergei Kornilov PG 12引入REINDEX CONCURRENTLY,为了解决在REINDEX期间不能执行查询操作的问题,REINDEX CONCURRENTLY使用了相对于pg_repack(前身是pg_reorg)成本更低的方法来实现在索引rebuild期间数据表的可读写查询,但rebuild的时间越长,需要的资源越多,其实现思路如下: 1.创建新的索引(在与原index一样的catalog中),以_ccnew结尾 2.构建新索引,这一步会并行去做 3.使新索引与在构建期间出现的变化同步(catch up) 4.重命名新索引为旧索引的名称并切换所有依赖旧索引的地方到新索引.旧索引设置为invalid(这一步成为交换) 5.标记旧索引为dead状态(vacuum进程可回收) 6.删除索引 上面每一步都需要事务.在reindexing table时,该表的所有索引会一次过进行收集每一步都会处理所有的索引.可以把这个过程视为在一个单独的事务中CREATE INDEX CONCURRENTLY后跟DROP INDEX的组合,中间有一步是完全透明的新旧索引切换. 如果在REINDEX期间出现异常,那么所有需要rebuild的索引的状态都是invalid,意味着这些索引仍然占用空间,定义仍在但不能使用. 下面是测试脚本,PG 11 vs PG 12的一些区别 **PG 11** ``` testdb=# CREATE TABLE tab (a int); CREATE TABLE testdb=# INSERT INTO tab VALUES (1),(1),(2); INSERT 0 3 testdb=# CREATE UNIQUE INDEX CONCURRENTLY tab_index on tab (a); ERROR: could not create unique index "tab_index" DETAIL: Key (a)=(1) is duplicated. testdb=# \d tab Table "public.tab" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------- a | integer | | | Indexes: "tab_index" UNIQUE, btree (a) INVALID testdb=# REINDEX TABLE tab; ERROR: could not create unique index "tab_index" DETAIL: Key (a)=(1) is duplicated. testdb=# DELETE FROM tab WHERE a = 1; DELETE 2 testdb=# REINDEX TABLE tab; REINDEX ``` **PG 12** ``` testdb=# CREATE TABLE tab (a int); CREATE TABLE testdb=# INSERT INTO tab VALUES (1),(1),(2); INSERT 0 3 testdb=# CREATE UNIQUE INDEX CONCURRENTLY tab_index on tab (a); psql: ERROR: could not create unique index "tab_index" DETAIL: Key (a)=(1) is duplicated. testdb=# REINDEX TABLE CONCURRENTLY tab; psql: WARNING: cannot reindex invalid index "public.tab_index" concurrently, skipping psql: NOTICE: table "tab" has no indexes REINDEX testdb=# DELETE FROM tab WHERE a = 1; DELETE 2 testdb=# REINDEX TABLE CONCURRENTLY tab; psql: WARNING: cannot reindex invalid index "public.tab_index" concurrently, skipping psql: NOTICE: table "tab" has no indexes REINDEX testdb=# REINDEX INDEX CONCURRENTLY tab_index; REINDEX testdb=# ``` PG 12自动跳过了invalid index. **参考资料** [Postgres 12 highlight - REINDEX CONCURRENTLY](https://paquier.xyz/postgresql-2/postgres-12-reindex-concurrently/)