Re: db grows and grows

From: Scott Marlowe <scott(dot)marlowe(at)ihs(dot)com>
To: Martijn van Oosterhout <kleptog(at)svana(dot)org>
Cc: "'PostgreSQL general list'" <pgsql-general(at)postgresql(dot)org>
Subject: Re: db grows and grows
Date: 2002-06-20 19:20:20
Message-ID: Pine.LNX.4.33.0206201318280.8468-100000@css120.ihs.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

> On Wed, Jun 19, 2002 at 06:37:38PM -0400, terry(at)greatgulfhomes(dot)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

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Bruce Momjian 2002-06-20 19:21:33 Re: database size
Previous Message Andrew Sullivan 2002-06-20 19:07:18 Re: database size