From: | Martijn van Oosterhout <kleptog(at)svana(dot)org> |
---|---|
To: | Scott Marlowe <scott(dot)marlowe(at)ihs(dot)com> |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Subject: bool / vacuum full bug followup part 2 |
Date: | 2002-05-06 23:54:43 |
Message-ID: | 20020507095443.A14434@svana.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Mon, May 06, 2002 at 10:23:53AM -0600, Scott Marlowe wrote:
> A growing index that vacuum doesn't shrink is a serious issue for people
> who expect to reclaim lost space with vacuum. We at least need to let
> people know of this behavior in the admin docs, as right now they (the
> docs) seem to imply that vacuum frees up all unused space. For indexes,
> this isn't true, and people who are getting started don't need this kind
> of gotcha waiting to kill a production database 2 or 6 months into use.
PostgreSQL has never shrunk indexes, not now not never. The only option is
to reindex or recreate them. We use a script here to automatically rebuild
all the indexes each month.
> Is it maybe at least possible to make reindex either transaction safe or
> have an option that pretty much drops and recreates the index in a
> transactionally safe mode or something?
It is safe to drop and create the index within a transaction.
Feed output of this to psql. Totally transaction safe :)
======================
#!/usr/bin/perl -w
my $DB = "database";
open( FH, "pg_dump -s $DB |grep INDEX |" ) || die "Can't pg_dump ($!)\n";
while(<FH>)
{
chomp;
/"(\w+)"/ or die "Couldn't extract index name from [$_]\n";
my $index = $1;
s/$index/${index}_reindex/;
s/ "\w+"(,| \))/$1/g;
print "begin;\n";
print "$_\n";
print "drop index $index;\n";
print "alter table ${index}_reindex rename to $index;\n";
print "commit;\n";
}
======================
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Canada, Mexico, and Australia form the Axis of Nations That
> Are Actually Quite Nice But Secretly Have Nasty Thoughts About America
From | Date | Subject | |
---|---|---|---|
Next Message | Gregory Seidman | 2002-05-07 01:05:55 | Re: help with *mysql* |
Previous Message | Marc G. Fournier | 2002-05-06 23:35:15 | Re: Problems with list? |