REINDEX

What REINDEX does

REINDEX performs a full rebuild of one or more indexes. It scans the entire table, constructs a new index from the live data, and replaces the old index with the new one. The new index is fully compact — no dead entries, no sparse pages, no leftover fragmentation from previous updates and deletes. A clean slate, in the most literal sense.

This is not a repair operation in the incremental sense. REINDEX does not patch the existing index structure. It discards it entirely and starts fresh. There is a certain honesty to the approach — rather than attempting to mend what has deteriorated beyond salvaging, it acknowledges the situation and begins again. This makes it effective against both gradual bloat (space wasted over time from normal write activity) and outright corruption (structural damage from crashes, storage failures, or bugs).

REINDEX vs REINDEX CONCURRENTLY

The two modes differ in locking, speed, and failure behavior. Allow me to lay out the distinctions clearly.

For production databases where write availability matters, REINDEX CONCURRENTLY is the approach I would recommend without hesitation. It takes longer, it uses more disk space, and it leaves a mess if it fails — but it does not turn your guests away at the door while the work is being done. Use plain REINDEX during maintenance windows or on replicas where the write lock is acceptable.

When to REINDEX

REINDEX is not routine housekeeping — it is a targeted remedy for specific situations. One does not rebuild the filing cabinets on a schedule. One rebuilds them when the diagnosis warrants it:

• Index bloat — when pgstatindex shows avg_leaf_density below 50-60%, the index has accumulated more dead space than live data warrants. REINDEX compacts it back to ~90% density. Common on tables with heavy UPDATE or DELETE activity on indexed columns — the sort that accumulate clutter fastest.
• Index corruption — after a crash, storage failure, or unexpected behavior where queries return wrong results. The amcheck extension can confirm corruption. REINDEX rebuilds a correct index from the heap data.
• After major bulk operations — large bulk DELETEs, mass UPDATEs, or pg_restore operations can leave indexes in a rather disheveled state. A REINDEX after the bulk work compacts everything cleanly.
• PostgreSQL major version upgrades — occasionally a new version changes the on-disk index format or fixes a bug in index maintenance. The release notes will say when a REINDEX is recommended.

-- Check index bloat with pgstatindex (requires pgstattuple extension)
CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT
  indexrelname,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
  (pgstatindex(indexrelid)).avg_leaf_density,
  (pgstatindex(indexrelid)).leaf_fragmentation,
  (pgstatindex(indexrelid)).deleted_pages
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 10;

-- avg_leaf_density below 50% = significant bloat, REINDEX recommended
-- 70-90% = normal for actively updated indexes
-- 90%+ = healthy

-- Check for index corruption with amcheck
CREATE EXTENSION IF NOT EXISTS amcheck;

-- Verify a B-tree index structure (non-blocking)
SELECT bt_index_check('my_table_pkey');

-- Thorough check including heap cross-references (slower, more complete)
SELECT bt_index_parent_check('my_table_pkey', heapallindexed => true);

Syntax

REINDEX operates at four levels of granularity — a single index, all indexes on a table, all indexes in a schema (PostgreSQL 14+), or all indexes in a database. Each level supports the CONCURRENTLY option. Start narrow and widen only as the situation requires.

-- Rebuild a single index (locks writes)
REINDEX INDEX my_table_pkey;

-- Rebuild all indexes on a table
REINDEX TABLE my_table;

-- Rebuild all indexes in a schema (PostgreSQL 14+)
REINDEX SCHEMA public;

-- Rebuild all indexes in the entire database
REINDEX DATABASE mydb;

-- CONCURRENTLY variants (PostgreSQL 12+) — no write lock
REINDEX INDEX CONCURRENTLY my_table_pkey;
REINDEX TABLE CONCURRENTLY my_table;
REINDEX DATABASE CONCURRENTLY mydb;

System catalog indexes can only be rebuilt with REINDEX SYSTEM or REINDEX DATABASE — not with CONCURRENTLY, because catalog modifications require exclusive access.

REINDEX vs pg_repack

pg_repack is an external extension that can rebuild indexes online, similar to REINDEX CONCURRENTLY. The key differences:

• Scope — REINDEX rebuilds indexes only. pg_repack can also rebuild the table itself (compacting table bloat), reorder rows by an index, and move tables to a different tablespace.
• Availability — REINDEX is built into PostgreSQL. pg_repack requires installing an extension and a command-line tool.
• Locking — both REINDEX CONCURRENTLY and pg_repack avoid sustained write locks. pg_repack takes a brief exclusive lock at the start to set up triggers and at the end to swap. REINDEX CONCURRENTLY takes a brief lock only at the end.
• Failure handling — a failed REINDEX CONCURRENTLY leaves an INVALID index. A failed pg_repack leaves the original intact but may leave behind temporary objects that need cleanup.

For index-only rebuilds on PostgreSQL 12+, REINDEX CONCURRENTLY is simpler — no extension to install, no additional tooling to maintain. For combined table and index compaction, or on older PostgreSQL versions, pg_repack is the better tool. Use the right instrument for the job, not the most impressive one.

How Gold Lapel relates

Gold Lapel detects index bloat by correlating query performance metrics — when index scans read more pages than expected relative to the rows returned, and the index size is disproportionate to the table size, bloat is the likely cause. When I identify this pattern, I provide the specific REINDEX CONCURRENTLY command for the affected indexes. Not a general suggestion to investigate — the precise remedy, ready to run. The diagnosis is only useful if the treatment follows promptly.