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/)

PostgreSQL DBA(37) - PG 12 REINDEX CONCURRENTLY

相關文章