Detecting and Fixing PostgreSQL Index Bloat with REINDEX CONCURRENTLY

PostgreSQL index bloat is the silent tax on every query: B-tree indexes that have inflated to two or three times their necessary size force more page reads per lookup, evict useful pages from shared buffers, stretch backup and replica-rebuild times, and inflate your storage bill. Unlike table bloat, PostgreSQL index bloat routinely survives regular vacuuming — vacuum removes dead index entries but B-tree pages rarely merge back together, so the structure keeps its high-water mark. This guide covers how we measure PostgreSQL index bloat with real evidence rather than estimation folklore, why it happens, and how to rebuild safely in 24×7 production using REINDEX CONCURRENTLY on PostgreSQL 12+.

PostgreSQL Index Bloat: Key Takeaways

• Measure PostgreSQL index bloat with pgstatindex, never with catalog-only estimates.
• Rebuild online with REINDEX CONCURRENTLY once average leaf density falls under 60% on a large index; REINDEX CONCURRENTLY keeps reads and writes running throughout.
• Remove vacuum horizon blockers first, or PostgreSQL index bloat returns within days of every rebuild.

Symptoms That Point at PostgreSQL Index Bloat

PostgreSQL index bloat rarely announces itself. It shows up as: index-only scans and range scans reading progressively more buffers for the same logical work (visible as rising shared_blks_read/shared_blks_hit per call in pg_stat_statements for stable queries); indexes larger than their tables; cache hit ratio decaying with no workload change; and step-function improvements after a restore into a fresh cluster — the "why is staging faster than production with the same data?" question. Any of these justifies a measurement pass.

Measure PostgreSQL Index Bloat, Don’t Estimate It

The popular catalog-only "bloat estimation" queries (btree density heuristics over pg_class/pg_statistic) are fine for a first-pass fleet ranking but routinely mis-estimate by 2x on wide keys, expression indexes, and non-default fillfactors. For decisions, use pgstattuple's B-tree function, which walks the actual index:

CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT
    i.indexrelid::regclass                        AS index_name,
    pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size,
    s.avg_leaf_density,
    s.leaf_fragmentation
FROM pg_index AS i,
LATERAL pgstatindex(i.indexrelid) AS s
WHERE i.indexrelid::regclass::TEXT LIKE 'public.%'
ORDER BY pg_relation_size(i.indexrelid) DESC
LIMIT 20;

Interpretation anchors: a freshly built B-tree with default fillfactor = 90 shows avg_leaf_density around 90. Density below ~50–60% on a large index is material bloat — roughly, a 55% density index is doing almost double the page reads it needs. leaf_fragmentation above ~30–40% degrades range-scan locality specifically. pgstatindex reads the whole index, so run it off-peak or on a physical replica; a cheap continuous proxy is simply trending pg_relation_size() per index against row counts and alerting on divergence.

Before rebuilding anything, check whether the index deserves to exist at all:

SELECT
    schemaname,
    relname,
    indexrelname,
    idx_scan,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

An unused 40 GB bloated index should be dropped, not rebuilt. Verify the counter's observation window (stats_reset in pg_stat_database), check replicas too on PostgreSQL 16+ where standby index usage matters to you, and confirm the index doesn't back a constraint before dropping.

Why B-Trees Bloat: PostgreSQL Index Bloat Mechanics

Four mechanisms explain nearly all real-world cases. Churn on random keys: UPDATEs (each non-HOT update inserts a new index entry) and DELETEs leave dead entries;

vacuum removes them, but half-empty pages persist because B-tree pages only merge when completely empty. The sweeping-range pattern: queue/outbox tables where rows are inserted at one end of the key space and deleted from the other strand a trail of near-empty pages — the classic worst case.

