--- title: "PostgreSQL Index Bloat Surgery: Zero-Downtime Rebuilds on Live SaaS Tables" published: true description: "Detect and rebuild bloated PostgreSQL indexes on live SaaS tables using pgstattuple and REINDEX CONCURRENTLY — no downtime, no table locks." tags: postgresql, devops, cloud, performance canonical_url: https://mvpfactory.dev/blog/postgresql-index-bloat-zero-downtime-rebuild --- ## What you will learn  By the end of this tutorial you will be able to triage index bloat on a live production database, measure real fragmentation with `pgstattuple`, and rebuild bloated indexes at 50M+ rows without touching a table lock. This is surgery — not a quick fix. ## Prerequisites  - PostgreSQL 12+ (required for `REINDEX CONCURRENTLY`) - Superuser or `pg_monitor` access - A write-heavy table with 10M+ rows to make this worth your time --- ## Why your queries are still slow  Your autovacuum is running. Your queries are still slow. The culprit is probably not the heap — it is the indexes. PostgreSQL's MVCC model lets dead tuples linger until `VACUUM` reclaims them. Autovacuum cleans the heap, but **index pages are never compacted**. On a write-heavy table — orders, events, audit logs — you end up with a swelling B-tree full of wasted pages. A healthy index uses 70%+ of its leaf pages for live data. A bloated one might use 40%. Most teams compound this by adding more indexes when queries slow down. The indexes themselves become the problem. --- ## Step 1: Triage with pg_stat_user_indexes  Fast, zero-cost estimation. No extension required.


sql
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 Here is the minimal setup to get this working. Install the extension once per database:


sql
CREATE EXTENSION pgstattuple;

 Then run:


sql
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 | |---|---|---| | `avg_leaf_density` | > 70% | < 50% | | `leaf_fragmentation` | < 10% | > 30% | | `free_space_pct` | < 15% | > 40% | Note: `free_space` from `pgstatindex` is raw bytes — you derive the ratio yourself, as the query shows. Anything below 50% `avg_leaf_density` is costing you query throughput. --- ## Step 3: REINDEX CONCURRENTLY — the safe rebuild Let me show you a pattern I use in every project. Classic `REINDEX` acquires an `AccessExclusiveLock`. On a 50M-row table, that is minutes of downtime. `REINDEX CONCURRENTLY`, introduced in PostgreSQL 12, builds a new index alongside the old one:


sql
-- Rebuild a single index
REINDEX INDEX CONCURRENTLY idx_orders_user_id;

-- Or rebuild everything on a table
REINDEX TABLE CONCURRENTLY orders;

 Under the hood: a new index is built in the background, the old one stays live and queryable, then after validation the new index is atomically swapped in and the old one dropped. The cost is 2–3x longer build time — accept it as the price of zero downtime. Track progress live during the operation:


sql
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. --- ## The 50M+ row strategy 1. **Identify** 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 4. **Rebuild one index at a time** — concurrent rebuilds compete for I/O 5. **Monitor** `pg_stat_progress_create_index` throughout Cloud block storage with burst credits is particularly unforgiving here — schedule rebuilds before credits deplete, not during the burst window. --- ## Gotchas **`pgstattuple` does a full index scan internally.** Run it during off-peak hours on large tables — it is not a free query. **Never run `REINDEX` without `CONCURRENTLY` on production.** The docs do not emphasize this enough, but `REINDEX INDEX` on a live table takes an `AccessExclusiveLock` and will block reads and writes for the duration. **Rebuilding does not prevent re-bloat.** Pair this with autovacuum tuning — reduce `autovacuum_vacuum_scale_factor` on high-churn tables. Otherwise you are mopping the floor with the tap still running. *(Speaking of compounding overhead — during a long rebuild session I keep [HealthyDesk](https://play.google.com/store/apps/details?id=com.healthydesk) running for desk exercise reminders. Forty-minute index rebuilds are surprisingly good for forced movement breaks.)* --- ## 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** — accept the 2–3x build time as the price of zero downtime. 3. **Pair bloat remediation with autovacuum tuning** to prevent accumulation in the first place. Index bloat is a slow tax on every query. Pay it down before it starts compounding.