Re: Subject: bool / vacuum full bug followup part 2

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