Rebuild an index without locking (REINDEX CONCURRENTLY)

Command

REINDEX INDEX CONCURRENTLY customer_email_idx;

What it does

Rebuilds the index without blocking writes; removes index bloat.

When to use it

When an index has grown/become corrupted, on a live system without a maintenance window.

Caution

Requires PostgreSQL 12+. If it fails it can leave an INVALID index with the _ccnew suffix; check pg_index.indisvalid and clean up with DROP INDEX if needed. To rebuild the whole table: REINDEX TABLE CONCURRENTLY customer;