PostgreSQL REINDEX CONCURRENTLY: Rebuild Bloated Indexes Without Downtime

REINDEX CONCURRENTLY rebuilds a PostgreSQL index without locking out inserts, updates or deletes on the table. It has existed since PostgreSQL 12, and on a busy production database it is usually the only acceptable way to shrink a bloated index or replace a corrupted one. It is also slower than a normal rebuild, cannot run everywhere, and leaves an invalid index behind if it fails. This guide covers how to tell whether an index needs rebuilding, how to run the rebuild safely, and what to do when it goes wrong.

When an index actually needs a rebuild

The REINDEX documentation lists the main reasons:

• Bloat. The index contains many empty or nearly empty pages, so it is much bigger than it needs to be.
• Corruption. The index no longer contains valid data, for example after a hardware or software problem.
• A changed storage parameter, such as fillfactor, that you want to take full effect.
• An invalid index left behind by a failed CREATE INDEX CONCURRENTLY.

There is one more case that is easy to miss. The PostgreSQL 13 release notes say that B-tree deduplication, which stores duplicate keys only once, applies to existing indexes upgraded with pg_upgrade only after a REINDEX. If you upgraded from 12 or older that way, rebuilding indexes on low-cardinality columns can make them noticeably smaller.

Index bloat usually comes from the same place as table bloat: dead row versions piling up faster than vacuum removes them. If indexes keep coming back bloated after a rebuild, the real fix is in autovacuum settings and long-running transactions, covered in our autovacuum tuning guide.

Step 1: find candidates

Start with the largest indexes and how often they are used:

SELECT s.schemaname,
       s.relname      AS table_name,
       s.indexrelname AS index_name,
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS index_size,
       s.idx_scan
FROM pg_stat_user_indexes s
ORDER BY pg_relation_size(s.indexrelid) DESC
LIMIT 20;

Large indexes with idx_scan at or near zero are worth a second look before you rebuild anything: an index nobody uses should perhaps be dropped, not rebuilt. Two caveats: the documentation notes that all statistics counters are reset after an unclean shutdown, a crash or point-in-time recovery, so check how long the counters have been accumulating; and if replicas serve queries, check the index statistics there too before concluding that an index is unused.

For a B-tree index you suspect of bloat, the pgstattuple extension gives real numbers:

CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT pg_size_pretty(index_size) AS size,
       leaf_pages, empty_pages, deleted_pages,
       avg_leaf_density, leaf_fragmentation
FROM pgstatindex('public.orders_customer_id_idx');

• avg_leaf_density is the average fill of leaf pages. B-trees use a default fillfactor of 90, and the documentation says leaf pages are filled to that percentage during an initial build. A much lower value means a lot of the index is empty space.
• empty_pages and deleted_pages count pages that hold no live entries.
• leaf_fragmentation shows how far the physical order of leaf pages has drifted from the logical order.

By default only superusers and members of pg_stat_scan_tables can run these functions. The documentation notes that results are collected page by page rather than as an instant snapshot; on very large indexes, run the check outside peak hours.

Why a plain REINDEX is a problem on production

The documentation is direct about the locking. A plain REINDEX locks out writes, but not reads, on the parent table. It also takes an ACCESS EXCLUSIVE lock on the index being rebuilt, and because the query planner tries to take a lock on every index of the table regardless of the query, a plain REINDEX "blocks virtually any queries" on that table, apart from some prepared queries with cached plans that do not use the index. On a large table that can mean minutes of blocked traffic.

REINDEX CONCURRENTLY builds a new copy of the index next to the old one and swaps them, without taking locks that prevent inserts, updates or deletes:

-- one index
REINDEX INDEX CONCURRENTLY public.orders_customer_id_idx;

-- every index of one table
REINDEX TABLE CONCURRENTLY public.orders;

Under the hood, according to the documentation, it adds a temporary index definition, builds it in a first pass, adds rows written in the meantime in a second pass, switches constraints and names to the new index, marks the old one invalid and finally drops it. Each step runs in its own transaction.

The costs and restrictions

• It is slower. PostgreSQL scans the table twice for every index and waits for all existing transactions that could use the index. The documentation says it "takes significantly longer to complete" than a normal rebuild.
• Long transactions stall it. Because it waits for older transactions, a forgotten session can keep a rebuild hanging for hours. Check for them first; our article on "idle in transaction" sessions shows how.
• Not inside a transaction block. A plain REINDEX TABLE can run inside BEGIN ... COMMIT; REINDEX CONCURRENTLY cannot. Some migration tools wrap every step in a transaction, so check how yours handles it.
• One at a time per table. Only one concurrent index build can run on a table at a time, and no other schema changes are allowed on it meanwhile.
• Not for system catalogs, so REINDEX SYSTEM has no concurrent form.
• Not for exclusion constraints. Naming such an index directly raises an error; when you reindex a whole table or database concurrently, those indexes are skipped and need a plain REINDEX.
• It needs space. Old and new copies exist side by side until the swap, so make sure the disk has room for another copy of the index.

Privileges in current versions (PostgreSQL 17 and later): rebuilding a single index or table requires the MAINTAIN privilege on the table; a schema or database requires ownership or membership in pg_maintain.

Watching progress

Since PostgreSQL 12, pg_stat_progress_create_index reports REINDEX as well as CREATE INDEX:

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

The command column shows REINDEX CONCURRENTLY, and phase tells you where it is. If it sits in a phase such as waiting for old snapshots or waiting for readers before marking dead, it is not slow; it is waiting for another session to finish. That is the moment to look at pg_stat_activity for old transactions.

When it fails: _ccnew and _ccold

If a concurrent rebuild fails, for example because of a uniqueness violation in a unique index, the command fails but leaves an invalid index behind. Such an index is ignored by queries but, as the documentation points out, still adds overhead to every write. Find leftovers like this:

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

The suffix tells you what to do:

• _ccnew is the half-built new index. Drop it with DROP INDEX and run REINDEX CONCURRENTLY again once you have fixed the cause.
• _ccold is the original index that could not be dropped at the end. The rebuild itself succeeded, so just drop the old one.

A number may be appended to keep names unique, such as _ccnew1. Only REINDEX INDEX can rebuild an invalid index concurrently.

Rebuilding many indexes

The reindexdb utility wraps the same command and can run several in parallel:

reindexdb --concurrently --jobs=2 --table=public.orders --table=public.order_items mydb

--jobs opens that many connections and, as the documentation warns, increases load on the server, so keep it modest on production. It cannot be combined with --system. For a single large index, the build itself can use parallel workers: B-tree, GIN and BRIN support parallel builds, limited by maintenance_work_mem and max_parallel_maintenance_workers.

Do you need to rebuild regularly?

Usually not. Modern PostgreSQL removes a lot of index garbage on its own. Deduplication is enabled by default for B-tree indexes and, according to the B-tree documentation, significantly reduces the size of indexes on repeated values. Since PostgreSQL 14, bottom-up index deletion removes expired entries to prevent page splits, which the release notes describe as particularly helpful for indexes on tables with frequently updated columns. Scheduling a weekly rebuild of every index mostly adds I/O. Measure, rebuild the indexes that are clearly bloated, and fix the vacuum problem behind them.