Re: index files still growing

Alvaro Herrera <alvherre(at)atentus(dot)com> writes:
> But at least in 7.1 REINDEX has to be run under stand-alone Postgres, or
> so say the docs:

The docs are not very well phrased (I've just made a note to fix that).
If you are trying to recover from a corrupted system index then indeed
you have to do all the pushups mentioned, because you don't want the
backend trying to use the broken index along the way:

> REINDEX is used to recover corrupted system indexes. In order to run
> REINDEX command, postmaster must be shut down and stand-alone Postgres
> should be started instead with options -O and -P (an option to ignore
> system indexes).

If you are trying to optimize an index on a user table, you can just do
REINDEX INDEX or REINDEX TABLE without the funny stuff.

> Whereas the DROP INDEX/CREATE INDEX method works on-the-fly. You may end
> up running a seqscan somewhere, but that's not so big a problem, IMHO.

True, that way doesn't lock out concurrent readers of the table while
the index is rebuilt.

regards, tom lane