Long-horizon blockers: long transactions and abandoned replication slots prevent vacuum from removing dead entries at all, so bloat compounds (fix the horizon first or rebuilds will re-bloat within days). Version note: PostgreSQL 13's B-tree deduplication and 14's bottom-up deletion dramatically reduced bloat growth for duplicate-heavy and update-heavy indexes — if you're seeing severe bloat on 12 or earlier, the upgrade itself is a bloat-mitigation measure; indexes are rebuilt in the new format on REINDEX, not automatically at pg_upgrade.

The Fix: REINDEX CONCURRENTLY, Operated Correctly

PostgreSQL 12+ makes online rebuilds first-class; the official PostgreSQL REINDEX documentation lists the exact syntax and per-version limits. Blast radius statement up front: REINDEX CONCURRENTLY holds only a SHARE UPDATE EXCLUSIVE lock — reads and writes proceed — but it (a) needs free disk for a full second copy of the index, (b) roughly doubles the index's write amplification for the duration, (c) must wait for transactions older than its snapshots, so a long-running query can stall it, and (d) like CREATE INDEX CONCURRENTLY, it cannot run inside a transaction block.

-- 1. Verification before: record current state
SELECT pg_relation_size('idx_orders_customer_id') AS bytes_before;
SELECT avg_leaf_density FROM pgstatindex('idx_orders_customer_id');

-- 2. Guard the session
SET lock_timeout = '5s';
SET statement_timeout = 0;

-- 3. Rebuild online
REINDEX INDEX CONCURRENTLY idx_orders_customer_id;

-- 4. Validation after: size down, density restored, index valid
SELECT pg_relation_size('idx_orders_customer_id') AS bytes_after;
SELECT avg_leaf_density FROM pgstatindex('idx_orders_customer_id');
SELECT indexrelid::regclass
FROM pg_index
WHERE NOT indisvalid;   -- must not list this index

Operational rules from our runbooks: rebuild one large index at a time and sequence by size descending during low-traffic windows; monitor pg_stat_progress_create_index for phase and block progress; and know the failure mode — an interrupted REINDEX CONCURRENTLY run leaves an INVALID index (suffixed _ccnew) that consumes space and write overhead until you drop it. The rollback path is exactly that: DROP INDEX CONCURRENTLY the invalid leftover and re-run. On PostgreSQL 14+, REINDEX CONCURRENTLY handles TOAST-table indexes and partitioned indexes (REINDEX INDEX CONCURRENTLY on the parent recurses per partition); on 12–13, script the per-partition loop yourself.

