REINDEX CONCURRENTLY Without Downtime #

Rebuilding an index is the only way to reclaim the space that dead entries have scattered through its leaf pages, and on a production system it has to happen while traffic continues. REINDEX INDEX CONCURRENTLY is designed for exactly that, but it has three operational edges — locking, disk headroom, and failure cleanup — that decide whether the maintenance window is boring or memorable.

The measurement that justifies the rebuild is covered in index maintenance and bloat; this page is the procedure itself.

What the Concurrent Rebuild Actually Does #

A plain REINDEX takes an ACCESS EXCLUSIVE lock and rebuilds in place: fast, simple, and it blocks every reader and writer on the table until it finishes. The concurrent form trades that for a longer, gentler process in four phases:

  1. Build. A new index is created alongside the old one under a SHARE UPDATE EXCLUSIVE lock — reads and writes continue. A first pass scans the table and builds the initial structure.
  2. Catch-up. A second pass picks up rows written during the first pass, waiting for transactions that started before the build to finish.
  3. Swap. The new index is marked valid and the old one invalid, under a brief strong lock measured in milliseconds.
  4. Drop. The old index is dropped, again briefly locking.

Because it waits for pre-existing transactions, a long-running query or an idle-in-transaction session can stall the whole operation indefinitely.

Phases and locks of REINDEX CONCURRENTLY A horizontal timeline with four phases: build, catch-up, swap and drop. The build and catch-up phases hold a share update exclusive lock allowing reads and writes. The swap and drop phases briefly hold a stronger lock. 1. build first table pass 2. catch-up waits for older transactions 3. swap brief 4. drop brief lock held SHARE UPDATE EXCLUSIVE — reads and writes continue strong lock, milliseconds both copies exist from phase 1 until phase 4 — plan disk headroom for that window

Annotated Evidence: Before the Rebuild #

The case for rebuilding is a query whose page reads grew while its row counts stayed flat:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, payload FROM events WHERE account_id = 91 AND created_at > '2026-01-01';

Index Scan using events_account_created_idx on events  (actual time=0.09..11.87 rows=412 loops=1)
  Index Cond: (account_id = 91 AND created_at > '2026-01-01')
  Buffers: shared hit=31 read=27            -- 58 pages for 412 rows
Planning Time: 0.14 ms
Execution Time: 11.94 ms

Confirm it is really bloat and not a statistics problem before touching anything:

SELECT index_size, avg_leaf_density, leaf_fragmentation
  FROM pgstatindex('events_account_created_idx');
-- index_size | avg_leaf_density | leaf_fragmentation
-- 4184342528 |            41.7  |              38.2

avg_leaf_density of 41.7% against a freshly built 90% means roughly half of every page read is empty space. That is a rebuild candidate.

Step-by-Step Runbook #

  1. Baseline. Save the EXPLAIN (ANALYZE, BUFFERS) output and the index size so the improvement is provable rather than felt.

    SELECT pg_size_pretty(pg_relation_size('events_account_created_idx'));

  2. Check headroom. You need free space for a second full copy plus WAL. On a 4 GB index, do not start with 4.5 GB free.

  3. Clear blockers. A transaction older than the build start stalls the catch-up phase indefinitely:

    SELECT pid, state, xact_start, query
  FROM pg_stat_activity
 WHERE xact_start < now() - interval '5 minutes' AND state <> 'idle'
 ORDER BY xact_start;

  4. Rebuild. One index at a time, so a failure is easy to reason about:

    REINDEX INDEX CONCURRENTLY events_account_created_idx;

    REINDEX TABLE CONCURRENTLY events rebuilds every index on the table, which is convenient and much harder to interrupt cleanly.

  5. Watch progress from a second session:

    SELECT phase, blocks_done, blocks_total, tuples_done, tuples_total
  FROM pg_stat_progress_create_index;

  6. Clean up any invalid leftovers. After an interrupted run:

    SELECT indexrelid::regclass AS invalid_index, indrelid::regclass AS table
  FROM pg_index WHERE NOT indisvalid;
DROP INDEX CONCURRENTLY events_account_created_idx_ccnew;

  7. Refresh statistics and re-measure. ANALYZE updates relpages so the planner immediately sees the smaller index:

    ANALYZE events;
