What are the available options to identify and remove the invalid objects in Postgres (ex: corrupted indexes)

Question

What are the available options to identify and remove the invalid objects in Postgres

Answer 1

If you're referring to detecting "invalid" (poorly created) indexes, apparently Postgres can "fail" in an attempt to create an index, and then the query planner won't use them, though they exist in your system. This query will detect "failed" indexes:

https://www.enterprisedb.com/blog/pgupgrade-bug-invalid-concurrently-created-indexes

SELECT n.nspname, c.relname
FROM   pg_catalog.pg_class c, pg_catalog.pg_namespace n,
       pg_catalog.pg_index i
WHERE  (i.indisvalid = false OR i.indisready = false) AND
       i.indexrelid = c.oid AND c.relnamespace = n.oid AND
       n.nspname != 'pg_catalog' AND
       n.nspname != 'information_schema' AND
       n.nspname != 'pg_toast'

though I suppose detecting TOAST table indexes wouldn't hurt, so you can remove that portion of the query :)

Related, for me sometimes just running a fresh ANALYZE on a table also makes indexes suddenly start being used in production (i.e. even if indexes aren't "invalid" they may be unused until an ANALYZE run). Weird.

Answer 2

Have you tried running vacuum full pg_class as superuser?

Also, auto-vacuum should take care of it eventually. Your objects seem to be temporary tables/indexes, and the catalog is (usually) not being updated as frequently as your data.