Cleaning up an invalid index from a failed CREATE INDEX CONCURRENTLY

A CREATE INDEX CONCURRENTLY was interrupted — the index entry exists in the catalog but is marked indisvalid = false. It imposes write overhead without being usable by the planner.

Diagnose it

SELECT n.nspname || '.' || t.relname    AS table_name,
       i.relname                           AS index_name,
       ix.indisvalid,
       ix.indisready,
       ix.indislive,
       pg_size_pretty(pg_relation_size(i.oid)) AS index_size
FROM pg_index ix
JOIN pg_class i  ON i.oid  = ix.indexrelid
JOIN pg_class t  ON t.oid  = ix.indrelid
JOIN pg_namespace n ON n.oid = t.relnamespace
WHERE NOT ix.indisvalid
  AND n.nspname NOT IN ('pg_catalog', 'pg_toast', 'information_schema')
ORDER BY t.relname;

An index where indisvalid = false is still maintained by DML (if
indisready = true) but is never used by the planner.

Why it happens

CREATE INDEX CONCURRENTLY works in multiple phases. If it is cancelled
(by a signal, a statement timeout, or a deadlock) between phases, the index can be left
in a broken state: present in the catalog, possibly partially built, but not usable.
The catalog entry must be cleaned up manually.