Routine Reindexing

8.3. Routine Reindexing

PostgreSQL is unable to reuse B-tree index pages in certain cases. The problem is that if indexed rows are deleted, those index pages can only be reused by rows with similar values. For example, if indexed rows are deleted and newly inserted/updated rows have much higher values, the new rows can't use the index space made available by the deleted rows. Instead, such new rows must be placed on new index pages. In such cases, disk space used by the index will grow indefinitely, even if VACUUM is run frequently.

As a solution, you can use the REINDEX command periodically to discard pages used by deleted rows. There is also contrib/reindexdb which can reindex an entire database.

Home
Online Resources
General
Beginner Tutorials
MySQL
PostgreSQL
Oracle
mSQL
Microsoft SQL
Contact Us
Random quote of the moment:
Don't you feel more like you do now than you did when you came in?
 
http://www.sql.org/
 
Proudly designed and hosted by OmegaSphere, providers of all your Web Hosting, SSL Certificate, and Domain Name needs!