Measuring index bloat without an extension

14 August 2026

Every few months an index doubles in size and nothing obviously changed. The usual advice is to install pgstattuple, but on managed instances you often cannot.

The catalog already knows enough for an estimate. Compare the number of live tuples against the pages actually allocated:

SELECT c.relname,
       pg_size_pretty(pg_relation_size(c.oid)) AS size,
       c.reltuples::bigint AS rows
FROM pg_class c
JOIN pg_index i ON i.indexrelid = c.oid
WHERE c.relkind = 'i'
ORDER BY pg_relation_size(c.oid) DESC
LIMIT 20;

Divide size by rows. A btree over a single bigint should land near 30 bytes per row once you account for page overhead. Three times that is worth a look.

Index size over eight weeks — One index over eight weeks. The step is a bulk delete that never got vacuumed.

The fix is nearly always REINDEX CONCURRENTLY. It takes a while and needs the disk headroom, but it does not block writes.

Estimates are estimates. Before rebuilding anything large, check whether autovacuum is simply behind.