Encontrar y limpiar í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;

Qué hace

Lista, con su tamaño, los índices inválidos que dejaron ejecuciones fallidas de CREATE INDEX CONCURRENTLY o REINDEX CONCURRENTLY, y genera el comando para eliminarlos.

Cuándo usarlo

Tras operaciones de índice en línea fallidas o canceladas, en las comprobaciones de salud rutinarias y antes de un upgrade mayor.

Precaución

Un CREATE INDEX CONCURRENTLY / REINDEX CONCURRENTLY en curso también aparece como INVALID; revise pg_stat_progress_create_index antes de eliminarlo. Las consultas no usan un índice INVALID, pero si indisready = true se sigue manteniendo en cada INSERT/UPDATE, es decir, es pura sobrecarga. Se puede reconstruir con REINDEX INDEX CONCURRENTLY, pero vuelve a fallar si no se corrige la causa (p. ej., una violación de unicidad). Los restos de REINDEX CONCURRENTLY llevan el sufijo _ccnew/_ccold. DROP INDEX CONCURRENTLY no se puede ejecutar dentro de un bloque de transacción.