PostgreSQL index bloat: why VACUUM never shrinks an index, and how to measure it with avg_leaf_density

Short answer: If a PostgreSQL table’s heap is healthy and its indexes are six times larger than the heap, autovacuum is not broken and never was. VACUUM marks dead index entries reusable and reclaims index pages that become completely empty, but a page holding one surviving key out of several hundred stays allocated forever, because a b-tree does not merge sparse neighbours. Measure it with pgstatindex(...).avg_leaf_density, compare against the b-tree default fillfactor of 90, and rebuild with REINDEX INDEX CONCURRENTLY. VACUUM FULL is the wrong tool: it rewrites the entire table to fix a problem that lives in the indexes.

TL;DR

• Fix: REINDEX INDEX CONCURRENTLY <name>, one index at a time. Peak extra space is one new index, not one new table, and the disk comes back after each step.
• Diagnose: avg_leaf_density from pgstattuple. A freshly built b-tree sits near 90 because that is its default fillfactor. Anything far below is bloat you can quantify.
• Predict: recovered size is roughly current_size x density / 90. It is arithmetic off two numbers you already have.
• Don’t: VACUUM FULL, unless the heap is the thing that is bloated. It takes ACCESS EXCLUSIVE and needs free space the size of the whole table.
• Trap: an index with idx_scan = 0 is not automatically dead. Uniqueness checks on INSERT do not increment that counter, so unique indexes look unused while they are holding your constraints up.
• Trap: pg_stat_* counters are per node. A zero on the primary says nothing about what the replica is serving.

A note on numbers before anything else. The absolute volumes below are a model, because someone else’s disk is not mine to publish. What is real is the shape: the ratio of index size to heap, the densities rounded to whole percent, and every PostgreSQL default and doc quote, which you can check against the links yourself.

The disk alert was about the indexes, not the tables

The page that came in said the root volume was over 80% full, and the fix turned out to be in a place the alert could not see. Take a box with a 125 GB root volume, 101 GB used, 24 GB free, and a threshold that fires under 20% free. It had just crossed.

The space breakdown looked ordinary at first:

Logs and stale images are the usual suspects and they are worth about 11 GB here. The database is worth 62. So I went to look at the largest tables, fully intending to VACUUM FULL the worst one.

     relname     | total   | heap    | idx     | n_live_tup | n_dead_tup
-----------------+---------+---------+---------+------------+------------
 batch_result    | 35 GB   | 4710 MB | 30 GB   |   19840000 |    2600000
 batch_cache     | 4504 MB | 30 MB   | 4474 MB |          0 |     512000

That stopped the plan. A table of roughly 20 million rows carrying 4.6 GB of heap is fine. Its indexes are 30 GB, more than six times the data they point at. The second row is stranger still: zero live rows, 30 MB of heap, and 4.4 GB of indexes on top of nothing.

My first half hour went into a hypothesis one query would have killed. The lesson is cheap to state and I keep relearning it: split pg_relation_size from pg_indexes_size before choosing a tool. Heap bloat and index bloat are different diseases with different cures, and the combined pg_total_relation_size hides which one you have.

Autovacuum was not broken, it was working itself to death

The counters ruled out every standard explanation for “space is not coming back”. Autovacuum was on with default settings, there were no replication slots holding an old xmin, and no long-running transaction was pinning the horizon.

    relname     |     ins     |     upd     |     del     |    hot    | hot_pct | av_cnt
----------------+-------------+-------------+-------------+-----------+---------+--------
 batch_staging  |  9105000000 | 20300000000 |           0 |   1810000 |     0.0 |  42100
 batch_cache    |  9160000000 |           0 |  9160000000 |         0 |         |  51800
 batch_result   |  9140000000 |           0 |  9110000000 |         0 |         |   7900

Autovacuum had run tens of thousands of times on each of the hot tables. It was not asleep, misconfigured or starved. It was running flat out and the space still was not coming back, which meant the problem was something autovacuum does not do at all.

This is the counterintuitive part and it sends people down a long road of tuning autovacuum_naptime and autovacuum_vacuum_cost_limit. None of those knobs touch the mechanism below. The instinct “space is not returning, so vacuum must be failing” is wrong here in a specific and useful way.

What VACUUM actually does with freed space

VACUUM makes space reusable inside the file; it does not hand it back to the operating system. The routine vacuuming docs are explicit:

The standard form of VACUUM removes dead row versions in tables and indexes and marks the space available for future reuse. However, it will not return the space to the operating system, except in the special case where one or more pages at the end of a table become entirely free and an exclusive table lock can be easily obtained.

