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