Index maintenance on pg_catalog tables in AWS RDS

Question

I have had some bad historical usage on my Aurora PostgreSQL instance. Namely, connections that create a lot of temporary objects and some very long running transactions (which limits autovacuum).

Now I have some very, very bloat pg_catalog indices.

For example: pg_attribute has only 34k records but pg_attribute_relid_attnam_index and pg_attribute_relid_attnum_index are 630MB and 450MB!

In total I have gigabytes of (bloated) system indices on system tables. However, I cannot REINDEX or VACUUM FULL these tables due to permissions.

Is this situation correctable in any way? Or is my RDS managed database permanently hosed and I need to migrate to a new one.

Answer 1

REINDEX INDEX didn't work because PostgreSQL checks the index owner (which is rdsadmin).

However, REINDEX SYSTEM and REINDEX DATABASE only check the database owner (which is myself).

So

REINDEX SYSTEM <database>

fixes the issue.