Knowing When to Rebuild PostgreSQL Indexes With REINDEX

Indexes are essential for improving PostgreSQL query performance. They help the database locate records without scanning entire tables, support sorting and filtering, and enforce uniqueness constraints.

However, indexes do not remain perfectly efficient under every workload. Database corruption, accumulated empty index pages, failed concurrent index builds, and changes to index storage parameters can create situations where rebuilding an index becomes necessary.

PostgreSQL provides the REINDEX command to rebuild indexes using the data stored in their underlying tables.

The challenge for database administrators is knowing when to use it. Rebuilding indexes unnecessarily can consume significant CPU, memory, disk I/O, and storage space. On production databases, it can also block write operations if the wrong rebuilding method is selected.

This article explains when PostgreSQL indexes should be rebuilt, how to identify invalid indexes, how to investigate index bloat, and how to choose between standard and concurrent reindexing.

What Does REINDEX Do in PostgreSQL?

The REINDEX command recreates an existing index using the data stored in the associated table.

During the operation, PostgreSQL builds a replacement index and uses it to replace the existing index.

For example, consider a table containing customer information:

CREATE TABLE customers (
    customer_id BIGINT PRIMARY KEY,
    customer_name VARCHAR(150),
    email VARCHAR(200),
    created_at TIMESTAMP
);

Suppose the application frequently searches customers by email.

You create an index:

CREATE INDEX idx_customers_email
ON customers(email);

PostgreSQL can use this index to locate matching customer records more efficiently.

Over time, the index may become corrupted, contain unnecessary empty pages, or require rebuilding after certain storage parameter changes.

You can rebuild it using:

REINDEX INDEX idx_customers_email;

PostgreSQL recreates the index from the underlying table data.

The command does not remove application records or recreate the table itself. It rebuilds the index structure.

REINDEX command options

PostgreSQL supports several levels of reindexing.

For example:

REINDEX INDEX idx_customers_email;

To rebuild all indexes associated with a table:

REINDEX TABLE customers;

To rebuild indexes in a schema:

REINDEX SCHEMA public;

To rebuild indexes across the current database:

REINDEX DATABASE application_db;

Use the narrowest appropriate operation. Rebuilding one problematic index is generally preferable to rebuilding every index in a database without evidence that they need it.

When Should You Rebuild PostgreSQL Indexes?

PostgreSQL documents several situations where REINDEX is appropriate.

The most important are index corruption, index bloat, changes to index storage parameters, and failed concurrent index creation or rebuilding.

Understanding these scenarios helps prevent unnecessary maintenance operations.

When an Index Becomes Corrupted

Index corruption occurs when an index no longer contains valid information or its internal structure becomes inconsistent.

Although PostgreSQL is designed to maintain index consistency, corruption can occur because of:

• Hardware failures.

• Storage problems.

• Operating system issues.

• Software defects.

• Unexpected failures involving database files.

For example, an application may execute a query that uses an index but return unexpected results or encounter an index-related error.

However, an incorrect query result does not automatically mean the index is corrupted. Incorrect SQL, stale application assumptions, transaction behavior, and data problems can produce similar symptoms.

Investigate the underlying issue before rebuilding the index.

Rebuilding a corrupted index

If an index on a user table is confirmed or strongly suspected to be corrupted, you can rebuild it directly:

REINDEX INDEX idx_customers_email;

For all indexes associated with a table:

REINDEX TABLE customers;

A successful rebuild recreates the index using the table data.

However, REINDEX is not a universal solution for storage corruption. If the underlying table data, database files, or storage device are damaged, rebuilding an index may not resolve the root cause.

Investigate database logs, operating system logs, storage health, and PostgreSQL consistency checks as part of the recovery process.

For suspected corruption in system catalog indexes, the recovery procedure is more sensitive because PostgreSQL uses those indexes during normal operation. Follow the PostgreSQL documentation's specific recovery procedure rather than treating system indexes like ordinary application indexes.

When an Index Becomes Bloated

Index bloat is another reason administrators may consider REINDEX.

Index bloat refers to space consumed by an index that is no longer efficiently used for its current contents.

It can occur when indexes contain many empty or sparsely populated pages.

Consider a table where records are frequently updated and deleted.

DELETE FROM orders
WHERE order_date < DATE '2020-01-01';

PostgreSQL uses MVCC (Multi-Version Concurrency Control) to manage row versions.

When a row is updated or deleted, its previous version may remain until it is no longer visible to any active transaction and can be cleaned up.

