Postgres Index Bloat: Detecting and Fixing It Before It Hurts
KU
Kiril Urbonas
last week • 4 min read•0 views
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.
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.
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 but doesn't shrink or rebalance the physical index file. 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.
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%. 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.
The old advice was REINDEX, which takes an ACCESS EXCLUSIVE lock and blocks writes and reads for the duration. On a 40GB index 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, only taking a brief lock at the very start and end, then swaps them. It's slower in wall-clock time and 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 and leave an invalid index named orders_pkey_ccnew; drop those leftovers with DROP INDEX CONCURRENTLY. It also can't run inside a transaction block, so don't wrap it. And it won't help if the bloat keeps coming back, which brings up the real fix.
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 scale_factor of 0.2 means vacuum waits until 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. Also consider whether the churning column needs to be indexed at all, and set a fillfactor below 100 (say 85) on hot-update tables so HOT updates can stay on-page and skip touching the index entirely.
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.
Get the latest tutorials, guides, and insights on AI, DevOps, Cloud, and Infrastructure delivered directly to your inbox.
Least privilege fails when it's a one-time audit that locks things down until something breaks, then gets reverted. The iterative, log-driven approach that tightens permissions safely — and the policies we stopped writing by hand.
A bad deploy used to mean a pager at 2am and a manual rollback. Now Argo Rollouts watches the error rate and aborts the canary itself before anyone wakes up.
Explore more articles in this category
The Backstage demo always wows leadership. Then six months later the catalog has 400 stale entries and nobody trusts it. Here's what got ours to actually stick.
We inherited 200-odd AWS resources built by hand over four years, with no state file anywhere. Here's how import blocks and a generation workflow got them under Terraform without a rebuild.
A single ALTER TABLE took a lock and stalled every write for 40 seconds during peak traffic. Expand-contract is how we stopped shipping outages.
Evergreen posts worth revisiting.
