Bjoern Metzdorf wrote:
> > > REINDEX is really intended for disaster recovery, not routine space
> > > management.
> >
> > That's nice. Except the fact Postgres is so bad at maintaining its own
> > indexes makes us have to *use* it as routine space management. Having a
> > 200MB index that's still getting bigger, and really should only be 200k,
> > tells me that there's no better use for it. Sure, I could wait until
> > the index takes up my entire disk, and then consider it disaster
> > recovery, but that's just silly.
>
> I think was Tom meant is, that REINDEX should not have to be used for
> routine space management. It *should* be only there for disaster recovery.
> Unfortunately this is not the case in the latest releases, so we all use it
> now temporarily for space management, although this is surely going to be
> fixed in one of the next releases.
Yes, but the problem is that we don't have a plan on how to fix the
index growth problem right now, so if people want to prevent index
growth, reindex is the only solution.
The TODO item says:
* Certain indexes will not shrink, e.g. indexes on ever-increasing
columns and indexes with many duplicate keys
but in fact that wording is misleading. >80% of index are on
ever-increasing columns, so it isn't really 'certain index' but more
accurately 'most indexes'.
I am planning to add the reindex script to /contrib, document its need
in the maintenance docs, and add an FAQ item. If it gets fixed in 7.3,
great. If not, we will have communicated to users and given them the
tools then need.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026