Re: when to reindex?

On Fri, Jun 6, 2008 at 9:36 AM, Roberts, Jon <Jon(dot)Roberts(at)asurion(dot)com> wrote:
> In Oracle, there is a method to determine when it is advisable to
> rebuild indexes. Are there any guidelines for this in PostgreSQL?
>
> I found this but it doesn't indicate at which point an index should be
> rebuilt other than corruption.
>
> http://www.postgresql.org/docs/8.3/interactive/routine-reindex.html

PostgreSQL isn't quite as finicky about indexes as oracle can be. If
you've ever rebuilt a table and forgot to rebuild the indexes in
oracle you know what I'm talking about.

PostgreSQL generally takes care of indexes pretty well. There are two
reasons to reindex in pgsql. The first one is a corrupted index.
Note that if you're running on quality hardware, and a properly
configured db (i.e. fsync isn't off, etc...) then you shouldn't get
corrupted indexes. If you get them quite often, then you've got worse
problems than just figuring out when to reindex. The second common
situation that requires a reindex is when you suffer from index bloat.
This can be caused by certain out of the ordinary update patterns and
by vacuum full.