Rebuild PostgreSQL Indexes Safely with reindexdb

A bloated index that used to answer queries in milliseconds is a common reason people reach for reindexdb. It wraps the SQL REINDEX command so you can rebuild PostgreSQL indexes from a plain Linux shell, narrowing the job to one table, schema or index instead of touching the whole database's indexes at once.

The examples use the installed PostgreSQL 16.15 command, packaged as PostgreSQL 16.15 on Ubuntu 24.04. Allow about fifteen minutes for the checks and planning below; the rebuild itself may take much longer, depending on the size and activity of the database.

You need a running PostgreSQL server, a database role allowed to perform the selected REINDEX operation, and enough space and connection capacity for the work. These commands change database indexes, not table rows, but they can consume substantial I/O and may block writes unless you request a concurrent rebuild.

1. Confirm the installed command

Check the binary and version before trusting any option from memory. This is a read-only check and needs no elevated privileges:

$ command -v reindexdb
/usr/bin/reindexdb
$ reindexdb --version
reindexdb (PostgreSQL) 16.15 (Ubuntu 16.15-0ubuntu0.24.04.1)

The manpage describes reindexdb as a wrapper around SQL REINDEX: the server, not the shell command, does the actual rebuilding. A version mismatch between client and server is worth recording before a maintenance run, especially if you are relying on a newer option or a distribution-specific package.

2. Check the connection without rebuilding anything

reindexdb has no dry-run mode in the installed interface, so use a separate client check, such as psql, to confirm the target and role before making a change:

$ psql --dbname='postgresql://DB_USER@DB_HOST:5432/APP_DB' \
    --command='SELECT current_database(), current_user;'
 current_database | current_user
------------------+--------------
 app_db           | db_user
(1 row)

Replace the connection string with a safe value for your environment. Avoid putting a password on the command line, where it can leak through shell history or process inspection; use the normal libpq mechanisms instead, such as a protected .pgpass file or an interactive prompt. If you pass a connection string as the dbname argument, its parameters can override conflicting command-line connection options.

If you omit the database name from reindexdb, it first falls back to PGDATABASE, then to the connection user name. Make the target explicit in a maintenance command, so an inherited environment variable does not send the rebuild somewhere unexpected. The complete examples below keep the target visible throughout.

3. Choose the smallest useful rebuild

Start with the narrowest scope that fixes the problem. These are mutually exclusive examples: run only the one that matches your maintenance plan.

# All indexes in one database, excluding system catalog indexes
$ reindexdb --dbname='APP_DB'

# All indexes belonging to one table
$ reindexdb --dbname='APP_DB' --table='public.orders'

# All indexes in one schema
$ reindexdb --dbname='APP_DB' --schema='public'

# One named index
$ reindexdb --dbname='APP_DB' --index='public.orders_customer_id_idx'

# System catalog indexes in the current database
$ reindexdb --dbname='APP_DB' --system

The table, schema and index forms can each be repeated with multiple --table, --schema or --index options. --system targets system catalogues, not user-table indexes: treat it as a separate repair decision, not a broader version of an ordinary application-table rebuild.

4. Run a normal rebuild with visible progress

For a planned table rebuild, add --verbose so the terminal shows which indexes are being processed. Add --echo when you need to review the SQL the wrapper generates:

$ reindexdb --dbname='APP_DB' --table='public.orders' \
    --verbose --echo
reindexdb: reindexing table "public.orders"
reindexdb: index "public.orders_pkey"
reindexdb: index "public.orders_customer_id_idx"

Exact progress lines depend on the indexes present, so treat the names above as representative rather than a fixed transcript. A successful run returns status zero, so check it immediately:

$ printf 'reindexdb exit status: %s\n' "$?"
reindexdb exit status: 0

For quiet batch behaviour, use --quiet and keep relying on the exit status in whatever scheduler or wrapper calls the command. Do not combine quiet mode with a monitoring design that expects progress text as its only evidence of completion.

5. Use concurrent rebuilding when its limits fit

--concurrently requests PostgreSQL's concurrent index rebuild. It avoids the locks that would otherwise block concurrent inserts, updates and deletes on the table, but it has extra caveats and can take longer or use more resources: it is not a universal replacement for the normal form.

$ reindexdb --dbname='APP_DB' --table='public.orders' \
    --concurrently --verbose
$ printf 'reindexdb exit status: %s\n' "$?"
reindexdb exit status: 0

Check the SQL REINDEX restrictions for your PostgreSQL release and workload before relying on this. Concurrent execution is not available with --system, and --jobs cannot be combined with --system or --index. A concurrent rebuild can still create load, need extra storage, and fail after leaving an invalid index that needs investigation.

6. Parallelise a broad run carefully

--jobs=N runs that many reindex commands at once, opening N database connections. Compare that value against the server's connection budget and whatever the application is already using:

$ reindexdb --dbname='APP_DB' --jobs=2 --verbose
$ printf 'reindexdb exit status: %s\n' "$?"
reindexdb exit status: 0

Start small, such as 2, and watch database latency, I/O and connection usage. More workers can cut elapsed time while making the server less responsive. Do not add --jobs to an index-only or system-catalog command; the installed utility rejects those combinations.

7. Recover from a failed run

A non-zero status means the operation did not complete cleanly. Save the error text, note the exact scope, and check the server logs and locks before retrying. Do not immediately broaden the command to the whole database.

$ reindexdb --dbname='APP_DB' --index='public.orders_customer_id_idx' \
    --verbose 2>reindexdb.err
$ status=$?
$ printf 'reindexdb exit status: %s\n' "$status"
$ sed -n '1,80p' reindexdb.err
$ test "$status" -eq 0

Keep reindexdb.err for the incident record until the failure is understood. If a concurrent index build left an invalid index, follow the PostgreSQL REINDEX guidance for repairing that specific index. If the problem involves system catalogues or suspected corruption, stop: use a documented recovery procedure with a database administrator rather than improvising a server restart or running commands as root.

There is no file rollback for this operation. Recovery means resolving the database or connection problem, then rerunning the smallest affected scope and checking the result. Remove the diagnostic file only once it is no longer needed:

$ rm -- reindexdb.err

That removal is irreversible, so do not fold it into an unattended repair script.