INVALIDなインデックスを特定してクリーンアップ

コマンド

SELECT n.nspname AS schema_name,
       c.relname AS index_name,
       t.relname AS table_name,
       pg_size_pretty(pg_relation_size(c.oid)) AS index_size,
       i.indisvalid,
       i.indisready,
       format('DROP INDEX CONCURRENTLY %I.%I;', n.nspname, c.relname) AS fix
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
JOIN pg_class t ON t.oid = i.indrelid
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE NOT i.indisvalid OR NOT i.indisready;

-- Before dropping: is an index build still in progress?
SELECT pid, relid::regclass AS table_name, index_relid::regclass AS index_name, command, phase
FROM pg_stat_progress_create_index;

概要

失敗したCREATE INDEX CONCURRENTLYやREINDEX CONCURRENTLYで残された無効なインデックスをサイズとともに一覧表示し、削除コマンドを生成する。

使用する場面

失敗または取り消されたオンラインインデックス操作の後、定期的なヘルスチェック、メジャーアップグレードの前に。

注意

実行中のCREATE INDEX CONCURRENTLY / REINDEX CONCURRENTLYもINVALIDと表示されるため、削除前にpg_stat_progress_create_indexを確認する。INVALIDなインデックスはクエリに使用されないが、indisready = trueの場合はすべてのINSERT/UPDATEで引き続きメンテナンスされ、純粋なオーバーヘッドとなる。REINDEX INDEX CONCURRENTLYで再構築できるが、原因(例: 一意性違反)を修正しないと再び失敗する。REINDEX CONCURRENTLYの残存物には_ccnew/_ccoldのサフィックスが付く。DROP INDEX CONCURRENTLYはトランザクションブロック内では実行できない。