Are there any hints, stats or alike available to check thoroughly which indexes can be safely deleted from a PostgreSQL 9.4.9 database? Our database keeps growing and growing and, we think there might be some lost in indexes not being used. In the past, we tried to trim down the database overall size (about 2TB now, mostly historic data), but customers kept needing access to old data, so we had to stop expiring old "supposedly unused" data because bringing it back alive, was a huge pain.

Any additional comments on saving space would be greatly appreciated.


Perhaps you could start with:

    FROM pg_stat_all_indexes
    WHERE schemaname NOT IN ( 'pg_catalog', 'pg_toast' )
        AND idx_scan = 0
        AND idx_tup_read = 0
        AND idx_tup_fetch = 0
        AND indexrelid NOT IN (
            SELECT indexrelid
                FROM pg_index
                WHERE indisunique = true
                    OR indisprimary = true ) ;

No guarantee that the indexes that pop up aren't ever used, but it should provide a list to start looking at.

  • had to exclude a few more schemas of historic data, and the result gave me 7 indexes that I'll have to analyze. Any ideas regarding unused tables? Mar 17 '17 at 12:24
  • @gonzalo-vasquez have a look at pg_stat_all_tables
    – gsiems
    Mar 17 '17 at 16:29

This site is temporarily in read only mode and not accepting new answers.

Not the answer you're looking for? Browse other questions tagged .