Blog
4 min read

Postgres Index Bloat and REINDEX CONCURRENTLY: Rebuilding Indexes Without Downtime

B-tree indexes in Postgres bloat under heavy updates and deletes, slowing queries and wasting memory. How to measure index bloat with pgstattuple, rebuild indexes online with REINDEX CONCURRENTLY, what it locks, how to clean up invalid _ccnew indexes after a failure, and how to reduce bloat in the first place.

Postgres indexes don't shrink by themselves. After months of updates and deletes, a B-tree index can be several times larger than a freshly built one, with half-empty pages. That costs cache memory, disk and query time. REINDEX CONCURRENTLY (Postgres 12+) rebuilds it without blocking writes. (Database Indexes, Postgres Index Types)

Why indexes bloat

Every row version Postgres creates needs index entries (unless the update is a HOT update). When VACUUM removes dead row versions, it removes their index entries too — but the pages they occupied usually stay allocated. B-tree pages that empty out completely can be reused, but partially empty pages aren't merged. Patterns that bloat indexes fastest:

Measure it

The accurate way uses the pgstattuple extension:

CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT avg_leaf_density, leaf_fragmentation,
       pg_size_pretty(pg_relation_size('orders_customer_id_idx')) AS size
FROM pgstatindex('orders_customer_id_idx');

A fresh B-tree has a leaf density around 90% (the default fillfactor). Density down in the 40–60% range means the index is roughly twice the size it needs to be. pgstatindex reads the whole index, so run it off-peak on big ones; estimate-based queries are cheaper for a quick survey.

Also check for indexes you don't use at all — dropping is better than rebuilding:

SELECT relname, indexrelname, idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
ORDER BY idx_scan, pg_relation_size(indexrelid) DESC
LIMIT 20;

(Unused on the primary may still be used on replicas — check those too.)

Rebuild online

REINDEX INDEX CONCURRENTLY orders_customer_id_idx;
-- or every index on a table:
REINDEX TABLE CONCURRENTLY orders;

What it does:

  1. Creates a new index alongside the old one (named with a _ccnew suffix while building).
  2. Builds it in two passes, waiting for transactions that might not see it.
  3. Swaps the new index in and drops the old one.

Locks: it takes a SHARE UPDATE EXCLUSIVE lock on the table — reads and writes continue; other DDL and VACUUM on that table wait. The swap needs brief stronger locks, so it can wait behind long-running transactions. (Postgres Deadlocks)

Cost: roughly double the index's disk space during the build, plus extra I/O and WAL. Two full scans make it slower than a plain REINDEX.

Can't run inside a transaction block, and can't concurrently reindex system catalogs.

Watch progress:

SELECT * FROM pg_stat_progress_create_index;

When it fails: invalid indexes

If a concurrent reindex is cancelled or errors (a unique violation, a deadlock, running out of disk), it can leave behind an invalid index — still maintained on every write, never used for reads:

SELECT indexrelid::regclass AS index_name
FROM pg_index
WHERE NOT indisvalid;

Drop the leftover _ccnew index (with DROP INDEX CONCURRENTLY) and try again. The same applies to failed CREATE INDEX CONCURRENTLY. (Postgres Migrations on Large Tables)

Reduce bloat in the first place

  • Keep autovacuum aggressive enough on hot tables (lower scale factors per table).
  • Avoid indexing columns that change constantly unless queries need them.
  • Lower fillfactor on tables with heavy updates so HOT updates can happen.
  • Use time-ordered keys rather than random UUIDs for big, insert-heavy tables.
  • For tables that bloat and need a full rewrite, use pg_repack (or the REPACK command coming in Postgres 19) instead of locking VACUUM FULL.

A sensible routine

  1. Monthly: survey index sizes and densities; list unused indexes.
  2. Drop unused indexes (after checking replicas).
  3. REINDEX CONCURRENTLY the worst offenders off-peak, one at a time, with disk headroom.
  4. Check for invalid indexes afterwards.

The summary

  • B-tree indexes bloat under updates and deletes and don't shrink on their own.
  • Measure with pgstatindex; rebuild with REINDEX CONCURRENTLY without blocking writes.
  • Clean up invalid _ccnew indexes after failures, and tune autovacuum and fillfactor to bloat less.

EasySpawn runs Postgres beside your app on each server, and Claude Code can survey index bloat, find unused indexes and run online rebuilds when you approve. See how it works or join the waitlist.

Related: pg_stat_statements · Postgres EXPLAIN ANALYZE · Postgres Planner Statistics · Postgres TOAST

Keep reading