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.

I am concerned about index bloat. I have an index on a table that is
updated with new data frequently and according to this:
http://www.postgresql.org/docs/8.3/interactive/routine-reindex.html

"Any situation in which the range of index keys changed over time"

I will eventually get index bloat.

Based on this, I have the fillfactor set lower than the default 90 but
this will fill up and it will run slower over time. I want to automate
the reindex process but only reindex when needed. I have a pretty large
database so I can't reindex everything regardless if it needs it or not.

Jon