Reclaiming Unused Indexes Disk Space in PostgreSQL 9.4

index-statisticspostgresqlpostgresql-9.4statistics

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.

Best Answer

Perhaps you could start with:

SELECT *
    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.

Related Question