Re: Contrib reindex script:

Shaun Thomas wrote:
> On 7 May 2002, Jason Earl wrote:
>
> > Hey thanks, I was just wondering if such a thing were available.
>
> Here also is an updated version of the script.  I've cleaned up some of
> the code, heavily commented it, and fixed a bug or two.  Remember, this
> is basically just vacuumdb, so it'll take all the same parameters
> (except the obvious ones like -z and -f) and you can put it in your
> postgres bin directory.  I have a line in cron that runs it every two
> hours with the -a option with the same user that owns the install.
>
> Works great!

OK, I have added your reindex script to CVS /contrib.

> > How has your migration to 7.2?  Are you still have problems with the
> > database growing out of control?
>
> Actually, now that I have this script to basically be a vacuum script
> for indexes to go along with vacuumdb, I've arrested the database
> growth.  The database I was groaning about before is standing firm
> around 87MB instead of the 300MB it would normally be by now.
>
> So far, 7.2 is fine.  Database load is a non issue, growth is gone
> thanks to the reindex script, and I couldn't be happier.  Now the real
> question is, why can't Postgres have a monitor that does these two
> things (vacuum, reindex) automatically throughout the day?  Something
> that just trawls the tables doing a continuous partial vacuum, and
> triggers on deletes and updates to keep the indexes consistant.

Yep, it would be nice.  I now realize there isn't even a way to see
index wastage.  I think Tatsuo was working on such a script for contrib.

I have also added information to the SGML manual under maintenance:

  <para>
   <productname>PostgreSQL</productname> is unable to reuse btree index
   pages in certain cases. The problem is that if indexed rows are
   deleted, those index pages can only be reused by rows with similar
   values. For example, if indexed rows are deleted and newly
   inserted/updated rows have much higher values, the new rows can't use
   the index space made available by the deleted rows. Instead, such
   new rows must be placed on new index pages. In such cases, disk
   space used by the index will grow indefinately, even if
   <command>VACUUM</> is run frequently.
  </para>
  <para>
   As a solution, you can use the <command>REINDEX</> command
   periodically to discard pages used by deleted rows. There is also
   <filename>contrib/reindex</> which can reindex an entire database.
  </para>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026