REINDEX command for rebuilding indexes - PolarDB

REINDEX rebuilds one or more indexes from the data in their underlying tables, replacing the old indexes.

Synopsis

REINDEX [ ( option [, ...] ) ] { INDEX | TABLE | SCHEMA | DATABASE | SYSTEM } [ CONCURRENTLY ] name

Where option can be one of:

CONCURRENTLY [ boolean ]
TABLESPACE new_tablespace
VERBOSE [ boolean ]

When to use REINDEX

Use REINDEX in these situations:

Corrupted index: An index no longer contains valid data due to a software bug or hardware failure. REINDEX recovers it.
Bloated index: An index has accumulated many empty or nearly-empty pages. This can happen with B-tree indexes in PolarDB under certain uncommon access patterns. REINDEX compacts the index by writing a fresh copy without the dead pages.
Storage parameter change: You changed a storage parameter such as fillfactor and want the change to take full effect.
Failed concurrent build: If an index build fails with the CONCURRENTLY option, the index is left in an invalid state. Use REINDEX INDEX to rebuild it concurrently.

Note

Only REINDEX INDEX can perform a concurrent build on an invalid index.

Parameters

Parameter	Description
`INDEX`	Recreate the specified index.
`TABLE`	Recreate all indexes of the specified table. If the table has a secondary TOAST table, that is reindexed as well.
`SCHEMA`	Recreate all indexes of the specified schema. If a table in this schema has a secondary TOAST table, that is reindexed as well. Indexes on shared system catalogs are also processed. Cannot be executed inside a transaction block.
`DATABASE`	Recreate all indexes within the current database. Indexes on shared system catalogs are also processed. Cannot be executed inside a transaction block.
`SYSTEM`	Recreate all indexes on system catalogs within the current database. Indexes on shared system catalogs are included. Indexes on user tables are not processed. Cannot be executed inside a transaction block.
`name`	The name of the index, table, or database to reindex. Index and table names can be schema-qualified. `REINDEX DATABASE` and `REINDEX SYSTEM` can only reindex the current database, so their argument must match the current database's name.
`CONCURRENTLY`	Rebuild the index without taking locks that prevent concurrent inserts, updates, or deletes on the table. A standard index rebuild locks out writes (but not reads) on the table until it completes. For temporary tables, `REINDEX` is always non-concurrent because no other session can access them, and non-concurrent reindexing is cheaper.
`VERBOSE`	Print a progress report as each index is reindexed.

Examples

Rebuild a single index:

REINDEX INDEX my_index;

Rebuild all indexes on the table my_table:

REINDEX TABLE my_table;

Rebuild all indexes in a database without trusting the system indexes to be valid:

$ export PGOPTIONS="-P"
$ psql broken_db
...
broken_db=> REINDEX DATABASE broken_db;
broken_db=> \q

Rebuild indexes for a table without blocking reads or writes while reindexing is in progress:

REINDEX TABLE CONCURRENTLY my_broken_table;

Usage notes

Permissions

Reindexing a single index or table requires owning that index or table.
Reindexing a schema or database requires owning that schema or database.
Non-superusers can rebuild indexes on tables owned by other users.
When a non-superuser runs REINDEX DATABASE, REINDEX SCHEMA, or REINDEX SYSTEM, indexes on shared catalogs are skipped unless the user owns the catalog.
Superusers can reindex anything.

Comparison with DROP INDEX and CREATE INDEX

REINDEX differs from dropping and recreating an index:

REINDEX locks out writes but not reads of the index's parent table. It also takes an ACCESS EXCLUSIVE lock on the specific index being processed (blocking reads that try to use that index).
DROP INDEX takes an ACCESS EXCLUSIVE lock on the parent table, blocking both reads and writes momentarily.
The subsequent CREATE INDEX locks out writes but not reads. Because the index does not exist yet, no read attempts to use it, so there is no index-level blocking—but reads may fall back to expensive sequential scans.

Limitations

Rebuilding indexes on partitioned tables or partition indexes is not supported. Rebuild indexes separately for each partition.
REINDEX SYSTEM does not support CONCURRENTLY because system catalogs cannot be reindexed concurrently.
Indexes for exclusion constraints cannot be reindexed concurrently. If such an index is named directly, an error is raised. If a table or database with exclusion constraint indexes is reindexed concurrently, those indexes are skipped. You can still reindex them without CONCURRENTLY.
REINDEX CONCURRENTLY cannot run inside a transaction block. Regular REINDEX TABLE and REINDEX INDEX can.
Regular index builds permit other regular index builds on the same table to occur simultaneously, but only one concurrent index build can occur on a table at a time.

Recovering from system catalog corruption

If you suspect corruption of an index on a system table, the server may crash at startup due to reliance on the corrupted indexes. To recover, start the server with the -P option, which prevents it from using indexes for system catalog lookups.

Option 1: Single-user mode

Start a single-user PostgreSQL server with -P, then run REINDEX DATABASE, REINDEX SYSTEM, REINDEX TABLE, or REINDEX INDEX depending on how much you want to reconstruct. If in doubt, use REINDEX SYSTEM to rebuild all system indexes. Quit the single-user session and restart the regular server.

Option 2: Regular session with `-P`

Set the PGOPTIONS environment variable to -P before starting a libpq-based client:

export PGOPTIONS="-P"
psql <your_database>

This method does not lock out other clients, but consider preventing additional users from connecting to the damaged database until repairs are complete.

Rebuilding indexes concurrently

Standard index rebuilds lock out writes on the table for the duration of the build. For large tables in production, this can be unacceptably disruptive. Use the CONCURRENTLY option to rebuild indexes with minimal write locks.

Concurrent reindexing requires two full table scans per index and must wait for existing transactions that could use the index to complete. This means it takes longer than a standard rebuild and places additional CPU, memory, and I/O load on the system.

How concurrent reindexing works

Each step runs in a separate transaction. If multiple indexes are being rebuilt, all indexes complete each step before moving to the next.

A new transient index definition is added to the pg_index catalog. A SHARE UPDATE EXCLUSIVE session-level lock is taken on the indexes being reindexed and their associated tables to prevent schema modifications during processing.
A first pass builds each new index. Once built, pg_index.indisready is set to true, making the index visible to new inserts. This step runs in a separate transaction per index.
A second pass adds tuples that were inserted during the first pass. This step also runs in a separate transaction per index.
All constraints referencing the old index are updated to point to the new index definition, and the index names are swapped. At this point, pg_index.indisvalid is set to true for the new index and false for the old, and a cache invalidation causes all sessions referencing the old index to invalidate their cached plans.
pg_index.indisready is set to false for the old indexes after waiting for running queries that might reference them to complete.
The old indexes are dropped and the SHARE UPDATE EXCLUSIVE session locks are released.

Handling invalid indexes after a failed concurrent reindex

If a concurrent reindex fails—for example, due to a uniqueness violation in a unique index—an invalid index is left behind in addition to the original. This index is ignored for queries but still incurs update overhead. Use \d in psql to identify invalid indexes:

postgres=# \d tab
       Table "public.tab"
 Column |  Type   | Modifiers
--------+---------+-----------
 col    | integer |
Indexes:
    "idx" btree (col)
    "idx_ccnew" btree (col) INVALID

To recover:

Suffix	Meaning	Recovery
`ccnew`	Transient index created during the failed concurrent build	Drop with `DROP INDEX`, then retry `REINDEX CONCURRENTLY`.
`ccold`	Original index that could not be dropped (rebuild completed successfully)	Drop with `DROP INDEX`.

What's next

CREATE INDEX: Create a new index on a table.
DROP INDEX: Remove an existing index.