This year I will be participating in the 2025 AIDS Lifecycle 🎉
Please consider donating on my rider page 🚲

Querying for invalid postgres indexes

Jan 22, 2025 #postgres #databases

Indexes can fail to build concurrently in Postgres in a number of reasons. I've used the following query several times to find indexes that have failed to successfully build. This query returns the name of the index which can then be used to drop the index manually if needed.

SELECT
  *
FROM
  pg_class,
  pg_index
WHERE
  pg_index.indisvalid = FALSE
  AND pg_index.indexrelid = pg_class.oid