If you have pgstattuple [0], you can check the bloat of indices. Otherwise, you can just make it a cron on a monthly / quarterly / semi-annually / whatever basis. Since PG12 you can do `REINDEX INDEX CONCURRENTLY` with zero downtime, so it really doesn't hurt to do it more often than necessary. Even before PG12, you can do an atomic version of it:
I find average leaf density to be the best metric of them all. Most btree indexes with default settings (fill factor 90%) will converge to 67.5% leaf density over time. So anything below that is bloated and a candidate for reindexing.
You can measure "bloat" in the index. It's essentially the wasted space in pages.
You can also have bloat in the heap for the same reasons.
You may also want to cluster if your pg_stat.correlation is low since that indicates your heap isn't in the same order as your index anymore. pg_repack can do all of this without blocking, but you can reindex just an index concurrently on version >= 12.