Two situations still require alternatives: rebuilding the index behind a primary key or unique constraint is handled transparently by REINDEX CONCURRENTLY (it swaps the constraint's index), but converting or renaming constraint indexes may need ALTER TABLE ... ADD CONSTRAINT ... USING INDEX choreography; and clusters still on PostgreSQL 11 or older must use the create-new/drop-old/rename dance or pg_repack's index mode.

Preventing PostgreSQL Index Bloat: Keep Density High Between Rebuilds

Rebuilds are remediation; prevention is configuration and design. Keep autovacuum aggressive enough on churn tables that dead entries are removed promptly (per-table autovacuum_vacuum_scale_factor as covered in our autovacuum guide — bloated tables and bloated indexes share root causes). Kill the horizon-holders: idle_in_transaction_session_timeout, replication-slot monitoring. For sweeping-range queue tables, the structural fix is partitioning by time and dropping old partitions — DROP PARTITION deletes index pages wholesale and cannot bloat. Reconsider index width: every indexed column you don't filter or sort on multiplies churn surface.

And for monotonic-insert plus random-delete patterns, measure whether a lower fillfactor actually helps your case — for pure append indexes, the default is right and lowering it just pre-bloats.

Finally, schedule measurement, not rebuilds: a quarterly pgstatindex audit that triggers rebuilds only when density crosses your threshold (we use <60% on indexes >5 GB) beats calendar-driven reindexing that churns I/O for no benefit.

Quantifying the Query-Level Payoff Before You Rebuild

"The index is 62% air" convinces a DBA; "this rebuild returns X ms of p99" convinces a change-approval board. Bridge the two with buffer accounting on the queries that use the index. Capture the hot query's profile before the rebuild:

EXPLAIN (ANALYZE, BUFFERS)
SELECT order_id, created_at
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 100;
-- Before: Index Scan using idx_orders_customer_created ...
--   Buffers: shared hit=311 read=42

Then rebuild in staging on a production-scale copy and re-run: a density-restored index typically shows the buffer count for the same logical work dropping proportionally to the density gain (e.g., ~350 buffers to ~190 at 55%→92% density). Multiply by the statement's calls from pg_stat_statements and you have the I/O saved per hour in pages — a number that also predicts the cache-pressure relief for every other query, since those reclaimed buffers stop evicting someone else's working set.

This is also your production verification metric: after the live rebuild, the same EXPLAIN must reproduce the staging buffer profile, and pg_stat_statements.shared_blks_read per call for the statement family should step down within one traffic cycle.

If it doesn't, the bloat you removed wasn't on this query's path — which is exactly the kind of finding that stops a team from reflex-rebuilding forty indexes when two mattered.

fillfactor: Pre-Bloat as a Deliberate Trade

Index fillfactor (default 90 for B-trees) reserves page slack for future insertions into existing key ranges. The right value is workload-specific, and both directions of error cost you. Lowering it to 70–80 on indexes over churning key ranges (UUID keys, updated indexed columns) delays page splits — the mechanism that creates fragmentation — at the price of a permanently larger index; that trade wins when your measured re-bloat velocity between rebuilds is high. Raising it to 100 is correct only for genuinely read-only indexes (closed partitions, archive tables), where every page is packed and range scans hit maximal density.

The common error is copying a low fillfactor onto monotonic append-only indexes (sequential IDs, timestamps): new entries only ever land in the rightmost leaf, the reserved slack in every other page is never used, and you've institutionalized 20% bloat by configuration. Set it per index at rebuild time — ALTER INDEX idx_orders_customer_created SET (fillfactor = 80); REINDEX INDEX CONCURRENTLY idx_orders_customer_created; — and judge the setting by one metric: density decay rate between your quarterly pgstatindex audits, not by intuition.

Partitioned Tables: REINDEX CONCURRENTLY at Fleet Scale

Partitioning changes the operational math in your favor. On PostgreSQL 14+, REINDEX INDEX CONCURRENTLY against the partitioned parent index recurses through every leaf partition sequentially in one command — convenient, but on a 96-partition table it is also a single long-running session whose failure point is opaque. Our runbooks prefer explicit per-partition loops for anything large: enumerate leaf indexes from pg_partition_tree(), rebuild them individually ordered by density ascending (worst first), checkpoint progress in an ops table, and make the job resumable — an interrupted per-partition loop restarts where it stopped, while an interrupted parent-level command leaves you auditing pg_index.indisvalid across the whole tree.

Partitioning also enables the cheapest rebuild of all: for time-partitioned data, old partitions become read-only in practice — rebuild each partition's indexes once with fillfactor = 100 when it ages out of the write window, and it never needs attention again. Combined with dropping expired partitions (which removes their index pages outright), a well-run partition lifecycle reduces steady-state index maintenance to the current write partitions only — typically a few percent of total index bytes. That lifecycle design, more than any REINDEX CONCURRENTLY cadence, is what separates fleets that fight PostgreSQL index bloat quarterly from fleets that stopped noticing it.

REINDEX CONCURRENTLY in Replicated and HA Topologies

An index rebuild is never a single-node event once standbys exist. REINDEX CONCURRENTLY writes the entire new index through WAL, so a 60 GB index rebuild ships roughly 60 GB (plus full-page-image overhead) to every standby — plan the replication-lag budget like a bulk load: check your standbys' catch-up headroom first, run the earlier lag decomposition during the rebuild, and on slot-protected topologies confirm max_slot_wal_keep_size won't invalidate a slow standby mid-operation. On synchronous quorum setups, the WAL surge lands directly in commit latency; schedule accordingly or temporarily widen the quorum with an ANY policy that tolerates the lagging member.

Under Patroni, remember that a big rebuild can push replicas past maximum_lag_on_failover — during that window your failover candidate pool shrinks, which belongs in the change ticket's risk section, not in the incident retro. Two adjacent traps: first, physical standbys inherit the primary's bloat exactly, so measuring on a replica (a good idea for pgstatindex's read cost) is valid — but "rebuild only on the standby" is not possible with physical replication; the rebuild happens on the primary and replicates, full stop.

Logical replicas are the opposite: their indexes are locally owned, independently bloated, and independently rebuildable — audit them separately, because a subscriber serving reads with its own five-year-old indexes is a common blind spot.

Second, major-version upgrades via logical replication or pg_dump effectively rebuild every index at maximal density, while pg_upgrade --link preserves bloat byte-for-byte — factor the difference into upgrade-method selection when a fleet is carrying heavy index debt, since the dump-based path buys the density restoration you were separately scheduling; on very large systems that alone has tipped the method decision.

PostgreSQL Index Bloat and REINDEX CONCURRENTLY Quick Reference

PostgreSQL Index Bloat FAQ

Does VACUUM fix PostgreSQL index bloat?

No. VACUUM removes dead entries so that pages can be reused, but it does not consolidate half-empty pages or shrink the index. Only a rebuild restores density.

REINDEX CONCURRENTLY or pg_repack for index rebuilds?

On PostgreSQL 12 and later, native REINDEX CONCURRENTLY is preferred: no extension is required, constraint-index handling is built in, and progress views are supported. pg_repack remains valuable on older versions and for rebuilding a table together with its indexes in a single operation.

How much slower are queries on a bloated index?

To a first approximation, page reads scale inversely with leaf density: an index at 45% density reads roughly twice the pages of one at 90% for the same range. The end-to-end latency impact depends on cache residency, so measure your own before-and-after figures with EXPLAIN (ANALYZE, BUFFERS) rather than quoting generic percentages to stakeholders.

Is PostgreSQL index bloat different on Aurora, RDS, or Cloud SQL?

The B-tree mechanics and the REINDEX CONCURRENTLY procedure are identical. The differences are operational: provider disk-space headroom for the second copy, I/O cost models (Aurora I/O billing makes rebuilds a visible line item), and extension availability — pgstattuple is available on the major managed services.

Do GIN and BRIN indexes bloat the same way?

No. GIN has its own dynamics, chiefly pending-list growth, which is tuned through gin_pending_list_limit and vacuum frequency. BRIN does not bloat in the classical sense but degrades through summarization drift on out-of-order data. Both require diagnosis paths separate from B-tree density.

Does REINDEX CONCURRENTLY block reads or writes?

No. REINDEX CONCURRENTLY holds only a SHARE UPDATE EXCLUSIVE lock, so application traffic keeps flowing. It does require free disk for a full second copy of the index, it roughly doubles that index’s write amplification while it runs, and it cannot run inside a transaction block.

How often should you audit for PostgreSQL index bloat?

Quarterly works for most fleets: measure density on indexes above 5 GB and rebuild only what crosses your threshold. Churn-heavy queue and outbox tables deserve a monthly review, because PostgreSQL index bloat re-accumulates fastest where rows sweep across the key space.

Put PostgreSQL Index Bloat Monitoring on Autopilot

MinervaDB's remote DBA programs include scheduled index-density audits, horizon-blocker alerting, and change-managed online rebuilds across self-managed PostgreSQL and every major managed platform. If index debt is inflating your latency or your storage spend, contact minervadb.com/contact-us · contact@minervadb.com.

Rehearse every REINDEX CONCURRENTLY rebuild in staging at production data scale first, confirm free-disk headroom for index copies, and enter any maintenance with verified backups and a tested DR path.