PostgreSQL - DBA Toolkit - Index Bloat and Health

Indexes Get Sick Too

In PostgreSQL, indexes are not automatically compacted.

Every UPDATE and DELETE leaves dead versions of index entries behind. Over time, an index that started lean becomes full of pages pointing to rows that no longer exist. VACUUM removes the dead heap tuples, but index pages only get cleaned up during autovacuum index scans or a full REINDEX.

The result: indexes that are twice the size they need to be, scans that read far more pages than necessary, and write overhead for indexes that nothing is using.

This script identifies bloated indexes, unused indexes, and duplicate coverage so you can rebuild what needs rebuilding and drop what is just costing you.

    Loading…

Reading the Results

Index Usage Stats (pg_stat_user_indexes)

Column	What It Tells You
`schemaname` / `tablename`	Where the index lives
`indexname`	Index name
`idx_scan`	Number of index scans using this index since last stats reset
`idx_tup_read`	Tuples read through this index
`idx_tup_fetch`	Heap fetches triggered by this index
`index_size`	Disk space used

Bloat Estimate

Column	What It Tells You
`real_size`	Actual disk size of the index
`extra_size`	Estimated bloat — space that could be reclaimed
`bloat_ratio`	Percentage of the index that is dead space
`is_na`	`true` if the estimate is not available for this index type

What to Watch For

Signal	What It Means
`idx_scan = 0` on a large index	Index has never been used since stats reset — candidate for removal
`bloat_ratio` > 30%	Index has significant dead space — consider `REINDEX CONCURRENTLY`
Multiple indexes covering the same leading columns	Redundant indexes — every write pays to maintain all of them
`index_size` > `table_size`	Over-indexed table — evaluate which indexes are earning their keep
Bloat on primary key or unique indexes	High churn table with frequent deletes — schedule regular reindex
Many small indexes with low `idx_tup_fetch`	Partial indexes or highly selective indexes that rarely fire — review selectivity

Rebuilding Bloated Indexes

    Loading…

REINDEX CONCURRENTLY builds a replacement index in the background while the old one stays live. It takes longer but does not block reads or writes.

Note: REINDEX CONCURRENTLY cannot run inside a transaction block.

Gareth Winterman