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가 남긴 invalid 인덱스를 크기와 함께 나열하고 삭제 명령을 생성한다.

사용 시점

실패하거나 취소된 온라인 인덱스 작업 후, 정기 상태 점검 및 메이저 업그레이드 전.

주의

진행 중인 CREATE INDEX CONCURRENTLY / REINDEX CONCURRENTLY도 INVALID로 표시되므로 삭제 전에 pg_stat_progress_create_index를 확인한다. INVALID 인덱스는 쿼리에 사용되지 않지만 indisready = true이면 모든 INSERT/UPDATE에서 계속 유지되므로 순수한 오버헤드다. REINDEX INDEX CONCURRENTLY로 재구축할 수 있지만 원인(예: unique 위반)을 해결하지 않으면 다시 실패한다. REINDEX CONCURRENTLY 잔여물에는 _ccnew/_ccold 접미사가 붙는다. DROP INDEX CONCURRENTLY는 트랜잭션 블록 안에서 실행할 수 없다.