Indexes Maintenance REINDEX · CONCURRENTLY · bloat

REINDEX

Rebuild bloated or fragmented indexes. Single-index, whole-table, or whole-database. Use CONCURRENTLY in production to avoid blocking writes.

What REINDEX does

Indexes accumulate "bloat" over time. Updates and deletes don't reclaim space inside the index immediately — they leave behind unused entries that the index has to step over during scans. Eventually a busy index can be much larger than the data it points to, and queries that should be fast start dragging.

REINDEX rebuilds the index from scratch. The result is a compact, efficient index identical in function to the old one — but smaller and faster to scan.

Syntax

REINDEX [(VERBOSE)]
    {INDEX | TABLE | SCHEMA | DATABASE | SYSTEM}
    [CONCURRENTLY]
    name;

Target	Effect
INDEX	Rebuild a single index by name.
TABLE	Rebuild every index on the named table.
SCHEMA	Rebuild every index in the named schema.
DATABASE	Rebuild every index in the current database.
SYSTEM	Rebuild system catalog indexes only (admin use).

Example 1 — rebuild a single index

SQL

REINDEX INDEX idx_first_name;

Example 2 — rebuild every index on a table

SQL

REINDEX TABLE customers;

Example 3 — rebuild a whole database

SQL

REINDEX DATABASE my_app;

Reserved for major maintenance windows — every index in the database is rebuilt sequentially, which can take a long time on a busy system.

CONCURRENTLY — rebuild without blocking

The plain form of REINDEX takes a heavy lock on the table — readers can still query but writes are blocked until the rebuild finishes. REINDEX CONCURRENTLY avoids that by building the new index alongside the old one and swapping them in at the end.

SQL

REINDEX INDEX CONCURRENTLY idx_first_name;
REINDEX TABLE CONCURRENTLY customers;

💡 Use CONCURRENTLY in production. The trade-off is that the operation takes longer overall and uses more disk during the rebuild. But it lets the application keep writing — usually a worthwhile exchange.

When should you REINDEX?

Symptom	What it suggests
Index size grew much larger than the table	Bloat — REINDEX will reclaim space.
Queries that used the index got slower over time	Bloat or fragmentation — REINDEX often helps.
Index is corrupt (rare, usually from hardware errors)	REINDEX rebuilds from scratch from table data.
You changed the operator class or collation	REINDEX picks up the new sort/comparison rules.

Most modern PostgreSQL versions handle bloat well via autovacuum, so frequent REINDEX is rarely needed. Keep an eye on index size with pg_stat_user_indexes + pg_relation_size() and reindex if you see growth that doesn't track table growth.

Inspect bloat before you reindex

SQL

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

The biggest indexes are usually the ones worth investigating. If an index is much bigger than expected — say larger than its underlying table — it's a likely candidate for a rebuild.

VERBOSE — see what's happening

SQL

REINDEX (VERBOSE) TABLE customers;
-- INFO:  index "customers_pkey" was reindexed
-- DETAIL:  CPU: user: 0.10 s, system: 0.02 s, elapsed: 0.13 s
-- INFO:  index "idx_email" was reindexed
-- ...

Recap

REINDEX rebuilds an index from scratch — same result, smaller and faster.
Targets: a single index, a table, a schema, or a whole database.
Use CONCURRENTLY in production to avoid blocking writes.
Modern Postgres rarely needs frequent reindexing; investigate index size growth first.
VERBOSE prints progress for each index rebuilt.