DBAtlas · Database operations atlas

Find and clean up INVALID indexes

PostgreSQLMaintenance

Command

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;

What it does

Lists invalid indexes left behind by failed CREATE INDEX CONCURRENTLY or REINDEX CONCURRENTLY runs, with their size, and generates the drop command.

When to use it

After failed or cancelled online index operations, in routine health checks and before a major upgrade.

Caution

An in-progress CREATE INDEX CONCURRENTLY / REINDEX CONCURRENTLY also shows as INVALID; check pg_stat_progress_create_index before dropping. An INVALID index is not used by queries, but if indisready = true it is still maintained on every INSERT/UPDATE, i.e. it is pure overhead. It can be rebuilt with REINDEX INDEX CONCURRENTLY, but this fails again unless the cause (e.g. a unique violation) is fixed. REINDEX CONCURRENTLY leftovers carry the _ccnew/_ccold suffix. DROP INDEX CONCURRENTLY cannot run inside a transaction block.

Open in DBAtlas →

Related