How and when should you use REINDEX in PostgreSQL?
REINDEX rebuilds an index from the table's current data and swaps it in. Run it for four reasons: bloat that vacuum cannot reclaim, a corrupt index, an INVALID index from a failed concurrent build, or a collation version change after an OS upgrade. Use REINDEX CONCURRENTLY so writes keep working. Routine scheduled reindexing of everything is almost always wasted I/O.
Key facts
| Fact | Value | |---|---| | Forms | REINDEX INDEX / TABLE / SCHEMA / DATABASE / SYSTEM (docs) | | Plain REINDEX locks | ACCESS EXCLUSIVE on the index, SHARE on the table — reads allowed, writes blocked (docs) | | REINDEX CONCURRENTLY locks | SHARE UPDATE EXCLUSIVE — reads and writes continue (docs) | | CONCURRENTLY available since | PostgreSQL 12 (docs) | | Disk needed for a concurrent rebuild | Room for a second full copy of the index until the swap (docs) | | Failed concurrent rebuild leaves | An invalid index suffixed _ccnew (or _ccold) that you must drop (docs) | | Collation mismatch surfaced by | pg_database.datcollversion / pg_collation.collversion (docs) | | Exact bloat measurement | pgstattuple's pgstatindex() — accurate, full scan (docs) | | Our fleet | Across 50 monitored instances, 44% carry at least one index whose estimated bloat is past the warning threshold |
Why this happens
A B-tree index is a set of pages holding pointers to table rows. When a row is deleted or updated, the index entry becomes dead. VACUUM removes dead index entries and marks the space reusable, but it does not give pages back to the filesystem unless an entire page empties out, and it never re-balances the tree. So a table with a churning workload accumulates half-empty index pages that stay allocated. The routine reindexing notes put it plainly: B-tree pages that have become completely empty are reclaimed, but "there is still a possibility of inefficient space use" where most but not all of a page's keys were deleted.
REINDEX fixes this by building a completely new index from the table data, in one pass, densely packed, then replacing the old one. That is also why it fixes a corrupt index: the new one is derived from the heap, not from the damaged index.
The fourth reason is the one that catches people out. Text indexes store keys in collation order, and collation order is defined by the operating system's glibc or by ICU — not by PostgreSQL. Upgrade the distro, move to a new host, or switch base images, and the library's sort rules can change underneath an index that was built with the old rules. The index is now sorted wrongly for the comparison function the planner uses, so index scans can silently miss rows. PostgreSQL records the collation version it saw at creation time and warns when it no longer matches; see ALTER COLLATION ... REFRESH VERSION and the collation documentation. The warning looks like this:
WARNING: database "app" has a collation version mismatch
DETAIL: The database was created using collation version 2.36, but the operating system provides version 2.39.
HINT: Rebuild all objects in this database that use the default collation and run ALTER DATABASE app REFRESH COLLATION VERSION.
Nothing is broken loudly. Queries return fewer rows than they should, and that is all.
How to detect it
Start with the cheap estimate, not the expensive scan. The widely used estimate query reads pg_class and pg_stats only, so it costs nothing and can run continuously:
SELECT
schemaname,
relname AS table_name,
indexrelname AS index_name,
pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
idx_scan AS index_scans
FROM pg_stat_user_indexes
JOIN pg_index USING (indexrelid)
WHERE NOT indisprimary
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 20;
That gives you size and usage. For an actual bloat figure on a candidate you have already narrowed down, the exact answer comes from pgstattuple:
CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT index_size, avg_leaf_density, leaf_fragmentation
FROM pgstatindex('public.orders_customer_id_idx');
avg_leaf_density near the 90% fill factor default is healthy; 40% means most of the index is air. Be aware that pgstatindex() reads every page of the index, so run it on one index at a time on a busy system, never across a whole schema. The same trade-off applies to tables — see how to measure and fix table bloat.
Check for a collation mismatch separately, because bloat queries will never show it:
SELECT datname, datcollate, datctype, datcollversion,
pg_database_collation_actual_version(oid) AS current_version
FROM pg_database
WHERE datcollversion IS DISTINCT FROM pg_database_collation_actual_version(oid);
And check for indexes already marked invalid, which is what a failed concurrent index build leaves behind:
SELECT n.nspname, c.relname
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE NOT i.indisvalid;
Any row here is an index that costs write throughput and disk while contributing nothing to query planning.
How MyDBA shows this

The Index Advisor's Bloated Indexes card — subtitled "Fragmented, need REINDEX" — lists each index with an estimated bloat percentage and the wasted bytes behind it, which is the list a rebuild should be driven from rather than a schema-wide sweep. The same data feeds the B4 index-bloat check in the storage health domain (warning past 30% bloat, critical past 50%), and FF5 adds a fill-factor voice on the same index. MyDBA runs the cheap statistical estimate continuously and only replaces it with a bounded pgstatindex() verification for a specific candidate, because scanning every index every hour would cost more than the bloat does. On the Storage page, Index Size Distribution and Top Indexes (by size) show whether an index is growing in step with its table or drifting past it.

