Contents
How does reindex work in a PostgreSQL table?
PostgreSQL supports rebuilding indexes with minimum locking of writes. This method is invoked by specifying the CONCURRENTLY option of REINDEX. When this option is used, PostgreSQL must perform two scans of the table for each index that needs to be rebuilt and wait for termination of all existing transactions that could potentially use the index.
How does the index build work in PostgreSQL?
Normally PostgreSQL locks the table whose index is rebuilt against writes and performs the entire index build with a single scan of the table. Other transactions can still read the table, but if they try to insert, update, or delete rows in the table they will block until the index rebuild is finished.
When to use reindex to rebuild an index?
You have altered a storage parameter (such as fillfactor) for an index, and wish to ensure that the change has taken full effect. An index build with the CONCURRENTLY option failed, leaving an “invalid” index. Such indexes are useless but it can be convenient to use REINDEX to rebuild them.
What happens when an index is corrupted in PostgreSQL?
An index has become corrupted, and no longer contains valid data. Although in theory this should never happen, in practice indexes can become corrupted due to software bugs or hardware failures. REINDEX provides a recovery method.
Do You Drop the CREATE INDEX command in reindex?
Note that REINDEX will not perform a concurrent build. To build the index without interfering with production you should drop the index and reissue the CREATE INDEX CONCURRENTLY command. Recreate the specified index. Recreate all indexes of the specified table. If the table has a secondary “TOAST” table, that is reindexed as well.
Why does my PostgreSQL index have so many dead pages?
An index has become “bloated”, that is it contains many empty or nearly-empty pages. This can occur with B-tree indexes in PostgreSQL under certain uncommon access patterns. REINDEX provides a way to reduce the space consumption of the index by writing a new version of the index without the dead pages. See Section 24.2 for more information.