Найти и удалить индексы 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 нельзя выполнять внутри блока транзакции.