Encontrar e limpar índices INVALID

Comando

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;

O que faz

Lista, com seu tamanho, os índices inválidos deixados por execuções de CREATE INDEX CONCURRENTLY ou REINDEX CONCURRENTLY com falha e gera o comando de remoção.

Quando usar

Após operações de índice online com falha ou canceladas, em verificações de saúde de rotina e antes de um upgrade major.

Atenção

Um CREATE INDEX CONCURRENTLY / REINDEX CONCURRENTLY em andamento também aparece como INVALID; verifique pg_stat_progress_create_index antes de remover. Um índice INVALID não é usado pelas consultas, mas se indisready = true ainda é mantido em todo INSERT/UPDATE, ou seja, é puro overhead. Pode ser reconstruído com REINDEX INDEX CONCURRENTLY, mas isso falha de novo se a causa (ex.: uma violação de unicidade) não for corrigida. Resíduos de REINDEX CONCURRENTLY têm o sufixo _ccnew/_ccold. DROP INDEX CONCURRENTLY não pode ser executado dentro de um bloco de transação.