Maintaining indexes

Just as tables can become bloated, so can indexes. However, reusing space in indexes is much less effective. In the Identifying and fixing bloated tables and indexes recipe, you saw that non-HOT updates can cause bloated indexes. Non-primary key indexes are also prone to some bloat from normal INSERT commands, as is common in most relational databases. Indexes can become a problem in many database applications that involve a high proportion of INSERT and DELETE commands.

autovacuum does not detect bloated indexes, nor does it do anything to rebuild indexes. Therefore, we need to look at other ways to maintain indexes.

Getting ready

PostgreSQL supports commands that will rebuild indexes for you. The client utility, reindexdb, allows you to execute the REINDEX command conveniently from the operating system:

$ reindexdb

This executes the SQL REINDEX command on every table in the default...