Re: db grows and grows

On Wed, Jun 19, 2002 at 06:37:38PM -0400, terry@greatgulfhomes.com wrote:
> If he needs to REINDEX live without locking out selects, then simply DROP
> INDEX <indexname>; and then do a CREATE INDEX ...  to recreate it.  Drop is
> fast, and the create does not acquire the exclusive lock.
>
> The only down side of delete/recreate is that with REINDEX one did not need
> to know the statement to recreate the index, and the latter does.

A while ago I did post a script that did this, only it did the create first,
then the drop and then renamed the new index to the old one. All within a
transaction so other queries wouldn't be left without an index.

It used pg_dump to get the CREATE INDEX command.

Should be in the archive somewhere...
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> There are 10 kinds of people in the world, those that can do binary
> arithmetic and those that can't.