That exception almost never fires under a churn workload, because the free pages end up scattered rather than parked at the tail. This is a deliberate trade: VACUUM runs without locking the table, and the price is that df does not move.

VACUUM FULL does return space, and the VACUUM reference spells out what it costs:

This method also requires extra disk space, since it writes a new copy of the table and doesn’t release the old copy until the operation is complete.

On the numbers above that means rewriting 35 GB with 24 GB free, while holding ACCESS EXCLUSIVE, in order to reclaim space that is sitting in the indexes. It would have run the volume to zero and taken the application down on the way.

Why a b-tree never compacts itself

A b-tree reclaims index pages that go completely empty and leaves partially empty ones exactly where they are. That single sentence is the root of the whole incident, and the reindexing docs say it plainly:

B-tree index pages that have become completely empty are reclaimed for re-use. However, there is still a possibility of inefficient use of space: if all but a few index keys on a page have been deleted, the page remains allocated. Therefore, a usage pattern in which most, but not all, keys in each range are eventually deleted will see poor use of space. For such usage patterns, periodic reindexing is recommended.

There is no page merging and no rebalancing on delete. PostgreSQL will not walk two neighbouring leaf pages that are 5% full each and fold them into one. Once density drops it stays dropped, and the only operation that raises it is a full rebuild of the index.

Worth being precise about the failure pattern, because it explains why this bites some workloads and not others. Deleting a contiguous range is survivable, since whole pages empty out and get reclaimed. Deleting most of every range is the bad case, and a batch job that rewrites a table by key is exactly that: it scatters a few survivors across every page it touches.

Why it accumulated so fast: HOT updates at 0.0%

The accelerant was heap-only tuple updates not happening. When an UPDATE leaves every indexed column unchanged and the page has room, PostgreSQL writes the new row version in place and touches no index at all. When it cannot, every index on the table gets a new entry.

On the staging table above, tens of billions of updates produced 1.8 million HOT updates. Rounded to one decimal that is 0.0%. Every one of those updates wrote a fresh entry into each of the table’s indexes, and every superseded entry became a dead key sitting on a page that would never be reclaimed.

The two mechanisms stack. The b-tree not merging pages explains why the bloat is permanent; HOT at zero explains why it arrived within months instead of years. Looking at either one alone makes the numbers seem impossible.

The load profile behind it: roughly nine billion inserts and almost exactly nine billion deletes against a table holding 20 million rows. That is the entire contents of the table rewritten several hundred times over. The cache table is the same story with the ending removed, since it is emptied with DELETE instead of TRUNCATE, which leaves zero live rows and 4.4 GB of index behind.

The table autovacuum had never touched once

Separately, a table can be permanently below the threshold and never get vacuumed at all, which looks identical from the outside. The autovacuum settings docs describe autovacuum_vacuum_scale_factor as “a fraction of the table size to add to autovacuum_vacuum_threshold”, and note that “The default is 0.2 (20% of table size)”.

Take a reporting table of 10 million live rows. The threshold is autovacuum_vacuum_threshold plus that fraction of the table, so 50 plus 2 million dead rows before autovacuum will look at it. This one had accumulated about 240,000, roughly a tenth of what it needed, so its autovacuum_count was 0 and had always been 0. Its HOT ratio was near 70%, so it was not bloating fast, but its dead rows were never being reclaimed and nothing was going to change that on its own.

ANALYZE is the part that still happens, because autovacuum_analyze_scale_factor defaults to 0.1 rather than 0.2 and counts inserts as well as updates and deletes. That is a useful asymmetry to remember when a table looks healthy in pg_stats and has never been vacuumed in its life.

For a big, slowly-churning table, 0.2 is the wrong default. A per-table autovacuum_vacuum_scale_factor = 0.01 is the standard remedy. One caveat if you run Patroni: setting it through ALTER SYSTEM survives until the next restart or failover, because Patroni rewrites postgresql.conf from its own configuration. Cluster-wide values go through patronictl edit-config, and per-table ones through ALTER TABLE ... SET, which lives in the catalog and is safe.

How to measure bloat instead of guessing at it

pgstatindex from the pgstattuple extension reports avg_leaf_density, and the number is directly interpretable because you know what a healthy value is. The CREATE INDEX docs state it: “B-trees use a default fillfactor of 90”. A rebuilt index packs its leaves to 90% and starts drifting down from there.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT i.indexrelname,
       pg_size_pretty(pg_relation_size(i.indexrelid)) AS sz,
       round(s.avg_leaf_density::numeric, 1) AS density
FROM (SELECT indexrelid, indexrelname
      FROM pg_stat_user_indexes
      ORDER BY pg_relation_size(indexrelid) DESC
      LIMIT 20) i
