Re: db grows and grows

On Thu, 20 Jun 2002, Martijn van Oosterhout wrote:

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

You can do 'select indexdef from pg_indexes where tablename='tablename' to
get the create index command without using pg_dump.

I've written a small PHP script that uses that and transactions to do
dynamic index recreation on my box at work.

--
"Force has no place where there is need of skill.", "Haste in every
business brings failures.", "This is the bitterest pain among men, to have
much knowledge but no power." -- Herodotus