You can see this on your own database with a free PostgreSQL health check.
How to fix it
1. Confirm you have the disk space. A concurrent rebuild holds the old and new index simultaneously, so you need at least the index's current size free, plus headroom for WAL. Check first:
SELECT pg_size_pretty(pg_relation_size('public.orders_customer_id_idx'));
2. Rebuild concurrently. This is the default choice on anything serving traffic:
REINDEX INDEX CONCURRENTLY public.orders_customer_id_idx;
It takes SHARE UPDATE EXCLUSIVE, so INSERT, UPDATE and DELETE continue. It does two table scans and waits for old transactions to drain, so it takes meaningfully longer than the plain form. That is the trade you are making.
3. Use the plain form only in a maintenance window. REINDEX INDEX public.orders_customer_id_idx; is faster and needs less peak disk, but it takes ACCESS EXCLUSIVE on the index and SHARE on the table: reads continue, writes stop until it finishes. The wider forms escalate accordingly:
REINDEX TABLE CONCURRENTLY public.orders; -- every index on one table
REINDEX SCHEMA CONCURRENTLY public; -- every index in a schema
REINDEX DATABASE CONCURRENTLY app; -- every index in the database
REINDEX SYSTEM app; -- catalogs only; no CONCURRENTLY
REINDEX SYSTEM cannot run concurrently, because system catalog indexes cannot be rebuilt that way. Neither can an index on a system catalog, an exclusion-constraint index, or one being rebuilt inside a transaction block.
4. Clean up after a failure. If a concurrent rebuild is cancelled or errors, PostgreSQL leaves behind an invalid index named after the original with a _ccnew suffix (_ccold if the failure was at the swap). It is never used by the planner but is still maintained on every write. Find and drop it:
SELECT n.nspname, c.relname
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE NOT i.indisvalid AND c.relname LIKE '%_cc%';
DROP INDEX CONCURRENTLY public.orders_customer_id_idx_ccnew;
Dropping safely has its own subtleties — see how to drop an index in PostgreSQL.
5. For a collation change, rebuild then refresh. Rebuild every index over a text column, then tell PostgreSQL the version is now current:
REINDEX DATABASE CONCURRENTLY app;
ALTER DATABASE app REFRESH COLLATION VERSION;
Do not refresh the version first. That silences the warning while leaving the wrongly-sorted indexes in place, which is the worst of both outcomes.
6. Consider pg_repack when the table is the problem. If the table itself is bloated, rebuilding its indexes treats a symptom. pg_repack rebuilds table and indexes online without the ACCESS EXCLUSIVE lock that VACUUM FULL needs. It is an external extension, needs a trigger on the table and roughly double the disk, and it is the right tool when VACUUM has stopped keeping up.
From the shell, for a whole database with progress reporting:
psql -d app -c "REINDEX DATABASE CONCURRENTLY app;"
# or, in another session, watch it:
psql -d app -c "SELECT * FROM pg_stat_progress_create_index;"
How to prevent it
Do not schedule a blanket reindex. On most instances, most indexes are not bloated, and rebuilding a healthy index burns I/O, WAL and replica bandwidth to produce an index that is the same size it was. The routine reindexing documentation is explicit that periodic reindexing is worthwhile only in specific circumstances. Measure, pick the worst few, rebuild those.
What is worth doing on a schedule:
- Watch index size against table size. An index growing faster than its table is the signal worth alerting on, not an absolute byte count.
- Keep autovacuum ahead of the workload. Bloat that keeps coming back after a rebuild is an autovacuum tuning problem, not a reindexing problem. Lower
autovacuum_vacuum_scale_factoron the hot tables. - Check
datcollversionas part of every OS upgrade runbook. Base-image bumps and managed-service maintenance both change glibc, and nothing in the application will tell you. - Alert on invalid indexes. One row in the
indisvalidquery above is a failed build nobody noticed. - Drop before you rebuild. An unused index does not need reindexing — see index usage optimization for proving it is unused first.
FAQ
Does REINDEX lock the table?
Plain REINDEX takes ACCESS EXCLUSIVE on the index and SHARE on the table, so reads continue but writes block for the duration. REINDEX CONCURRENTLY takes only SHARE UPDATE EXCLUSIVE, so reads and writes both continue; it takes longer and needs space for a second copy of the index while it runs.
Is REINDEX CONCURRENTLY safe on a production primary?
Generally yes, and it is the form to prefer, but plan for three things: it needs disk for a full second copy of the index until the swap, it waits for long-running transactions to finish before it can complete, and if it fails you are left with an invalid _ccnew index that you must drop manually. It also generates significant WAL, which replicas have to keep up with.
How often should I reindex a PostgreSQL database?
There is no useful fixed interval. Rebuild an index when you have measured that it is bloated, when it is invalid, when it is corrupt, or when the collation version changed. Across the 50 instances we monitor, 44% have at least one index past the bloat warning threshold — but that is rarely the same index twice, which is exactly why a blanket schedule wastes effort.
Will VACUUM fix index bloat instead?
Partly. VACUUM removes dead index entries and reclaims fully empty B-tree pages, so it stops bloat growing without bound. It does not compact partly-full pages or rebalance the tree, so an index that churned heavily stays larger and less dense than a fresh one. Only a rebuild returns the pages to the filesystem.
What does the "collation version mismatch" warning actually mean?
It means the glibc or ICU version now on the host sorts text differently from the version in use when the index was built — usually after an OS upgrade or a move to a new machine. Affected text indexes are sorted by the old rules, so index scans can return incomplete results. Rebuild the indexes, then run ALTER DATABASE ... REFRESH COLLATION VERSION.
Part of the PostgreSQL index types: the complete guide guide. Last verified against PostgreSQL 18, 2026-09-17.