How to enable pg_stat_user_indexes on postgres on Amazon RDS

Question

We want to check if we have unused indexes in our postgres 15.5 RDS instances.

Currently, we did "CREATE EXTENSION IF NOT EXISTS pg_stat_statements"

Which enables pg_stat_satements, but what we need is: "pg_stat_user_indexes"

The question is, how do we enable it?

The only info we can find is that "track_activities" config parameter should be set to true, but we dont know if this will enable all of the hundres of pg_stat tracking, or if there is more to do to specifically enable the collection of pg_stat_user_indexes.

Also, we dont know how to change parameters exactly. all 30 of our prod DBs have the same production parameter group. If we edit this parameter group, it will affect all 30DBs, and we only want to enable this on one. Is there some workaround for this? E.g. duplicate the prod parameter group (if this is possible via the web ui), and reassign the new temporary group (without a reboot).

Also, we certainly cant reboot any production servers, so we need a way to enable/disable tracking of pg_stat_user_indexes without reboot.

Any ideas?

• Laurenz Albe: track_activities is on by default (indispensable for autovacuum), and pg_stat_user_indexes always exists, so unless Amazon messed that up somehow, there is nothing to do.

Answer 1

Found a set of steps which dont require reboot:

1. copied the parameter group used by all dbs to a temp copy.
2. set "track_activities" dynamic value to true (1) on the temp copy.
3. modified one DB and assigned it to the new temp parameter group.
4. waited till it said modification complete.

Now I can do 'select * from pg_stat_user_indexes;'