Re: index files still growing

On Wed, 24 Oct 2001, Tom Lane wrote:

> Doug McNaught <doug(at)wireboard(dot)com> writes:
> >> My solution is to DROP & CREATE my indexes files once
> >> a week.
>
> > Your solution is currently the only one available. Not pretty, but it
> > works.
>
> REINDEX is a marginally prettier answer; at least you don't have to
> remember the index parameters. Shrinking indexes on-the-fly is still
> on the TODO list.

But at least in 7.1 REINDEX has to be run under stand-alone Postgres, or
so say the docs:

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).

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.

--
Alvaro Herrera (<alvherre[(at)]atentus(dot)com>)
The eagle never lost so much time as
when he submitted to learn from the crow. (Nobody)