JOIN pg_class c ON c.oid = i.indexrelid
JOIN pg_am am ON am.oid = c.relam AND am.amname = 'btree',
LATERAL pgstatindex(i.indexrelid) s
ORDER BY s.avg_leaf_density ASC;

Two things in that query are load-bearing. The amname = 'btree' filter is mandatory, because pgstatindex is documented as returning “information about a B-tree index” and gives you an error or nonsense on anything else. The LIMIT 20 before the join matters because pgstatindex reads the index in full, so this is a scan of every byte of the twenty largest indexes and not a catalog lookup. Run it on a replica where you can. The files are identical and the primary does not pay for it.

What came back:

        indexrelname          |   sz    | density
------------------------------+---------+---------
 batch_cache_period_idx       | 4416 MB |     0.6
 batch_result_ref_idx         | 9528 MB |    11.2
 batch_result_owner_idx       | 7810 MB |    11.3
 batch_result_uniq            | 6912 MB |    12.8
 batch_result_pkey            | 3140 MB |    42.0
 batch_result_link_idx        |  980 MB |    88.6

The bottom row is the control. One index on the same table sits at 88.6, which is where a b-tree lives when nothing pathological is happening to it, and it confirms the other five are not a measurement artifact. The top row is 4.4 GB of index structure at 0.6% density, pointing at a table with no rows in it.

An index at 11% density is a filing cabinet with one sheet of paper in each drawer. The filing is correct and the lookups return the right answer. You just walk nine drawers to find one page, and the cabinet takes nine times the floor space it needs.

Predicting the payoff before you spend the maintenance window

Multiply current size by current density and divide by 90. Since a rebuild targets fillfactor 90 by definition, that ratio is the compaction you should expect, give or take fragmentation and the fixed overhead of a small index.

The formula lands within a couple of percent everywhere except the first row, and the first row is instructive. At 0.6% density the prediction is 29 MB and the result is 54 MB, because once the payload approaches nothing you are measuring the metapage, the root and a handful of leaves rather than data. The model is good in the range where the decision is actually hard.

That is enough to walk into a change window with a number instead of a hope. Summed over both tables it predicted around 28 GB of recovery, which is the difference between a ticket for next quarter and a maintenance slot tonight.

REINDEX, not VACUUM FULL

REINDEX rebuilds one index at a time, so peak extra space is the size of one new index rather than one new table.

1
2

REINDEX INDEX <name>;                -- fast, takes ACCESS EXCLUSIVE
REINDEX INDEX CONCURRENTLY <name>;   -- no write lock, slower, more WAL

REINDEX CONCURRENTLY has been available since PostgreSQL 12, which is why pg_repack was not worth installing here. An external tool for a one-off operation that the server can do natively is extra supply chain for no gain.

The trade-off is narrow and worth stating outright. Plain REINDEX is faster and has no failure debris, and on a staging box that is the obvious choice. On a primary serving traffic only CONCURRENTLY is defensible, and you pay for it in runtime, in WAL volume, and in cleanup when it fails. If the heap is the bloated part, VACUUM FULL is right and REINDEX is the wrong answer. Check which one you have first.

The zero-scan index that was holding up the constraints

While the rebuild plan was being written, the tempting side quest appeared: the hot table carried eight indexes and five of them showed idx_scan = 0. Dropping five unused indexes would have solved the disk problem outright.

Two of those five were unique. The monitoring docs define idx_scan as the “Number of index scans initiated on this index”, and a uniqueness check on INSERT is not an index scan in that sense. It does not move the counter. Those two indexes were being consulted on every single insert into the table and their statistics said they had never been touched since the counters were last reset.

Dropping them would have let duplicates into the results of a financial recalculation, silently, with no error at the time of the mistake. This was caught by a rule rather than by a metric: verify the business meaning of every index before dropping it. The metric did not know what the index was for.

The query, with the filter that makes it safe:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

SELECT i.relname AS tbl, i.indexrelname AS idx,
       pg_size_pretty(pg_relation_size(i.indexrelid)) AS sz, i.idx_scan
FROM pg_stat_user_indexes i
JOIN pg_index x ON x.indexrelid = i.indexrelid
WHERE i.idx_scan = 0
  AND NOT x.indisunique
  AND pg_relation_size(i.indexrelid) > 5 * 1024 * 1024
ORDER BY pg_relation_size(i.indexrelid) DESC;

-- a zero means nothing without knowing the window it was counted over
SELECT stats_reset, now() - stats_reset AS window
FROM pg_stat_database WHERE datname = current_database();

Run the second query first. An idx_scan = 0 over a statistics window that was reset last Tuesday is not evidence of anything, and here the window was over half a year, which is what made the zeros worth investigating in the first place.

