Build1 publisher2 min readPublished
PostgreSQL's VACUUM strands sparse b-tree pages until a REINDEX rebuilds them
In a dev.to case study, PostgreSQL's VACUUM left indexes more than six times the size of their heap because it frees only b-tree pages that empty completely. The post sizes the bloat against the default fillfactor of 90, then rebuilds one index at a time with REINDEX CONCURRENTLY.
The Engineer · Build desk
What happened
- A PostgreSQL host with a 125 GB root volume had just crossed its under-20%-free disk alert with 24 GB left, in a case study published on dev.to.
- Its largest table, batch_result, held about 20 million live rows in 4,710 MB of heap under 30 GB of indexes, more than six times the data those indexes point at.
- Autovacuum had run 7,900 times on batch_result and more than 40,000 times each on two other hot tables, on default settings with nothing pinning the cleanup horizon.
- According to the post, VACUUM reclaims only index pages that become completely empty, so a page keeping one key out of several hundred stays allocated for good.
Compiled by The EngineerSomething wrong?How this is made
Why it matters
- cost VACUUM FULL, the author's first plan, would have needed free space equal to the 35 GB table on a disk with 24 GB free, and it holds ACCESS EXCLUSIVE while it runs.
- constraint Raising autovacuum_vacuum_cost_limit or shortening autovacuum_naptime cannot recover sparse index pages, so vacuum tuning spends effort on a subsystem that never merges them.
- decision Choosing between REINDEX and VACUUM FULL has to start with splitting pg_relation_size from pg_indexes_size, because pg_total_relation_size hides whether the heap or the indexes are bloated.
- exposure Treating idx_scan = 0 as proof an index is dead can drop one that backs a unique constraint, since INSERT uniqueness checks never increment that counter, and replicas keep their own counts.
According to the routine vacuuming docs the post cites, VACUUM makes space reusable inside the file. It does not hand that space back to the operating system [16]. The post's second table is the extreme case. The batch_cache table reported zero live rows, 30 MB of heap and 4,474 MB of indexes [12]. That is about 149 times as much index as heap [2]. It had taken 9.16 billion inserts and the same number of deletes, with no updates at all [19].
To size the damage, the post reads avg_leaf_density from pgstatindex in the pgstattuple extension. It compares that figure with 90, because a freshly built b-tree sits near its default fillfactor of 90 [2]. Its estimate of the size after a rebuild is current size times density divided by 90 [3]. I think this is the best idea in the post. You get a number to check before the rebuild and the same number to check after it. The 90 is only a default, so an index created with a different fillfactor needs its own value in the denominator [2].
The absolute volumes in the post are modelled. The author says the real parts are the index-to-heap ratios, the densities (rounded to whole percent) and the PostgreSQL defaults [8]. For those ratios to carry over to another system, its tables need the same churn: most keys on a leaf page deleted, with a few left behind [1]. An append-mostly table leaves few half-empty leaf pages for VACUUM to strand. batch_result had the churning shape, with 9.14 billion inserts against 9.11 billion deletes [19].
The rebuild is REINDEX INDEX CONCURRENTLY, run one index at a time. Peak extra space is one new index, and the disk comes back after each step [4]. On this box the indexes of the two tables held about 34 GB. That is roughly three times the 11 GB in logs and stale images, which the author calls the usual suspects [10][4]. The author had planned to run VACUUM FULL on the worst table [20]. Autovacuum was innocent this time. "My first half hour went into a hypothesis one query would have killed," the author wrote [18].
What to watch
- Whether post-rebuild index sizes land near the post's current_size x density / 90 estimate on tables other than the modelled ones.
- How fast avg_leaf_density falls again after a REINDEX on tables churning billions of inserts and deletes, which sets how often the rebuild has to be repeated.