Trouver et nettoyer les index INVALID

Commande

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;

Ce que fait la commande

Liste les index invalides laissés par des CREATE INDEX CONCURRENTLY ou REINDEX CONCURRENTLY en échec, avec leur taille, et génère la commande de suppression.

Quand l'utiliser

Après des opérations d'index en ligne échouées ou annulées, lors des contrôles de santé de routine et avant une mise à niveau majeure.

Attention

Un CREATE INDEX CONCURRENTLY / REINDEX CONCURRENTLY en cours apparaît aussi comme INVALID ; vérifiez pg_stat_progress_create_index avant de supprimer. Un index INVALID n'est pas utilisé par les requêtes, mais si indisready = true il est encore maintenu à chaque INSERT/UPDATE, c'est-à-dire qu'il n'est qu'une surcharge. Il peut être reconstruit avec REINDEX INDEX CONCURRENTLY, mais cela échoue de nouveau si la cause (par ex. une violation d'unicité) n'est pas corrigée. Les restes de REINDEX CONCURRENTLY portent le suffixe _ccnew/_ccold. DROP INDEX CONCURRENTLY ne peut pas s'exécuter dans un bloc de transaction.