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 of an index is more than 30% then it is recommended to do a REINDEX operation on it. For non btree indexes, it is not possible to identify the leaf page fragmentation as it maintains its own implementation.
Like a table, an index can also...
