PostgreSQL High Performance Cookbook

Routine reindexing

As like tables, an index also needs a special maintenance activity called reindex or rebuild. The job of reindex is to build a fresh index by replacing the existing index pages.

Getting ready

When we do the DELETE/UPDATE operation on a table, PostgreSQL will remove or update the corresponding entries from the table's indexes. Once it removes the entries from the index pages, there might be chances of increasing the leaf page fragmentation in btree indexes, which leads to more I/O while performing the index scan operations. This is because in the btree index all the index and row entries will be at the leaf node, where root and branch nodes will be helping the index scan to reach its index entries. In general, if a leaf page fragmentation ...