Reindex data

Pavel Semyonov

Contents

Check if reindexing is needed
Ways to recreate indexes
Update index statistics after reindexing

In Greengage DB, reindexing is an operation that recreates existing indexes based on the current table data. Functionally, it is similar to dropping the index and creating it again from scratch, but with different locking behavior. The difference is described in the DROP INDEX and CREATE INDEX combination section.

Reindexing can improve cluster performance or restore index functionality in the following cases:

Index bloat

Just like tables, indexes bloat over time when table rows are updated or deleted. Bloat increases the index size and can make index access slower because the on-disk structure becomes less efficient.
Changing the fillfactor index parameter

When you change an index’s fillfactor parameter, a rebuild is required for the change to take effect. Without reindexing, the existing index pages will retain the old fill pattern.
Index corruption

A software bug, disk failure, or other hardware issue may cause index corruption, making the index unusable for query execution. Reindexing replaces the corrupted index with a freshly built one in a valid state.

System catalog indexes can be rebuilt in the same way as indexes on user tables.

Table locks and resource usage during reindexing

Since reindexing consumes system resources and causes table locks, it is recommended to avoid unnecessary reindexing operations. To learn how to check whether an index should be rebuilt, see the Check if reindexing is needed section.

When planning reindex jobs, keep in mind the following:

Reindexing can lock system catalog tables and block concurrent activity, so it is best scheduled during low-use periods or maintenance windows.
Greengage DB builds a complete new copy of the index before replacing the old one, requiring temporary disk space roughly equal to the index size.
Reindexing operations consume memory from their initiator’s assigned resource group and are subject to its concurrency limit. Long-running operations can block other transactions in the same resource group.
On large distributed tables, reindexing can also produce significant network traffic between segments and the master.

Plan maintenance windows accordingly and monitor resource usage during the operation.

To decide whether an index needs to be rebuilt, consider the following:

Observed performance degradation

If the same queries on the same data volumes become slower, the indexes they use may be bloated or corrupted. Use EXPLAIN or EXPLAIN ANALYZE to verify whether the queries are using the intended indexes. See Examine index usage for details.
Growing index size

If the size reported by pg_relation_size() for an index grows disproportionately compared to the table size, it is likely to be bloated.
Significant data change

A large volume of data modifications — either in a single bulk operation or in many small transactions — can lead to index bloat. In such cases, rebuilding the index can restore optimal performance.

NOTE

When bulk inserts or updates are performed, it’s often faster to drop indexes on the affected table and recreate them after the operation. Learn more in DROP INDEX and CREATE INDEX combination.

Greengage DB provides several ways to rebuild indexes:

The REINDEX SQL commands.
The reindexdb command-line utility.
A combination of DROP INDEX and CREATE INDEX commands.

In some cases, dropping and recreating indexes manually can be faster than using REINDEX. This is especially true during bulk data loads or major data modifications. See DROP INDEX and CREATE INDEX combination for details.

Use the REINDEX SQL command with the appropriate clause to rebuild indexes:

REINDEX INDEX — rebuilds the specified index.
```
REINDEX INDEX orders_total_idx;
```
REINDEX TABLE — rebuilds all indexes on the specified table.
```
REINDEX TABLE orders;
```
REINDEX DATABASE — rebuilds all indexes in the specified database.
```
REINDEX DATABASE books_store;
```
REINDEX SYSTEM — rebuilds indexes on system catalogs within the specified database, including shared system catalogs.
```
REINDEX SYSTEM books_store;
```

NOTE

REINDEX DATABASE and REINDEX SYSTEM have the following limitations:

They work only within the current database. You must specify the current database name when using them.
They cannot be run inside a transaction.

The reindexdb utility is a command-line tool for rebuilding indexes without using the SQL console. It is well suited for automated maintenance via cron jobs, scripts, or orchestration tools.

To recreate all indexes in a database, execute:

$ reindexdb books_store

The full form includes the -d (--dbname) option name:

$ reindexdb -d books_store

To rebuild a specific index or all indexes for specific tables, use the -i (--index) and -t (--table) options:

$ reindexdb -i orders_total_idx books_store
$ reindexdb -t orders books_store

NOTE

When using the long option forms --dbname, --table, and --index, specify the name after an equals sign:

$ reindexdb --table=orders --dbname=books_store

To rebuild multiple indexes or indexes of multiple tables, specify each of them in a separate option:

$ reindexdb -i orders_total_idx -i orders_comment_idx books_store
$ reindexdb -i orders_total_idx -t customers books_store

To reindex system catalogs, use the -s (--system) option:

$ reindexdb -s books_store

The -a (--all) option rebuilds indexes of all databases in the Greengage DB cluster:

$ reindexdb --all

When invoked with -a/--all, reindexdb connects to the postgres database (or to template1 if postgres does not exist) and gathers the list of databases to reindex. You can explicitly select a database to connect to in the --maintenance-db option:

$ reindexdb --all --maintenance-db admin_database

Two options control the reindexdb output:

-e (--echo) — prints the SQL commands that the utility executes.
-q (--quiet) — suppresses the output.

REINDEX is conceptually equivalent to removing the index (DROP INDEX) and creating it again (CREATE INDEX). An index may be rebuilt as follows:

DROP INDEX orders_total_idx;
CREATE INDEX orders_total_idx ON orders (total);

The differences between REINDEX and DROP INDEX-CREATE INDEX combination are:

Locking behavior
- REINDEX acquires an ACCESS EXCLUSIVE lock on the index and a SHARE UPDATE EXCLUSIVE lock on the table. This blocks all index usage and prevents writes to the table until the operation finishes. Queries that attempt to use the index are blocked for the entire duration of the reindexing operation.
- DROP INDEX acquires an ACCESS EXCLUSIVE lock on the parent table for the drop phase only. Once the index is removed, queries that would have used it switch to sequential scans or other indexes. CREATE INDEX then blocks table writes but allows reads to continue.
  
  NOTE
  
  In both cases, the lack of an index may cause some queries to become significantly slower until the index is available again.
Command flexibility

REINDEX can rebuild all indexes of a table, schema, or database in one command, while DROP INDEX and CREATE INDEX combination operates on a single index.
Performance during bulk data changes

Dropping indexes before large data loads and recreating them afterward is often faster than updating them during inserts or updates. When an index exists, each inserted row must also be inserted into the index, increasing disk and CPU load.

Example:
```
DROP INDEX orders_total_idx;
-- INSERT ...
CREATE INDEX orders_total_idx ON orders (total);
```

After REINDEX, index statistics such as reltuples and relpages in the pg_class system catalog are reset to zero. To refresh these statistics for the query planner, run ANALYZE on the tables whose indexes were rebuilt:

ANALYZE orders;

Remove expired table rows via VACUUM

Manage spill files

Reindex data

Check if reindexing is needed

Ways to recreate indexes

REINDEX commands

reindexdb utility

DROP INDEX and CREATE INDEX combination

Update index statistics after reindexing