PostgreSQL Index Bloat on AWS RDS — Diagnosis and Fix

Index bloat is a common and frequently overlooked cause of query performance degradation on high-write PostgreSQL databases. Unlike table bloat — where dead tuples accumulate in heap pages — index bloat occurs when index pages fill with entries pointing to dead heap tuples, pages become sparsely populated after deletions, and the index grows far beyond what its live row count would require. The index continues to work correctly but scans become slower, buffer cache utilisation drops, and storage costs increase. VACUUM handles heap dead tuples but does not repack index pages.

Why index bloat happens differently to table bloat

When PostgreSQL updates or deletes a row, the old version remains on the heap page until VACUUM removes it. But the index entry for the old row version also remains — VACUUM marks it as deletable but does not merge sparse index pages back together. Over time, on a high-update or high-delete table, you accumulate index pages where 30–40% of the entries are dead. The index is larger than its live data requires, and every index scan must traverse more pages to find live entries.

This is most severe on:

• Tables with high UPDATE rates (primary keys and unique indexes especially)
• Tables with high row turnover (event queues, session tables, audit logs)
• Tables where a bulk delete removed a large fraction of rows
• Tables that went through a VACUUM FULL (the heap is rebuilt but indexes are rebuilt from scratch — this actually fixes bloat in this case)

Diagnosing index bloat

There is no pg_stat_index_bloat built into PostgreSQL. The most reliable approach is the pgstattuple estimation query (which requires the pgstattuple extension) or the widely-used btree bloat estimation CTE. On RDS, pgstattuple is available as a trusted extension — enable it once per database:

CREATE EXTENSION IF NOT EXISTS pgstattuple;

Then check a specific index:

SELECT *
FROM   pgstatindex('idx_orders_user_id');

The key column is avg_leaf_density — the percentage of leaf page space actually in use. Below 70% is concerning on an active index. Below 50% is severe and worth reindexing. The free_pages and deleted_pages columns show how many pages have been marked as reusable.

To get a broad view of all indexes above a bloat threshold:

SELECT
  schemaname,
  tablename,
  indexname,
  pg_size_pretty(pg_relation_size(indexrelid))   AS index_size,
  idx_scan,
  idx_tup_read,
  idx_tup_fetch
FROM   pg_stat_user_indexes
WHERE  pg_relation_size(indexrelid) > 10 * 1024 * 1024  -- indexes > 10MB
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT  30;

Cross-reference large indexes with idx_scan. An index that is 500MB and has been scanned 0 times is also a candidate for removal — not reindexing, but dropping. See the free diagnostic toolkit for the full unused index query.

REINDEX CONCURRENTLY — the safe fix on live tables

REINDEX CONCURRENTLY was added in PostgreSQL 12. It rebuilds the index while the table remains readable and writable — equivalent to DROP INDEX CONCURRENTLY followed by CREATE INDEX CONCURRENTLY but done atomically. On RDS 16, 17, and 18, it is safe to run on a production instance during business hours with the following caveats:

-- Rebuild a single index concurrently (safe on live tables):
REINDEX INDEX CONCURRENTLY idx_orders_user_id;

-- Rebuild all indexes on a table concurrently:
REINDEX TABLE CONCURRENTLY public.orders;

-- Rebuild all indexes in the database (run during a quieter window):
REINDEX DATABASE CONCURRENTLY mydb;

Blocking REINDEX — only in a maintenance window

Plain REINDEX INDEX (without CONCURRENTLY) takes a ShareLock on the table and blocks all reads and writes for the duration. On a large index this can be minutes. Only use it during a planned maintenance window:

-- Blocking — takes a lock, do not run on production without a maintenance window:
REINDEX INDEX idx_orders_user_id;  -- blocks reads + writes

Preventing index bloat

Index bloat on high-write tables is not a one-time fix. If the underlying cause is not addressed, the index will re-bloat within weeks. The primary levers are:

• Set autovacuum_vacuum_scale_factor = 0.01 (or lower) per-table on your highest-churn tables using ALTER TABLE ... SET (autovacuum_vacuum_scale_factor = 0.01). More frequent vacuums prevent dead tuple accumulation that leads to index bloat.
• Set maintenance_work_mem higher when running manual VACUUM — it affects how many index entries can be cleaned per vacuum pass. Default is 64MB, which is often too low. Set per-session before vacuum: SET maintenance_work_mem = '512MB'.
• On tables with predictable bulk deletes (e.g., purging records older than 90 days), schedule a REINDEX TABLE CONCURRENTLY to run after the bulk delete — bulk deletes cause the sharpest index bloat spikes.
• Consider fillfactor on tables with high UPDATE rates. Setting fillfactor = 70 on a high-update table reserves 30% of each heap page for HOT updates, which reduce index updates entirely.