H001 Invalid indexes

Goal

Detect indexes that are in INVALID state.

If a problem arises while scanning the table, such as a deadlock or a uniqueness violation in a unique index, the CREATE INDEX command will fail but leave behind an “invalid” index.

See https://www.postgresql.org/docs/current/sql-createindex.html

TODO

Create a report showing invalid indexes.

How to implement

Use indisvalid column in pg_index.

Acceptance criteria

As a DBA I can see the list of invalid indexes, with sizes in B/kiB/MiB/GiB. I will decide (depending on the concrete situation) whether they need to be dropped or to be recreated.

❓ what about constraints in INVALID state? (such as foreign keys or CHECK constraint)