The rebuild runbook at a glance Six connected steps: baseline the plan, check disk headroom, clear long transactions, run the concurrent rebuild, verify validity, then analyze and re-measure. A branch from the rebuild step leads to dropping an invalid index if the run was interrupted. 1. baseline plan + size 2. headroom 2× index size 3. blockers old transactions 4. rebuild CONCURRENTLY 5. ANALYZE re-measure interrupted? pg_index WHERE NOT indisvalid DROP INDEX CONCURRENTLY …_ccnew never leave an invalid index in place — it costs writes and serves no reads

Before and After #

-- BEFORE: 41.7% leaf density, 4.0 GB
Index Scan using events_account_created_idx  (actual time=0.09..11.87 rows=412)
  Buffers: shared hit=31 read=27
Execution Time: 11.94 ms

-- AFTER: 90.1% leaf density, 1.9 GB
Index Scan using events_account_created_idx  (actual time=0.06..1.42 rows=412)
  Buffers: shared hit=9 read=1
Execution Time: 1.48 ms

The row count is unchanged, which is the point: only the pages needed to reach those rows fell, from 58 to 10. That is the metric to quote when justifying the maintenance window.

Common Pitfalls #

Starting without disk headroom. The build fails part-way and leaves an invalid index plus a full filesystem. Diagnostic signal: could not extend file in the log. Fix: verify free space against pg_relation_size first.

Leaving _ccnew indexes behind. They are maintained on every write and used by nothing. Diagnostic signal: pg_index rows with indisvalid = false. Fix: make the cleanup query part of the runbook, not an afterthought.

Rebuilding while a long transaction is open. The catch-up phase waits for it, so the operation appears hung. Diagnostic signal: pg_stat_progress_create_index stuck in a waiting phase. Fix: end the offending session, and set idle_in_transaction_session_timeout to prevent recurrence.

Rebuilding indexes that should be dropped. An index with idx_scan = 0 does not need to be compact, it needs to be gone — see finding unused indexes safely. Diagnostic signal: a large index with no scans since the last statistics reset. Fix: drop instead of rebuild.

Rebuilding every index on a table at once. REINDEX TABLE CONCURRENTLY is convenient but takes hours on a large table, holds its lock class for that whole period, and leaves a harder cleanup if it fails part-way. Diagnostic signal: a single maintenance statement running past its window with no way to stop safely. Fix: rebuild index by index, so each unit of work is small enough to abandon.

Forgetting the WAL and replication cost. A concurrent rebuild writes the entire new index to the write-ahead log, which ships to every replica and to archive storage. On a 4 GB index that is 4 GB of replication traffic on top of normal load. Diagnostic signal: replica lag spiking during maintenance. Fix: schedule rebuilds when replication has headroom, and watch lag as an abort condition.

Assuming a rebuild fixes a badly designed index. A compact index on the wrong columns is still the wrong index. Diagnostic signal: an index whose scan count is low relative to its size even when freshly built. Fix: revisit the design against the actual predicates before spending the I/O — a different column order or an INCLUDE clause may serve the workload better than any amount of compaction.

Disk footprint through the rebuild A stepped area showing the original four gigabyte index alone, then both copies coexisting at nearly six gigabytes during the build, then the compact one point nine gigabyte replacement after the old copy is dropped. rebuild timeline → 4.0 GB both copies ≈ 5.9 GB 1.9 GB after the swap

Frequently Asked Questions #

Does REINDEX CONCURRENTLY block writes? Not during the build. It holds a SHARE UPDATE EXCLUSIVE lock that permits reads and writes, and needs a stronger lock only for the brief swap and drop phases. It does conflict with other DDL and with vacuum on the same table.

What happens if the rebuild is interrupted? A partially built index remains, marked invalid and typically suffixed _ccnew. No plan will use it, but every write still maintains it. Locate invalid entries in pg_index and remove them with DROP INDEX CONCURRENTLY.

How much extra disk space is needed? Room for a full second copy of the index for the duration, plus the WAL generated by the build. Check pg_relation_size on the target and leave comfortable margin before starting.