reindexdb

Reindex a specific database

$ reindexdb [database_name]

$ reindexdb [database_name] [[-h|--host]] [hostname] [[-p|--port]] [port] [[-U|--username]] [username]

$ reindexdb [[-a|--all]]

$ reindexdb [database_name] [[-t|--table]] [table_name]

$ reindexdb [database_name] [[-i|--index]] [index_name]

$ reindexdb [database_name] [[-S|--schema]] [schema_name]

$ reindexdb [database_name] [[-v|--verbose]]

$ reindexdb [database_name] [[-j|--jobs]] [number_of_jobs]

reindexdb [connection-option...] [option...] [dbname]

--all, -a
    Reindex all databases.

--dbname=dbname, -d dbname
    Specify the name of the database to connect to and reindex.

--table=table, -t table
    Reindex a specific table. Can be specified multiple times.

--index=index, -i index
    Reindex a specific index. Can be specified multiple times.

--schema=schema, -s schema
    Reindex a specific schema. Can be specified multiple times.

--jobs=N, -j N
    Use N concurrent jobs for reindexing.

--concurrently
    Reindex tables and indexes concurrently, minimizing downtime.

--full, -F
    Perform a full rebuild of the index by scanning the entire table.

--echo-queries, -e
    Show the SQL commands being executed by reindexdb.

--quiet, -q
    Suppress progress messages.

--verbose, -v
    Enable verbose output, including detailed progress.

--dry-run
    Print the commands that would be executed without actually running them.

--if-exists
    Do not error if the specified database, table, or index does not exist.

--host=hostname, -h hostname
    Database server host name or IP address.

--port=port, -p port
    Database server port number.

--username=username, -U username
    User name to connect as.

--no-password, -w
    Never prompt for password.

--password, -W
    Force password prompt.

reindexdb is a utility for rebuilding indexes in a PostgreSQL database. It's a wrapper around the SQL command REINDEX. This command is crucial for maintaining the health and performance of your database. Over time, indexes can become corrupted, inefficient due to data churn, or bloat, leading to slower query performance. reindexdb addresses these issues by rebuilding one or more indexes. It can operate on an entire database, a specific schema, a table, or even a single index.

Reindexing helps to:
Fix corrupted indexes: Although rare, indexes can sometimes become corrupted, leading to incorrect query results or errors.
Improve query performance: After significant data changes (insertions, updates, deletions), index structures can become unbalanced or fragmented. Rebuilding them restores their optimal B-tree structure.
Reclaim disk space: In some cases, especially with B-tree indexes, reindexing can reclaim space used by "dead" tuples, although VACUUM is primarily for table bloat.

When run without the --concurrently option, reindexing an object requires an exclusive lock, blocking write operations (and sometimes reads) on that object. Using --concurrently (available since PostgreSQL 9.4) allows reindexing with minimal impact on concurrent operations, making it suitable for production environments.

reindexdb requires appropriate PostgreSQL privileges. For REINDEX DATABASE or REINDEX SYSTEM, you typically need SUPERUSER privileges. For individual tables or indexes, ownership or CREATE privilege on the schema might suffice.

Without the --concurrently option, reindexing an object (table or index) will acquire an exclusive lock, blocking all concurrent writes (and sometimes reads) on that object until the operation completes. --concurrently greatly reduces the locking impact.

Reindexing an entire database (-a or -d without -t/-i/-s) or using REINDEX SYSTEM (-s pg_catalog) will block all other connections to that database for the duration of the reindex.

While reindexdb can help reclaim some space, particularly in B-tree indexes, its primary purpose is structural optimization and corruption repair, not general table bloat reclamation (which is handled by VACUUM FULL or CLUSTER).

The --full option ensures a complete rebuild of the index from scratch by scanning the table, which is the default behavior for REINDEX DATABASE and REINDEX SYSTEM. For individual indexes, --concurrently is often a safer choice in production.

When reindexing tables or individual indexes, --concurrently is generally preferred in production environments because it allows other database operations to continue while the index is being rebuilt. It takes longer but avoids blocking. The --full option performs a full rebuild, which is the default for entire databases or system catalogs, but for individual objects, --concurrently is usually the better choice to avoid exclusive locks.

The benefits of reindexing are most pronounced for B-tree indexes, which are the most common type. Other index types, like GIN or GiST, have different internal structures and might benefit from reindexing in different ways, or less frequently, depending on their specific usage patterns and PostgreSQL version. Always consult PostgreSQL documentation for specific index types.

The reindexdb utility has been a part of the PostgreSQL client application suite for many years, acting as a convenient command-line wrapper for the REINDEX SQL command. Its functionality has evolved alongside PostgreSQL itself, notably with the introduction of concurrent index rebuilds. The --concurrently option was added to reindexdb in PostgreSQL 9.4, mirroring the REINDEX CONCURRENTLY feature, which significantly improved its usability in high-availability production environments by allowing indexes to be rebuilt with minimal impact on database operations. Subsequent versions have seen refinements and additional options to enhance control and flexibility.

psql(1), vacuumdb(1), clusterdb(1), pg_dump(1), REINDEX (SQL command)