Rebuilding Bloated Indexes with REINDEX CONCURRENTLY

The sessions table is only 8 GB, but its primary-key index is 31 GB. Rows are inserted, updated a few times and deleted within hours, and B-tree pages emptied by those deletes are only partially reused, so the index has grown to four times the size of the table it indexes. Lookups are slower than they should be, the index no longer fits in memory, and backups carry 23 GB of dead weight. The classic fix, REINDEX INDEX, rebuilds it from scratch — while holding a lock that blocks every write to the table and every read that would use the index, for the whole build. PostgreSQL 12 added REINDEX CONCURRENTLY, which builds a replacement index alongside the old one and swaps them with only brief locks. This guide covers measuring bloat, running concurrent rebuilds, recovering from failed ones, and deciding when to rebuild at all. It belongs to Online Index Management.

Symptom / Error Signatures

Index bloat shows up as:

• An index much larger than a fresh build of the same index would be — compare pg_relation_size of the index against the estimate below, or against a freshly built copy on a replica restore.
• Rising read latency on index scans and a falling cache hit ratio for the table’s indexes.
• pgstattuple’s pgstatindex() reporting low avg_leaf_density (well below the default fill factor of 90%) or high leaf_fragmentation.

A plain REINDEX in production shows up as a lock pile-up: sessions waiting on Lock behind REINDEX INDEX sessions_pkey. A failed concurrent rebuild leaves invalid indexes with telling names:

 sessions_pkey_ccnew     | invalid
 sessions_pkey_ccnew1    | invalid

Root Cause Analysis

B-tree indexes in PostgreSQL reclaim space from deleted entries within a page, and fully empty pages can be recycled, but pages that become sparse through a pattern of deletes and inserts in different key ranges stay sparse. Workloads with high churn on a narrow set of keys, or monotonically increasing keys with deletes of old ranges, bloat indexes steadily. PostgreSQL 13 and 14 reduced some of this (deduplication, bottom-up deletion), but high-churn tables still bloat.

REINDEX INDEX rebuilds under ACCESS EXCLUSIVE on the index and SHARE on the table, blocking writes and index-dependent reads. REINDEX CONCURRENTLY instead builds a new index with the same definition using the concurrent-build machinery, then swaps it in and drops the old one, taking only SHARE UPDATE EXCLUSIVE on the table for most of the work and brief stronger locks for the swap. Like CREATE INDEX CONCURRENTLY, it waits for existing transactions at several points, cannot run inside a transaction block, and if interrupted leaves an invalid _ccnew index (or, after the swap, a _ccold index) behind.

Immediate Mitigation

1. Measure before rebuilding. Use pgstattuple for a precise reading on the indexes you suspect.

-- PostgreSQL · requires the pgstattuple extension · reads the whole index; run off-peak for large ones
CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT index_size, avg_leaf_density, leaf_fragmentation
FROM pgstatindex('sessions_pkey');

Density well below the fill factor (for example under 50%) indicates a rebuild will shrink the index substantially.

2. Rebuild concurrently, outside a transaction, with timeouts.

-- PostgreSQL 12+ · index owner or superuser · must not run inside BEGIN … COMMIT
-- WARNING: needs free disk for a second copy of the index while it builds.
SET lock_timeout = '3s';
SET statement_timeout = 0;
REINDEX INDEX CONCURRENTLY sessions_pkey;
-- ROLLBACK PATH: none needed; on failure, drop the invalid _ccnew index (step 3) and retry.

3. Clean up after a failed rebuild. Find leftovers and drop them concurrently.

-- PostgreSQL · read-only listing, then a concurrent drop outside a transaction
SELECT c.relname FROM pg_index i JOIN pg_class c ON c.oid = i.indexrelid
WHERE NOT i.indisvalid AND c.relname ~ '_cc(new|old)[0-9]*$';
DROP INDEX CONCURRENTLY IF EXISTS sessions_pkey_ccnew;

The general procedure for invalid indexes is in cleaning up invalid indexes after a failed build.

Permanent Fix / Long-Term Pattern

Treat index bloat as a monitored metric with a scheduled response. Track estimated bloat for high-churn tables weekly (a lightweight estimate from pg_class and pg_stats, or pgstatindex on a replica), and rebuild concurrently when an index exceeds a threshold such as twice its fresh size. Schedule rebuilds off-peak, one index at a time, with enough free disk for a second copy, and watch replica lag — a rebuild writes the whole index to WAL. Rebuilding a whole table’s indexes with REINDEX TABLE CONCURRENTLY is convenient but makes a failure leave several leftovers; one index per run is easier to operate.

Reduce the churn that causes bloat where possible: lower fillfactor on heavily updated indexes to leave room for updates, tune autovacuum to run more often on high-churn tables, and for tables where bloat is mostly in the heap as well as indexes, rebuild both online with pg_repack, as in removing table bloat online with pg_repack. For partitioned tables, reindex partition by partition. Rebuilds are also a natural moment to change an index’s definition — but that is a new index, built with CREATE INDEX CONCURRENTLY, not a reindex.

A rebuild is also a migration in the operational sense and deserves the same announcements and monitoring: record when it starts and ends, watch lock waits and replica lag while it runs, and keep the job’s logs with the migration history so a later latency change can be correlated. On managed databases, check that the provider’s storage autoscaling or quota will accommodate the temporary second copy of the index.

Verification Checklist

• Bloat was measured before the rebuild, and the expected saving justified it.
• Free disk space exceeded the index size before starting.
• The rebuild ran with REINDEX ... CONCURRENTLY outside a transaction, one index at a time.
• No _ccnew or _ccold invalid indexes remain afterwards.
• pg_relation_size of the index dropped as expected, and read latency improved.

Frequently Asked Questions

Does REINDEX CONCURRENTLY block writes? No. It takes SHARE UPDATE EXCLUSIVE on the table for the build and validation, allowing reads and writes, and brief stronger locks during the swap. It does wait for older transactions at several points, so long transactions slow it down.

Can I run it on a primary key or unique index? Yes. REINDEX CONCURRENTLY supports primary-key and unique indexes, including those backing constraints. It cannot be used on system catalogs or on exclusion-constraint indexes.

What are _ccnew and _ccold indexes? Temporary names used during a concurrent rebuild. If the rebuild fails, the invalid replacement (_ccnew) or the not-yet-dropped original (_ccold) remains and should be dropped with DROP INDEX CONCURRENTLY.

Is VACUUM enough to fix index bloat? Vacuum removes dead index entries and allows fully empty pages to be reused, but it does not compact sparse pages. For indexes that are mostly empty space, a rebuild is the only way to shrink them.