SQL REINDEX

What does the SQL REINDEX command do?

Rebuilds existing indexes to eliminate bloat and restore peak performance.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL REINDEX: PostgreSQL (native), SQLite (supports REINDEX keyword, different syntax), SQL Server (use ALTER INDEX ... REBUILD), Oracle (ALTER INDEX ... REBUILD), FreeBSD, MariaDB/MySQL (no REINDEX; use OPTIMIZE TABLE)

SQL REINDEX Full Explanation

REINDEX recreates one or more existing indexes using the current index definition. The command scans the underlying table (or catalog) and writes a brand-new copy of the index, then swaps it in place of the old one. This removes logical bloat that accumulates after many UPDATE or DELETE operations, shrinking on-disk size and speeding up future lookups.REINDEX can target a single index, all indexes on a table, an entire schema, or even the whole database/system catalogs. Each subtype acquires different locks:- INDEX / TABLE requires an ACCESS EXCLUSIVE lock on the index or table.- SCHEMA / DATABASE / SYSTEM hold exclusive locks on each object as it is processed.Use the CONCURRENTLY option to keep the table readable during the rebuild, at the cost of a longer run time and higher temporary disk usage. CONCURRENTLY cannot be combined with SYSTEM or executed inside a transaction block.Caveats:1. Requires disk space roughly equal to the existing index size.2. Large tables may experience write amplification on replication.3. Fails if another session holds a conflicting lock.4. In PostgreSQL, only B-tree, GiST, GIN and SP-GiST indexes support concurrent rebuild.Because REINDEX copies data rather than reorganizing it in place, it guarantees a fully optimized structure once complete.

SQL REINDEX Syntax

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

SQL REINDEX Parameters

object_type (enum) - INDEX, TABLE, SCHEMA, DATABASE, or SYSTEM indicating the scope of rebuild
name (ident) - Name of the index, table, schema, or database to rebuild
CONCURRENTLY (flag) - Rebuild without blocking reads; not allowed for SYSTEM
VERBOSE (flag) - Emit progress messages to the client

Example Queries Using SQL REINDEX

-- Rebuild a single index
REINDEX INDEX idx_orders_customer_id;

-- Rebuild all indexes on one table concurrently
REINDEX TABLE CONCURRENTLY orders;

-- Rebuild every index in the current database (blocking)
REINDEX DATABASE myapp_prod;

-- Rebuild system catalogs after major upgrade
REINDEX SYSTEM myapp_prod;

Expected Output Using SQL REINDEX

New index files replace the originals
Disk footprint of affected indexes decreases, and query plans using those indexes run faster
The server prints confirmation messages such as "REINDEX" for each object processed

Use Cases with SQL REINDEX

Remove index bloat after heavy UPDATE/DELETE workloads
Recover from index corruption reported by pg_amcheck or system crash
Speed up sequential scans that unexpectedly switched to index scans due to bloated statistics
Clean up inherited btree metadata after major PostgreSQL upgrades
Run scheduled maintenance in combination with VACUUM FULL for optimal storage reclamation

Common Mistakes with SQL REINDEX

Running REINDEX inside a transaction block when using CONCURRENTLY (results in ERROR)
Forgetting the extra disk space requirement and filling the filesystem
Expecting CONCURRENTLY to avoid all locks; it still requires SHARE UPDATE EXCLUSIVE at moments
Using REINDEX DATABASE on very large clusters during peak hours, causing extensive blocking
Thinking REINDEX replaces the need for periodic VACUUM or ANALYZE

First Introduced In

PostgreSQL 7.2

Frequently Asked Questions

When should I choose REINDEX over VACUUM?

Use REINDEX when an index is heavily bloated or shows corruption. VACUUM cleans up dead tuples but cannot rewrite index pages.

Does REINDEX block my application?

Without CONCURRENTLY, REINDEX acquires an ACCESS EXCLUSIVE lock and blocks all access to the target object. The CONCURRENTLY option keeps reads running but still pauses writes briefly.

How long does REINDEX CONCURRENTLY take?

It runs slower than a standard REINDEX because it builds a new index in the background and validates it twice. Duration scales with table size and system I/O throughput.

Is REINDEX safe in production?

Yes, if you monitor disk space and pick low-traffic windows. Prefer the CONCURRENTLY option for large, read-heavy tables.