Statistics counters live on each node separately

A pg_stat_* counter describes the node you read it from, not the cluster. Replicas keep their own, so an index that the primary has never scanned may be serving every read on the standby.

I checked the primary and one standby taken from the inventory file, then had to redo the reasoning properly: the list of nodes has to come from pg_stat_replication on the primary, not from an inventory that may be out of date. The conclusion held, but it had not been proved until the database itself confirmed how many subscribers there were. On a two-node cluster that distinction is easy to wave away, and waving it away is exactly how a busy index gets dropped.

What bites you after the decision is made

Five things caught me on the way through, in rough order of how much time each cost.

_ccnew and _ccold mean opposite things. A failed REINDEX CONCURRENTLY leaves an invalid index behind, and the REINDEX docs distinguish the two cases carefully. A _ccnew suffix is the transient index from a run that failed, so drop it and retry. A _ccold suffix is the original, which means the rebuild succeeded and only the cleanup failed, so drop it and you are done. The docs also note that “A nonzero number may be appended to the suffix of the invalid index names to keep them unique, like _ccnew1, _ccold2”. Any automation should check for leftovers as its first step:

1
2
3

SELECT c.relname
FROM pg_index i JOIN pg_class c ON c.oid = i.indexrelid
WHERE NOT i.indisvalid;

A synchronous replica turns REINDEX into application latency. A rebuild generates WAL on the order of the size of the index it produces. With synchronous_commit = on, every commit waits for the standby to confirm, and with wal_compression off that whole volume crosses the network uncompressed. Rebuild one index at a time, off-peak, checking replication lag between steps.

Laravel wraps migrations in a transaction, and only on PostgreSQL. DROP INDEX CONCURRENTLY inside a migration fails with cannot run inside a transaction block, and nothing in the migration file explains why. The reason is three files deep in the framework. Migration.php declares public $withinTransaction = true;, Migrator.php wraps the call when supportsSchemaTransactions() && $migration->withinTransaction, and PostgresGrammar.php sets protected $transactions = true;. The MySQL grammar does not override it, so it inherits false from the base class. The same migration passes on MySQL and fails on PostgreSQL. The fix is one line in the migration class:

1

public $withinTransaction = false;

That property is not in the Laravel documentation. I found it by reading the source, which is generally the faster route once a framework behaviour has no documented name.

Indexes dropped by hand come back. If the drop happened as ad-hoc SQL while the declaration is still sitting in a migration or a test schema, the next deploy to a fresh environment restores it. Grep the repository for the index name before dropping anything, and do the drop as a migration.

Two indexes with different names can be the same index. Matching column lists is not enough to prove it, and differing column order in a UNIQUE constraint does not prove the opposite, because for uniqueness purposes the order of columns means nothing. Compare pg_index properly: indclass, indcollation, indoption, indnullsnotdistinct, indnkeyatts to catch a hidden INCLUDE, and indpred and indexprs for partial and expression indexes. When every one of those matches, it is a duplicate, and you can drop the one with a billion scans on it: the planner moves to the twin without noticing.

What it bought

One detour on the way, for anyone who hits the same thing: after the rebuilds, pg_wal had grown and df had not moved as much as the arithmetic promised. Forcing a CHECKPOINT did nothing, and it should not have. The directory had grown to the configured max_wal_size ceiling, which is the setting doing its job rather than a leak, and PostgreSQL keeps recycled segments preallocated and shrinks back toward min_wal_size gradually. Chasing those gigabytes is wasted time.

The more interesting result was on the production cluster, where the same profile showed up on indexes that are actually hot:

The first row is the one that changed how I think about this. It is a 1.4 GB index at 14% density taking hundreds of millions of scans, and every one of those scans reads pages that are one seventh full. Rebuilt, it lands near 220 MB and fits in cache whole. The disk saving is real but secondary. The alert said “disk”, and the actual finding was a read path doing seven times the I/O it needed to.

Bottom line

Monitor index density, not free disk space. By the time a disk alert fires, the indexes have been degrading for months and the query latency has been degrading with them, quietly, in a way no threshold is watching. A weekly avg_leaf_density check over the twenty largest b-trees costs one scan on a replica and tells you months in advance.

Then treat the rebuild as maintenance rather than an incident response. B-tree pages not merging is not a bug and not a missing feature: it is a documented trade that buys you a VACUUM which never locks the table. Workloads that delete most of each key range pay for that trade, and the payment is a periodic REINDEX. Fix the workload if you can, because TRUNCATE instead of DELETE on a cache table removes a whole class of this in one line, and schedule the rebuild if you cannot.