The easiest way to get started with PostgreSQL on the Mac

Reindexing your database

In some situations database indexes can be corrupted, and you need to rebuild them with the REINDEX command.

How to reindex a database

Rebuilding indexes is very simple:

  1. Connect to the database
  2. Execute the following query: REINDEX DATABASE db_name;
  3. If you are using multiple databases, repeat the steps for every database.
  4. If shows the reindex warning, you can now hide it by clicking “More Info” and then on “Hide this Warning”

Why should I reindex my databases?

In some situations indexes can become corrupted. This can happen because of bugs in PostgreSQL or because of changes in macOS.

Bugs in PostgreSQL that require a REINDEX are typically mentioned in the PostgreSQL release notes.

Changes in macOS that require a REINDEX are unfortunately not documented anywhere. The only problematic change we are aware of currently is that the default text sort order (collation) has changed in macOS 11, which means indexes created or updated on earlier versions of macOS are no longer valid and must be rebuilt.

How do I know if my database is affected?

It is very hard to tell if indexes have actually been corrupted or not. tries to detect some scenarios where an index could have been corrupted, and shows a warning suggesting to reindex your database.

If you are unsure, we recommend to perform the reindex operation to be safe.

How long does reindexing take?

For small databases it will take just a few seconds, but if you have a lot of data it could take a few minutes.

You can use the command REINDEX (VERBOSE) DATABASE db_name; to see status messages if you have a large database.

Please note that to perform the reindex any concurrent writing transactions need to come to an end and new transactions and sessions may need to wait for the reindex to finish. If any client keeps a writing transaction open, the reindex operation will block and wait for that without any warning. You can cancel and restart the operation if needed.

What happens if I don’t reindex?


Backgrund Info on Collation Changes

If not explicitly requested otherwise (ICU collations), PostgreSQL uses the collations (language dependent rules for sorting text) provided by the operation system. PostgresApp sets the default locale (and thus collation) to ‘en_US.UTF-8’ when initialising a new cluster since PostgresQL 9.4.1 (End of 2014). However, UTF-8 based collations are not actually implemented in macOS (like in most BSD Systems). Thus, the effective sort order was rather following byte order, see Issue #216.

With the update to macOS 11, Apple started to use the ISO8859-1 collations for about half of the available locales, including the default locale of, ‘en_US.UTF-8’. As Database Indexes store the order of elements at the time these are inserted, corruption can happen if the sorting rules change later.

Is my database affected by macOS collation changes? records the version of macOS where initdb was called, and also all versions of macOS that the server was started on. Since this information is not available for old data directories, guesses the macOS version used to call initdb for existing data directories based on the install history of macOS updates. If detects that the data directory was used both pre macOS 11 and post macOS 11 or the macOS version used for initdb of a data directory is unknown, it shows the reindex warning.

So the warning is a good indicator, but may not be absolutely accurate. If you prefer to do a manual assessment, here are some guidelines:

You are not affected if:

You are likely affected if:

There is no relation with the version of PostgresApp or PostgreSQL or with the architecture (Apple Silcon / Intel) in use.

Advanced Stuff

Further reading