Measuring Index Bloat with pgstattuple #

“That index is huge” is not a diagnosis. A 4 GB index over a 400 million row table may be perfectly packed, and a 200 MB index may be two-thirds air. The only way to tell is to look inside the pages, which is what the pgstattuple extension does.

This page covers which function to use for which relation, what the numbers mean, and how to turn a reading into a decision. The repair itself is in REINDEX CONCURRENTLY without downtime; the wider context is index maintenance and bloat.

What the Functions Report #

The extension ships three functions that matter here, and they answer different questions:

The two numbers that drive decisions are avg_leaf_density and leaf_fragmentation.

avg_leaf_density is the average percentage of each leaf page occupied by live index entries. Compare it against the index’s fillfactor, which defaults to 90 for B-trees — that is the density a fresh build produces. Half of that means you read two pages for every page of useful data.

leaf_fragmentation measures how far the physical ordering of leaf pages has drifted from the logical key order. High fragmentation turns what should be sequential reads during a range scan into random ones, which matters even when density is fine.

Reading avg_leaf_density A horizontal scale from zero to one hundred percent leaf density divided into three bands: below fifty percent means rebuild, fifty to seventy means monitor, above seventy means healthy. A marker sits at forty-one point seven percent, inside the rebuild band. avg_leaf_density — percentage of each leaf page holding live entries rebuild pays off monitor healthy 0% 50% 70% 90% (default fillfactor) measured: 41.7% → rebuild compare against the index's own fillfactor, not against 100%

Running the Measurement #

Install the extension once per database, then measure only the indexes worth measuring:

CREATE EXTENSION IF NOT EXISTS pgstattuple;

-- Candidates first: big indexes are the only ones where a rebuild repays the I/O
SELECT s.indexrelname AS index,
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS size,
       s.idx_scan
  FROM pg_stat_user_indexes s
 WHERE pg_relation_size(s.indexrelid) > 500 * 1024 * 1024
 ORDER BY pg_relation_size(s.indexrelid) DESC;

Then look inside each candidate:

SELECT tree_level, index_size, leaf_pages, empty_pages, deleted_pages,
       avg_leaf_density, leaf_fragmentation
  FROM pgstatindex('events_account_created_idx');

-- tree_level        | 3
-- index_size        | 4184342528        -- 4.0 GB
-- leaf_pages        | 507421
-- empty_pages       | 0
-- deleted_pages     | 89204             -- pages vacuum emptied but did not return to the OS
-- avg_leaf_density  | 41.7              -- vs 90 in a fresh build
-- leaf_fragmentation| 38.2

deleted_pages at 17% of leaf_pages is the mechanism made visible: vacuum removed the entries, the pages are reusable by this index, and nothing has reused them yet. Together with a density of 41.7% this is an unambiguous rebuild candidate.

Contrast with a healthy index on the same table:

SELECT avg_leaf_density, leaf_fragmentation, deleted_pages
  FROM pgstatindex('events_pkey');
-- 89.4 | 2.1 | 0

Same table, same write traffic, no bloat — because the primary key is an append-only bigint whose entries are never updated in place.

Step-by-Step Measurement Workflow #

  1. Enable the extension in each database you intend to measure. It is not installed by default and requires superuser or an appropriate role grant.

  2. Filter by size before measuring. pgstatindex reads the entire index; running it across hundreds of small indexes wastes more I/O than the results are worth.

  3. Measure during a quiet period. A full index read pulls pages into shared buffers and can evict a working set that took hours to warm.

  4. Interpret against the fillfactor, not against 100%:

    SELECT reloptions FROM pg_class WHERE relname = 'events_account_created_idx';
-- {fillfactor=70}  → a fresh build would be ~70%, so 65% is fine, not bloated

  5. Record readings over time. A single 60% reading means little; 88% falling to 60% over six weeks is a maintenance schedule.

  6. Cross-check against usage with pg_stat_user_indexes.idx_scan. A bloated index nobody scans should be dropped rather than rebuilt.

  7. Measure the heap separately when index-only scans are underperforming — pgstattuple on the table shows whether dead heap tuples, not index pages, are the problem.

