PostgreSQL Index Bloat: Zero-Downtime Rebuild at Scale

SEO Meta Description: Detect and rebuild bloated PostgreSQL indexes on live SaaS tables using pgstattuple and REINDEX CONCURRENTLY — no downtime, no table locks.

Tags: backend saas microservices devops cloud

---

TL;DR

Index bloat silently degrades query performance on live SaaS tables. Use pgstattuple to measure real bloat, pg_stat_user_indexes for quick triage, and REINDEX CONCURRENTLY to rebuild without taking a table lock. At 50M+ rows, this is surgery — not a quick fix.

---

Why this matters right now

Your autovacuum is running. Your queries are still slow. The culprit is probably not the heap — it’s the indexes.

Most teams get this wrong. They notice slow queries. They add more indexes. The indexes themselves become the problem.

At 50M+ rows on a write-heavy SaaS table — orders, events, audit logs — index bloat isn’t hypothetical. It’s a compounding tax on every query that touches those tables. The longer you ignore it, the more I/O you waste, and the harder it becomes to separate bloat overhead from genuine growth in query complexity.

---

Understanding index bloat

PostgreSQL’s MVCC model means dead tuples linger until VACUUM reclaims them. Indexes hold references to those dead tuples too — and while autovacuum cleans the heap, index pages are never compacted. The result: a swelling B-tree with wasted pages, inflated scan costs, and cache thrashing.

A healthy index uses 70%+ of its leaf pages for live data. A bloated one might use 40%.

---

Step 1: Triage with pg_stat_user_indexes

Fast, zero-cost estimation. No extension required.

SELECT
  schemaname,
  tablename,
  indexrelname,
  idx_scan,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 20;

Low idx_scan + large index_size = candidate for investigation. This tells you where to look, not how bad it is.

---

Step 2: Measure real bloat with pgstattuple

Install the extension once per database:

CREATE EXTENSION pgstattuple;

Then run:

SELECT
  indexrelid::regclass AS index_name,
  stat.leaf_fragmentation,
  stat.avg_leaf_density,
  stat.free_space,
  pg_relation_size(indexrelid) AS index_size_bytes,
  round(100.0 * stat.free_space / nullif(pg_relation_size(indexrelid), 0), 1)
    AS free_space_pct
FROM pg_index
JOIN LATERAL pgstatindex(indexrelid::regclass) stat ON true
WHERE indrelid = 'your_large_table'::regclass
ORDER BY stat.leaf_fragmentation DESC;

Metric	Healthy	Action needed	Notes
avg_leaf_density	> 70%	< 50%	Direct pgstatindex output
leaf_fragmentation	< 10%	> 30%	Direct pgstatindex output
free_space_pct	< 15%	> 40%	Computed: free_space / index_size * 100

free_space from pgstatindex is raw bytes — you derive the ratio yourself, as the query above shows. Anything below 50% avg_leaf_density is costing you query throughput.

One warning: pgstattuple does a full index scan internally. Run it during off-peak hours on large tables.

---

Step 3: REINDEX CONCURRENTLY — the safe rebuild

Classic REINDEX acquires an AccessExclusiveLock. On a 50M-row table, that means minutes of downtime. REINDEX CONCURRENTLY, introduced in PostgreSQL 12, builds a new index alongside the old one:

-- PostgreSQL 12+
REINDEX INDEX CONCURRENTLY idx_orders_user_id;

-- Or rebuild all indexes on a table
REINDEX TABLE CONCURRENTLY orders;

What happens under the hood:

1. A new index is built in the background using CREATE INDEX CONCURRENTLY semantics
2. The old index stays live and queryable during the build
3. After validation, the new index is atomically swapped in
4. The old index is dropped

Method	Lock level	Safe for production	Build time
REINDEX INDEX	AccessExclusiveLock	No (downtime)	Fastest
REINDEX INDEX CONCURRENTLY	ShareUpdateExclusiveLock	Yes	~2–3x longer
CREATE INDEX CONCURRENTLY + swap	ShareUpdateExclusiveLock	Yes	~2–3x longer

---

The 50M+ row strategy

In my experience with production systems at this row count, the approach is:

1. Identify candidates via pg_stat_user_indexes (index size > 1GB, low scan count)
2. Measure precisely with pgstattuple — target leaf_fragmentation > 25%
3. Prioritize indexes on write-heavy tables (orders, events, audit logs)
4. Rebuild one index at a time — concurrent rebuilds compete for I/O
5. Monitor pg_stat_progress_create_index during the operation

-- Track progress live
SELECT phase, blocks_done, blocks_total,
       round(100.0 * blocks_done / nullif(blocks_total, 0), 1) AS pct
FROM pg_stat_progress_create_index;

Expect a 50GB index to take 20–60 minutes depending on I/O throughput. Cloud block storage with burst credits is particularly unforgiving here — schedule rebuilds before credits deplete, not during the burst window.

---

Conclusion

Three things worth doing this week:

1. Schedule monthly pgstattuple audits on your top 10 write-heavy tables. Automate alerting when leaf_fragmentation exceeds 25% or avg_leaf_density drops below 50%.

2. Never run REINDEX without CONCURRENTLY on production — always use the concurrent variant on live tables, and accept the 2–3x build time as the price of zero downtime.

3. Pair bloat remediation with autovacuum tuning — reduce autovacuum_vacuum_scale_factor on high-churn tables to prevent bloat from accumulating in the first place.

Index bloat is a slow tax on every query. Pay it down before it starts compounding.