Postgres Index Bloat: avg_leaf_density Said 96% on a 10x Index
Every Sunday at 3am a cron job checks the B-tree indexes on my smallest Postgres box. It flags anything with avg_leaf_density under 50 and emails me. Fourteen months, zero emails. Then the disk hit 85% and I went looking.
The table holds device status. 200,000 rows, a status column with two values, a heartbeat every 30 seconds. The index on status had gone from 1.3 MB to 14 MB without anyone adding a row. Textbook Postgres index bloat, and the cron job said nothing.
There are two kinds of B-tree bloat in Postgres and the metric I was watching only sees one.
Density averages the pages that are left
pgstatindex() returns index_size, leaf_pages, empty_pages, deleted_pages and avg_leaf_density. The docs are blunt: index_size normally equals internal plus leaf plus empty plus deleted pages, plus one metapage. Every page in the file counts.
Density averages leaf pages. Once VACUUM empties a page and unlinks it from the tree, it is not a leaf page anymore. It still eats disk, and it stops affecting the number you watch.
Ten rounds of flipping every row to the other value, with a VACUUM after each:
-- fresh
index_size | leaf_pages | deleted_pages | empty_pages | avg_leaf_density
------------+------------+---------------+-------------+------------------
1400832 | 169 | 0 | 0 | 89.69
-- after 2,000,000 updates to the indexed column, then VACUUM
14295040 | 157 | 1585 | 0 | 95.90
-- after REINDEX INDEX CONCURRENTLY
1400832 | 169 | 0 | 0 | 89.69
The file grew tenfold and the density went up. Not down, up. The tree also gained a level, 1 to 2, because the surviving leaf pages ended up scattered across a bigger file.
Same story on a bigger index: 15 MB, 1,977 pages, 1,440 of them dead, density 70.18. Most thresholds are 70. It passed by a rounding error.
Now the other kind, same table. The primary key was 32 MB, 4,098 leaf pages, zero dead pages, density 30.23. Every page still in the tree and a third full. That is a real read problem: a range scan walks three times the pages it needs. My cron job was built for that kind.
The check I run now
Density for the pages still in the tree, dead pages for the ones that fell out of it.
CREATE EXTENSION IF NOT EXISTS pgstattuple;
WITH biggest AS (
SELECT c.oid AS index_oid, c.relname
FROM pg_class c
JOIN pg_index i ON i.indexrelid = c.oid
WHERE c.relkind = 'i'
AND i.indisvalid -- more on this below
ORDER BY pg_relation_size(c.oid) DESC
LIMIT 20
)
SELECT relname AS index_name,
pg_size_pretty(pg_relation_size(index_oid)) AS total,
s.leaf_pages,
s.deleted_pages,
s.empty_pages,
round(100.0 * s.deleted_pages
/ nullif(s.leaf_pages + s.deleted_pages + s.empty_pages, 0), 1) AS dead_page_pct,
s.avg_leaf_density AS density
FROM biggest
CROSS JOIN LATERAL pgstatindex(index_oid) s
ORDER BY pg_relation_size(index_oid) DESC;
pgstatindex reads the whole index, so the CTE caps it at the twenty biggest, not all four hundred. The functions are also limited to pg_stat_scan_tables, so your monitoring role needs GRANT pg_stat_scan_tables TO monitor_role; first.
Where the dead pages come from
HOT updates. Postgres can skip index writes when an update changes no indexed column and the new row fits on the same page. My heartbeat changed the status column, which is indexed. So it never qualified, not once.
SELECT relname, n_tup_upd, n_tup_hot_upd,
round(100.0 * n_tup_hot_upd / nullif(n_tup_upd, 0), 1) AS hot_pct
FROM pg_stat_user_tables
ORDER BY n_tup_upd DESC
LIMIT 10;
Mine read 0.0 after two million updates. If your hot_pct is high and the index is still fat, the culprit is DELETE churn or random-key inserts splitting pages, not updates.
VACUUM only removes the entries. Nothing goes back to the filesystem. The emptied pages do get reused, which I confirmed by adding 300,000 rows and watching 169 dead pages turn into live ones. But the file size is a high water mark. One bad week sets it, and it stays set.
REINDEX CONCURRENTLY, and the index nothing can read
A rebuild is the only thing that gives space back. REINDEX INDEX CONCURRENTLY builds a second copy, tracks writes, then swaps. Reads and writes keep running. It needs roughly the index size in free disk, cannot run in a transaction block, and makes two passes.
I wanted to see what a half-finished one leaves behind, so I cancelled one with statement_timeout. Answer: an index named devices_status_idx_ccnew with indisvalid = false, still on disk, still in the catalog.
The two cancel points are not equally bad. In the first pass indisready stays false, so it is dead disk and nothing else. In the second pass it is true, and every write from then on maintains an index the planner will never read. On a 3M row table the dead index grew from 20 MB to 23 MB while I inserted 300,000 rows, and that insert ran in 1.0 second against 0.7 without. One run each, so treat the timing as a direction. The 3 MB is not ambiguous.
It breaks the monitoring too. pgstatindex refuses to open an invalid index, so my first sweep died with ERROR: index "devices_status_idx_ccnew" is not valid and mailed me a stack trace instead of a report. Hence the filter. To find the leftovers:
SELECT c.relname AS index_name, i.indisready,
pg_size_pretty(pg_relation_size(c.oid)) AS size
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
WHERE NOT i.indisvalid;
-- then, one at a time:
DROP INDEX CONCURRENTLY devices_status_idx_ccnew;
What the rebuild actually bought me
Not a faster query. My index-only count came back in 17 ms on the bloated index and 33 ms after the rebuild, warm cache both times. Noise, in the wrong direction. The read path was never the problem: the pages a scan walks were all live pages.
What changed was disk and writes. The big index went from 91 MB to 24 MB, the small one back to 1.3 MB and a tree level shorter, and the write path stopped paying for 1,585 pages of nothing.
So split the cases before booking a window. Density at 30% means your scans really are reading three times the pages, and rebuilding is a performance fix. The dead-page kind is a disk and write problem. Same command, different payoff.
My monthly routine is three queries and no cleverness. Sweep by size, read dead_page_pct and density separately, drop the unused indexes before rebuilding the used ones. It lives in Snippet Ark as "index bloat sweep" because I cannot remember SQL I wrote a month ago. If you have been blaming autovacuum for a fat index, table bloat and autovacuum falling behind covers the other half of that disk graph. When the plan looks wrong rather than the filesystem, my checklist for an ignored index is what I open.