Which measurement function to use Three rows describing pgstatindex for B-tree indexes with a full read and density output, pgstattuple for heaps and other index types with a full read and dead tuple percentages, and pgstattuple_approx for heaps only with a sampled cheap read. function use on cost key output pgstatindex() B-tree indexes full read avg_leaf_density leaf_fragmentation pgstattuple() heaps, GIN/GiST/BRIN full read dead_tuple_percent free_percent pgstattuple_approx() heaps only sampled, cheap approx_free_percent approx_dead_tuple_percent

Before and After #

-- BEFORE rebuild
avg_leaf_density  41.7   leaf_fragmentation 38.2   deleted_pages 89204   index_size 4.0 GB
query: Buffers: shared hit=31 read=27      Execution Time: 11.94 ms

-- AFTER REINDEX CONCURRENTLY
avg_leaf_density  90.1   leaf_fragmentation  1.4   deleted_pages     0   index_size 1.9 GB
query: Buffers: shared hit=9  read=1       Execution Time:  1.48 ms

Density and fragmentation both reset, and the buffer count for the same 412 rows fell by 83%. Those are the two numbers to record in the change log.

Common Pitfalls #

Using a bloat “estimate” query as proof. The widely circulated estimation queries based on pg_class and column widths are useful for ranking candidates but can be off by a wide margin. Diagnostic signal: an estimate that disagrees with pgstatindex. Fix: rank with the estimate, decide with the real measurement.

Running pgstatindex on a hot system. A full index read evicts the working set and briefly hurts everything. Diagnostic signal: a cache-hit dip during your measurement window. Fix: schedule measurement like any other maintenance job.

Ignoring fillfactor. An index deliberately built at fillfactor=70 is healthy at 68%. Diagnostic signal: reloptions containing a non-default fillfactor. Fix: compare against the configured value.

Treating a single reading as a trend. Density fluctuates with workload. Diagnostic signal: contradictory conclusions week to week. Fix: store readings and act on the slope.

Measuring the index when the heap is the problem. High Heap Fetches on an index-only scan is a visibility-map issue, not an index one — see visibility map and index-only scan regressions. Diagnostic signal: healthy density with slow index-only scans. Fix: measure the heap with pgstattuple and tune vacuum.

Measuring during or just after a vacuum. A vacuum pass frees space inside leaf pages without returning them to the operating system, so density readings taken mid-cycle can swing widely. Diagnostic signal: two measurements minutes apart disagreeing by twenty points. Fix: sample at a consistent point in the workload cycle, and prefer a trend line over any single figure.

Applying B-tree thinking to GIN and BRIN. pgstatindex is a B-tree function; other access methods have their own shape and their own failure modes, such as the GIN pending list. Diagnostic signal: an attempt to read avg_leaf_density for a GIN index. Fix: use pgstatginindex for GIN — see GIN fastupdate and pending list tuning — and pgstattuple for the rest.

Reading density without reading size. A 40% dense index of 20 MB is not worth a maintenance window; the same density on 40 GB certainly is. Diagnostic signal: a rebuild queue full of tiny indexes. Fix: rank by wasted bytes — size multiplied by the density shortfall — rather than by percentage alone.

Act on the slope, not on one reading Weekly density measurements falling from eighty-eight percent to forty-two percent over six weeks, crossing the fifty percent action line in week five where a rebuild is scheduled. weekly measurement → 50% — act below here 88% 42% schedule the rebuild here

Frequently Asked Questions #

What avg_leaf_density counts as bloated? Compare against the index’s fillfactor, 90% by default. Above 70% is healthy, 50–70% is worth watching, and below 50% means roughly half of every page read is empty — the point where a rebuild starts to repay its own I/O.

Is pgstatindex expensive to run? It reads the entire index, so on multi-gigabyte indexes it competes for I/O and disturbs the buffer cache. Schedule it in quiet windows. The cheaper pgstattuple_approx exists only for heaps, not for B-tree indexes.

Why does leaf_fragmentation matter separately? Density tells you how full pages are; fragmentation tells you whether they are in key order on disk. A dense but fragmented index still converts range scans into random reads, so a range-heavy workload can justify a rebuild at otherwise acceptable density.