---
title: "PostgreSQL index bloat: the silent query killer at scale"
published: true
description: "MVCC causes index bloat that silently kills query performance. Learn to detect bloat with pgstattuple, tune autovacuum, and when to use REINDEX CONCURRENTLY."
tags: postgresql, architecture, devops, performance
canonical_url: https://blog.mvpfactory.co/postgresql-index-bloat-silent-query-killer
---
## What we are building
By the end of this workshop, you will know how to detect PostgreSQL index bloat before it becomes a production incident, tune autovacuum per-table for high-write workloads, and execute a safe, non-blocking index rebuild when bloat has already accumulated. Let me show you a pattern I use in every project.
## Prerequisites
- PostgreSQL 12+ (REINDEX CONCURRENTLY requires it)
- Access to `pg_stat_user_tables` and ability to install extensions
- Basic comfort with SQL and `ALTER TABLE`
---
## The scale problem nobody warns you about
In Notion's 2021 public postmortem on their database migration, one theme kept surfacing: foundational assumptions that held at startup scale quietly broke under production load. The failure modes weren't dramatic — they were gradual degradations that looked like other problems until someone dug deep enough.
PostgreSQL index bloat follows the same pattern. On a small dataset, you never notice it. At scale — high-write workloads, frequent UPDATEs, tens of millions of rows — it becomes the silent culprit behind P95 latency spikes that are often misattributed to load increases or application regressions.
In my experience building production systems at scale, index bloat is one of the most underdiagnosed performance issues in the PostgreSQL ecosystem. (It is also the kind of slow-burn problem that compounds during the long hours you think you are just babysitting a stable system — I keep [HealthyDesk](https://play.google.com/store/apps/details?id=com.healthydesk) running during those sessions so at least my wrists survive the on-call shift.)
---
## Step 1 — Understand how MVCC creates bloat
PostgreSQL's MVCC model never overwrites rows in place. Every UPDATE creates a new row version and marks the old one as a dead tuple. Indexes hold references to all versions — alive and dead — until VACUUM reclaims them.
The math here is unforgiving. On a table with 10 million rows receiving 50k updates per day, you accumulate millions of dead tuple references within weeks. Each dead reference is wasted I/O on every index scan.
---
## Step 2 — Measure bloat with the right tool for the job
Two approaches, different trade-offs:
| Method | Accuracy | Lock impact | Speed |
|---|---|---|---|
| `pgstattuple` extension | High | Full table read | Slow on large tables |
| Bloat estimation query | Approximate ±5–15% | None | Fast |
| `pg_stat_user_tables` dead_tup | Low (indicative) | None | Instant |
Here is the minimal setup to get this working. Start with the fast triage query — it runs in milliseconds and identifies candidates without touching your tables:
sql
-- Fast triage: identify bloat candidates (indicative, not precise)
SELECT
schemaname, tablename,
pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS total_size,
ROUND(
(n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0)) * 100, 2
) AS dead_ratio_pct
FROM pg_stat_user_tables
WHERE n_live_tup > 10000
ORDER BY dead_ratio_pct DESC NULLS LAST
LIMIT 20;
A `dead_ratio_pct` above 10–15% warrants deeper investigation. Above 30% is a four-alarm fire. Once you have confirmed suspects, reach for `pgstattuple` for precise analysis:
sql
-- Requires: CREATE EXTENSION pgstattuple;
SELECT * FROM pgstatindex('idx_orders_user_id');
-- Key fields: leaf_fragmentation, avg_leaf_density
An `avg_leaf_density` below 50% means your index is at least half empty — you're doing twice the I/O you need to.
---
## Step 3 — Tune autovacuum per table
Most teams get autovacuum wrong the same way: the defaults are tuned for small-to-medium workloads. At scale, you need per-table overrides.
sql
-- Per-table autovacuum tuning for high-write tables
ALTER TABLE orders SET (
autovacuum_vacuum_scale_factor = 0.01, -- Trigger at 1% dead tuples (default: 20%)
autovacuum_vacuum_cost_delay = 2, -- ms between I/O bursts (default: 20ms)
autovacuum_vacuum_threshold = 500 -- Min rows before triggering (default: 50)
);
`scale_factor` is the most critical lever. At the default 20%, a 50M-row table requires 10 million dead tuples before autovacuum triggers. Drop it to 1–2% for write-heavy tables.
`cost_delay` controls vacuum aggressiveness. Lower values consume more I/O — useful for off-peak maintenance windows, but tune against your disk headroom during peak traffic.
---
## Step 4 — Rebuild with REINDEX CONCURRENTLY when bloat has already accumulated
The docs do not mention this prominently enough, but autovacuum reclaims dead tuple references — it doesn't compact index pages. If `avg_leaf_density` is already below 50% on `pgstattuple`, you need to rebuild:
sql
-- Non-blocking index rebuild (requires PostgreSQL 12+)
REINDEX INDEX CONCURRENTLY idx_orders_user_id;
`CONCURRENTLY` avoids the table lock that makes standard `REINDEX` a production incident. It requires more disk space temporarily (two index copies exist briefly) and runs slower. Plan accordingly.
For tables where even a brief lock is unacceptable, build a new index concurrently, swap it in, and drop the old one — the manual two-phase approach that gives you full control over timing and rollback.
---
## Gotchas
**`scale_factor` at default 20% is dangerously permissive.** On any table over 1M rows with frequent updates, this is essentially "never vacuum in time." Override it immediately.
**REINDEX CONCURRENTLY needs disk headroom.** Two copies of the index exist briefly. If your disk is near capacity, you will hit a failed rebuild mid-operation — at the worst possible moment.
**`pg_stat_user_tables` dead tuple counts reset on restart.** Use them as a directional signal only. Always confirm with `pgstattuple` before making rebuild decisions.
**Bloat on expression indexes is invisible to basic queries.** `pgstatindex` by index name is the only reliable path for those.
---
## What to do now
Add dead tuple ratio to your monitoring stack. The triage query above runs in milliseconds. Flag candidates at 15%, confirm with `pgstattuple` before escalating. Catching bloat early means autovacuum tuning is enough — you avoid the REINDEX window entirely.
Set per-table autovacuum overrides on every high-write table. Drop `autovacuum_vacuum_scale_factor` to 0.01–0.02 for tables with more than 1M rows and frequent updates. The default 20% is dangerously permissive at scale.
Treat REINDEX CONCURRENTLY as a planned operation, not emergency surgery. Schedule it quarterly on your bloatiest indexes, off-peak, with disk space headroom monitored. Reacting to a production regression is the worst time to learn its operational requirements.
**Further reading:**
- [PostgreSQL VACUUM documentation](https://www.postgresql.org/docs/current/routine-vacuuming.html)
- [pgstattuple extension reference](https://www.postgresql.org/docs/current/pgstattuple.html)
- [REINDEX CONCURRENTLY docs](https://www.postgresql.org/docs/current/sql-reindex.html)
Top comments (0)