Josh Berkus <josh@agliodbs.com> writes:
>> 1) When is it necessary to run REINDEX or drop/create
>> an index? All I could really find in the docs is:
> If you need to VACUUM FULL, you need to REINDEX as well. For example, if you
> drop millions of rows from a table.
That's probably a pretty good rule of thumb. It's worth noting that
VACUUM FULL tends to actively bloat indexes, not reduce them in size,
because it has to create new index entries for the rows it moves before
it can delete the old ones. So if a VACUUM FULL moves many rows you
are likely to see the indexes get bigger not smaller.
> Better to up your max_fsm_pages and do regular VACUUMs regularly and
> frequently so that you don't have to REINDEX at all.
Yes, definitely. Also consider using CLUSTER rather than VACUUM FULL
when you need to clean up after massive deletions from a table. It's
not any less intrusive in terms of locking, but it's often faster and it
avoids the index bloat problem (since it effectively does a REINDEX).
regards, tom lane