Postgres Index Bloat: Detecting and Fixing It Before It Hurts
A 40GB index on a 6GB table was the first sign. Queries were fine until they weren't. Here's how we found the bloat and cleared it with zero downtime.
Key takeaways
- Measure Postgres index bloat with pgstatindex from the pgstattuple extension and watch avg_leaf_density, which sits near 90 percent on a fresh B-tree.
- Rebuild bloated indexes online with REINDEX INDEX CONCURRENTLY, never plain REINDEX on a live primary.
- Then lower the per-table autovacuum scale factor and table fillfactor so the bloat does not come back.
On this page
Detect Postgres index bloat with pgstatindex() from the pgstattuple extension: a B-tree's avg_leaf_density should sit near 90% (the default B-tree fillfactor), and anything under about 50% is bloated. Fix it online with REINDEX INDEX CONCURRENTLY, then stop it recurring with per-table autovacuum settings and a lower table fillfactor.
Verified against
PostgreSQL 18 (18.6 docs), against the postgresql.org REINDEX, pgstattuple, CREATE INDEX and autovacuum configuration pages. Checked October 2026.
The orders table was 6GB. Its primary key index was 40GB. Nobody noticed until a routine pg_dump started taking twice as long and the storage alert fired at 85%. The table had a hot update pattern (status column flipping through pending, paid, shipped) and years of churn had inflated the index into something absurd. The queries still worked, which is exactly why it went unnoticed for so long. Bloat rarely announces itself; it just slowly eats disk and cache.
Why indexes bloat under MVCC
Postgres never updates a row in place. An UPDATE writes a new tuple version and marks the old one dead, and VACUUM reclaims the dead space later. For heaps that mostly works. For B-tree indexes it's messier: even with HOT (heap-only tuple) updates, any update that changes an indexed column, or that can't fit on the same page, adds a new index entry. Autovacuum removes dead pointers and can recycle fully empty pages, but it doesn't compact half-empty pages or shrink the physical index file. (Our autovacuum tuning notes from production stalls cover the heap side of the same problem.) Over time you get pages that are half empty, and the index on disk is far larger than the live data justifies.
A table that's mostly append-only barely bloats. A table with hot, frequent updates on indexed columns bloats fast. Ours was the second kind.
Measuring it honestly
Do not trust the eyeball estimate queries that float around blogs; they're often wrong by a wide margin because they guess at row widths. Install pgstattuple, which reads the actual pages.
CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT
round(100 * (1 - avg_leaf_density / 100.0), 1) AS approx_wasted_pct,
avg_leaf_density,
leaf_pages,
pg_size_pretty(pg_relation_size('orders_pkey')) AS idx_size
FROM pgstatindex('orders_pkey');
avg_leaf_density is the useful number. A freshly built B-tree sits around 90%, because B-trees default to a fillfactor of 90. Ours read 14%, meaning leaf pages were mostly empty air. That maps directly to the 40GB-on-6GB horror. For a quick fleet-wide scan without the per-index cost of pgstatindex, this catch-all flags suspects:
SELECT schemaname, relname, indexrelname,
pg_size_pretty(pg_relation_size(indexrelid)) AS size,
idx_scan
FROM pg_stat_user_indexes
JOIN pg_index USING (indexrelid)
WHERE pg_relation_size(indexrelid) > 1e9
ORDER BY pg_relation_size(indexrelid) DESC;
Any large index with a low idx_scan is a double win to look at: possibly bloated and possibly unused.
Fixing it without taking the app down
The old advice was plain REINDEX, which blocks writes on the table and takes an ACCESS EXCLUSIVE lock on the index, so any query that wants that index waits too. On a 40GB primary key that's an outage. Since Postgres 12 the answer is REINDEX CONCURRENTLY:
REINDEX INDEX CONCURRENTLY orders_pkey;
It builds a new index alongside the old one under a SHARE UPDATE EXCLUSIVE lock, which blocks schema changes but not reads or writes, then swaps them. The docs are explicit that it does two table scans per index and waits for transactions that might use the index, so it's slower in wall-clock time. It also uses extra disk (you need room for both copies simultaneously, so check free space first), but the app keeps serving. Our rebuild took 22 minutes and the resulting index was 5.1GB, down from 40. Cache hit ratio on that table jumped because the index finally fit in shared buffers.
A few sharp edges. REINDEX CONCURRENTLY can fail partway (a uniqueness violation is the classic cause) and leave an INVALID index named orders_pkey_ccnew. Invalid indexes are ignored by queries but still slow every write, so find and drop them:
SELECT indexrelid::regclass AS leftover
FROM pg_index
WHERE NOT indisvalid;
DROP INDEX CONCURRENTLY orders_pkey_ccnew;
A leftover ending in _ccold means the rebuild succeeded and only the old copy failed to drop, so dropping it is safe. The command also can't run inside a transaction block, so don't wrap it, and exclusion-constraint indexes can't be rebuilt concurrently at all. And it won't help if the bloat keeps coming back, which brings up the real fix.
Stopping the recurrence
Rebuilding is treating the symptom. For hot-update tables, tune autovacuum to run more aggressively on that table specifically:
ALTER TABLE orders SET (
autovacuum_vacuum_scale_factor = 0.02,
autovacuum_vacuum_cost_limit = 2000
);
The default autovacuum_vacuum_scale_factor of 0.2 (plus a 50-row threshold) means vacuum waits until roughly 20% of the table is dead before running, which on a busy table is far too lazy. Dropping it to 0.02 makes vacuum keep up. The default autovacuum_vacuum_cost_limit is -1, which falls back to vacuum_cost_limit of 200 shared across workers, so raising it per table lets vacuum finish instead of sleeping. PostgreSQL 18 also added autovacuum_vacuum_max_threshold (default 100,000,000 tuples) to cap the trigger on huge tables. Also consider whether the churning column needs to be indexed at all, and set a table fillfactor below its default of 100 (say 85) on hot-update tables so HOT updates can stay on-page and skip touching the index entirely. Changing fillfactor only affects pages written afterwards, so pair it with a rebuild. If dropping an index is part of the fix, do it as one step of an expand-and-contract migration rather than a hot change on the primary.
Questions people ask
How do I check index bloat in PostgreSQL?
Install pgstattuple with CREATE EXTENSION pgstattuple; and run SELECT avg_leaf_density FROM pgstatindex('your_index');. A healthy, freshly built B-tree reads near 90%. Below about 50% the index is mostly empty space and worth rebuilding. Estimation queries based on pg_class statistics are cheaper but guess at row widths, so confirm any suspect with pgstatindex before scheduling work.
Does VACUUM FULL or autovacuum fix index bloat?
Autovacuum does not shrink an index; it removes dead entries and recycles empty pages, but half-empty pages stay half empty. VACUUM FULL rewrites the table and its indexes, but it holds an ACCESS EXCLUSIVE lock for the whole run, which blocks reads and writes. For a live system, REINDEX INDEX CONCURRENTLY is the tool that actually compacts the index without an outage.
Is REINDEX CONCURRENTLY safe on a production primary?
Yes, with two caveats. It needs enough free disk for a second copy of the index, and it can fail and leave an INVALID index named with a _ccnew suffix that you must drop before retrying. It cannot run inside a transaction block. Reads and writes continue throughout; only DDL on the table is blocked until it finishes.
The call we'd make
Add pgstatindex density to your monitoring and alert when any large index drops below 50%. Rebuild with REINDEX CONCURRENTLY during a low-traffic window, never plain REINDEX on a live primary. Then fix the cause: lower the per-table autovacuum scale factor on your hot tables and set a sensible fillfactor. Rebuilding once and walking away just means you'll be back here in six months with the same 40GB surprise.
Sources
Cite this article
Kiril Urbonas (2026). Postgres Index Bloat: Detecting and Fixing It Before It Hurts. DevOpsNess. Updated 2026-10-31. https://www.devopsness.com/blog/postgres-index-bloat-detecting-and-fixing
[Postgres Index Bloat: Detecting and Fixing It Before It Hurts](https://www.devopsness.com/blog/postgres-index-bloat-detecting-and-fixing) (Kiril Urbonas, DevOpsNess, updated 2026-10-31)
More from Infrastructure
Explore more articles in this category
Database Migrations Without Downtime: Patterns From Three Real Cutovers
How we shipped three schema migrations with zero customer impact. Expand-then-contract, dual-writes, and the rollback plan we never had to use — but tested anyway.
Observability: Correlating Logs, Metrics, and Traces in Anger
The "three pillars" framing misses the point. What matters is correlating across them: the patterns that earn their place and the tooling decisions that pay back.
Terraform Modules Best Practices: Building Reusable Infrastructure
We have a private module registry with ~25 modules used across 12 accounts. Versioning, interface design, and the over-modularization mistake we keep making.
You might have missed
Evergreen posts worth revisiting.