【PostgreSQL】REINDEX CONCURRENTLYでインデックスを無停止で再構築する

インデックスの再構築が必要になる場面

インデックスはUPDATEやDELETEを繰り返すと、削除済みの領域を残したまま肥大化することがある。

SELECT pg_size_pretty(pg_relation_size('idx_accounts_balance'));

 pg_size_pretty 
----------------
 1936 kB

同じ行を3回更新すると、インデックスのサイズが4倍近くに膨らむ。

UPDATE accounts SET balance = balance + 1;
UPDATE accounts SET balance = balance + 1;
UPDATE accounts SET balance = balance + 1;

 pg_size_pretty 
----------------
 7712 kB

VACUUMでは肥大化が解消しない

不要になったインデックスエントリは通常のVACUUMでも削除される。しかしVACUUMはエントリを削除して領域をページ内の空き領域として確保するだけで、インデックスのファイルサイズそのものは縮小しない。

VACUUM accounts;
SELECT pg_size_pretty(pg_relation_size('idx_accounts_balance'));

 pg_size_pretty 
----------------
 7712 kB

VACUUMを実行してもサイズは7712 kBのままである。VACUUMが確保した空き領域は以降のINSERTやUPDATEで再利用されるが、既に肥大化したファイルサイズを縮めるには不十分である。テーブルとインデックスを丸ごと作り直すVACUUM FULLならサイズを縮小できるが、AccessExclusiveLockを取得するため実行中はテーブルへの読み書きがすべてブロックされる。サービスを止めずにインデックスのサイズを戻すには、次に説明するREINDEX CONCURRENTLYを使う。

通常のREINDEXが取得するロック

肥大化したインデックスはREINDEXで再構築するとサイズを戻せる。ただし通常のREINDEXは対象のインデックスにAccessExclusiveLockを取得し、テーブルへの書き込みをブロックする。さらにクエリプランナーはテーブルの全インデックスに参照ロックを取得しようとするため、そのインデックスを使う読み取りも実行中はブロックされる。

【PostgreSQL】CREATE INDEX CONCURRENTLYでサービスを止めずにインデックスを作成するにまとめたCREATE INDEX CONCURRENTLYと同様、REINDEXにもCONCURRENTLYオプションがある。これを使うとサービスを止めずに再構築できる。

REINDEX CONCURRENTLYの実行

REINDEX INDEX CONCURRENTLY idx_accounts_balance;

SELECT pg_size_pretty(pg_relation_size('idx_accounts_balance'));

 pg_size_pretty 
----------------
 1936 kB

再構築後はサイズが肥大化前の状態に戻る。テーブル単位でREINDEX TABLE CONCURRENTLYを実行すると、対象テーブルに定義されているすべてのインデックスをまとめて再構築できる。

REINDEX TABLE CONCURRENTLY accounts;

実行中に作成される一時インデックス

REINDEX INDEX CONCURRENTLYは、既存のインデックスをその場で書き換えるのではなく、_ccnewという接尾辞を付けた新しいインデックスを別名で作成してからデータを流し込み、完成後に元のインデックスと入れ替える。この間accountsテーブルにはShareUpdateExclusiveLockが取得されるだけなので、INSERT・UPDATE・DELETEはブロックされない。

SELECT locktype, relation::regclass, mode, granted
FROM pg_locks
WHERE relation::regclass::text LIKE 'accounts%' OR relation::regclass::text LIKE 'idx_accounts%';

 locktype |          relation          |           mode           | granted 
----------+----------------------------+--------------------------+---------
 relation | idx_accounts_balance_ccnew | RowExclusiveLock         | t
 relation | idx_accounts_balance       | ShareUpdateExclusiveLock | t
 relation | accounts                   | ShareUpdateExclusiveLock | t
 relation | idx_accounts_balance_ccnew | ShareUpdateExclusiveLock | t

再構築が正常に完了すると_ccnewインデックスは元のインデックス名に置き換わり、一時的なインデックスは残らない。

REINDEX CONCURRENTLYの制約

CREATE INDEX CONCURRENTLYと同様に、REINDEX CONCURRENTLYもトランザクションブロック内では実行できない。

BEGIN;
REINDEX INDEX CONCURRENTLY idx_accounts_balance;
ROLLBACK;

ERROR:  REINDEX CONCURRENTLY cannot run inside a transaction block

失敗時に残る一時インデックス

REINDEX INDEX CONCURRENTLYの実行中に接続が切れたりキャンセルされたりすると、_ccnewの付いた一時インデックスが無効な状態のまま残る。

\d accounts

Indexes:
    "accounts_pkey" PRIMARY KEY, btree (id)
    "idx_accounts_balance" btree (balance)
    "idx_accounts_balance_ccnew" btree (balance) INVALID

このまま放置せず、DROP INDEXで削除してからREINDEX CONCURRENTLYを再実行する。_ccnewが付いたインデックスは無効で使われていないため、通常のDROP INDEXが一瞬取得するAccessExclusiveLockの影響はほとんどない。【PostgreSQL】CREATE INDEX CONCURRENTLYでサービスを止めずにインデックスを作成するで扱った無効なインデックスも、同様の考え方で削除できる。

DROP INDEX idx_accounts_balance_ccnew;