0

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.

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.