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.
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...