Indexes can also retain entries associated with obsolete row versions until maintenance removes them.

Over time, certain workloads can cause B-tree indexes to contain many underutilized pages.

However, not every dead tuple or large index indicates bloat.

Why VACUUM does not always solve index bloat

PostgreSQL's VACUUM command helps reclaim space from dead tuples and makes space available for reuse.

For example:

VACUUM customers;

VACUUM performs important maintenance, but ordinary VACUUM does not generally shrink the physical size of an index file by returning its trailing space to the operating system.

It can clean up index entries and make space reusable within the index.

REINDEX takes a different approach: it builds a new index structure from the table data.

This can remove unnecessary empty or sparsely populated pages and reduce index size.

The two commands serve different purposes.

Do not run REINDEX simply because VACUUM has been executed or because a table contains dead tuples. First determine whether the index itself has a meaningful space-efficiency problem.

How to Identify Invalid Indexes

An index can become invalid when an index creation or concurrent rebuilding operation fails.

For example, a concurrent index operation may fail because of a uniqueness violation or another error.

The resulting index may remain in the system catalogs with an invalid status.

An invalid index is not usable for normal query planning, but it can still consume disk space and create maintenance overhead.

PostgreSQL stores index validity information in the pg_index system catalog.

You can inspect it using:

SELECT
    n.nspname AS schema_name,
    t.relname AS table_name,
    i.relname AS index_name,
    ix.indisvalid,
    ix.indisready,
    ix.indislive
FROM pg_index ix
JOIN pg_class i
    ON i.oid = ix.indexrelid
JOIN pg_class t
    ON t.oid = ix.indrelid
JOIN pg_namespace n
    ON n.oid = t.relnamespace
WHERE NOT ix.indisvalid
   OR NOT ix.indisready
   OR NOT ix.indislive
ORDER BY
    n.nspname,
    t.relname,
    i.relname;

The important fields are:

An index with an invalid status needs investigation.

Do not assume every invalid index should immediately be rebuilt. Determine why it became invalid and whether it is an unfinished concurrent index operation.

Inspect index definitions

Once you identify a suspicious index, retrieve its definition:

SELECT
    schemaname,
    tablename,
    indexname,
    indexdef
FROM pg_indexes
WHERE indexname = 'idx_customers_email';

You can also inspect index details through psql:

\d customers

Review the table's indexes and identify any that are marked INVALID.

For an invalid index left by a failed concurrent build, PostgreSQL documents recovery options that depend on the state of the replacement index.

A failed concurrent rebuild may leave an index with a suffix such as ccnew or ccold.

The recovery procedure should follow the documented state of the index. In particular, a failed replacement index may need to be dropped before attempting the rebuild again.

Do not drop the original valid index simply because another index with a similar name is invalid.

When Index Storage Parameters Change

PostgreSQL indexes support storage parameters that influence how index pages are maintained.

One example is fillfactor.

Fillfactor controls how full index pages are initially packed during index creation.

For a B-tree index, the default fillfactor is generally 90.

You can change the setting:

ALTER INDEX idx_customers_email
SET (fillfactor = 80);

However, changing the storage parameter does not immediately rebuild the existing index pages.

To ensure that the new setting takes effect throughout the index, rebuild it:

REINDEX INDEX idx_customers_email;

Fillfactor is a workload-dependent setting.

For example, a workload with frequent inserts and updates may benefit from leaving additional space on index pages, depending on the access pattern.

Do not reduce fillfactor without testing. A lower value can increase index size and memory requirements.

How to Investigate Index Bloat

Index bloat should be measured or investigated rather than assumed from the index's physical size.

A large index is not necessarily bloated. It may simply contain many valid entries because the table itself is large.

Start by checking index sizes.

SELECT
    schemaname,
    tablename,
    indexname,
    pg_size_pretty(
        pg_relation_size(indexrelid)
    ) AS index_size
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC;

This query helps identify the largest indexes in the current database.

Large indexes deserve attention when they are associated with performance problems or unusual space growth.

Check index usage statistics

PostgreSQL provides pg_stat_user_indexes for monitoring index activity.

For example:

SELECT
    schemaname,
    relname AS table_name,
    indexrelname AS index_name,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch,
    pg_size_pretty(
        pg_relation_size(indexrelid)
    ) AS index_size
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;

Important columns include:

• idx_scan: Number of index scans initiated.

• idx_tup_read: Number of index entries returned by scans.

• idx_tup_fetch: Number of live table rows fetched through the index.