【PostgreSQL】CREATE INDEX CONCURRENTLYでサービス無停止でインデックスを作成する

通常のCREATE INDEXが取得するロック

通常のCREATE INDEXは対象テーブルにShareLockを取得する。ShareLockはINSERT・UPDATE・DELETEが取得するRowExclusiveLockと競合するため、インデックス作成が完了するまで書き込みがブロックされる。

BEGIN;
CREATE INDEX idx_accounts_balance ON accounts (balance);
SELECT locktype, relation::regclass, mode, granted
FROM pg_locks
WHERE relation = 'accounts'::regclass;
ROLLBACK;

 locktype | relation |   mode    | granted 
----------+----------+-----------+---------
 relation | accounts | ShareLock | t

数百万行あるテーブルに対して実行すると、インデックス作成に数十秒から数分かかることがあり、その間書き込みが止まってしまう。実際にShareLockを保持した状態で別セッションからINSERTを実行すると、ロックが解放されるまで待たされる。

-- セッションA
BEGIN;
LOCK TABLE accounts IN SHARE MODE;

-- セッションB
INSERT INTO accounts (name, balance) VALUES ('user', 1);
-- セッションAのロックが解放されるまで待たされる

CONCURRENTLYで取得するロック

CREATE INDEXにCONCURRENTLYを付けると、取得するロックがShareUpdateExclusiveLockに変わる。ShareUpdateExclusiveLockはRowExclusiveLockと競合しないため、インデックス作成中もINSERT・UPDATE・DELETEをブロックしない。

CREATE INDEX CONCURRENTLY idx_accounts_balance ON accounts (balance);

ShareUpdateExclusiveLockを保持した状態で別セッションからINSERTを実行しても、待たされずに完了する。

-- セッションA
BEGIN;
LOCK TABLE accounts IN SHARE UPDATE EXCLUSIVE MODE;

-- セッションB
INSERT INTO accounts (name, balance) VALUES ('user', 1);
-- 待たされずに完了する

書き込みをブロックしない代わりに、CONCURRENTLYはテーブルを2回スキャンするため、通常のCREATE INDEXよりも時間がかかる。

CONCURRENTLYの制約

CREATE INDEX CONCURRENTLYはトランザクションブロック内では実行できない。

BEGIN;
CREATE INDEX CONCURRENTLY idx_accounts_balance ON accounts (balance);
ROLLBACK;

ERROR:  CREATE INDEX CONCURRENTLY cannot run inside a transaction block

失敗時に残る無効なインデックス

CREATE INDEX CONCURRENTLYの実行中に接続が切れたりキャンセルされたりすると、作成途中のインデックスが無効な状態のまま残る。pg_indexのindisvalid列で有効性を確認できる。

SELECT indexrelid::regclass, indisvalid FROM pg_index WHERE indrelid = 'accounts'::regclass;

      indexrelid      | indisvalid 
----------------------+------------
 accounts_pkey        | t
 idx_accounts_balance | t
 idx_accounts_name    | f

indisvalidがfのインデックスは検索には使われず、プランナーからも無視される。このまま放置せず、DROP INDEX CONCURRENTLYで削除してから作り直す。

DROP INDEX CONCURRENTLY idx_accounts_name;

DROP INDEXもCONCURRENTLYを付けることで、通常のDROP INDEXが取得するAccessExclusiveLockを避け、書き込みをブロックせずに削除できる。