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:

`CREATE INDEX new_<index_name> CONCURRENTLY;`

`RENAME INDEX <index_name> TO old_<index_name>;`

`RENAME INDEX new_<index_name> TO <index_name>;`

`DROP INDEX CONCURRENTLY old_<index_name>;`

[0]: https://www.postgresql.org/docs/current/pgstattuple.html

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.

https://wiki.postgresql.org/wiki/Show_database_bloat