> On Fri, Jun 6, 2008 at 9:36 AM, Roberts, Jon <Jon.Roberts@xxxxxxxxxxx> > 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
