Re: index keeps on growing

I have a perl script which rebuilds index files "hot".  It looks up the
pertinent information about the index you specify on a table or for ALL
indexes for a table.  It then builds a new index exactly like the one you
specify named "<index>_new".  After the index has been created, it removes
the original index and renames <index>_new to <index>.  Just so noone
thinks I am reckless, the perl script actually just creates a SQL
script of what work is to be done, and then the user has to feed the
commands to 'psql'.  I always run the program once and look at the
script that it wants to use.  Then, if I like what I see, I run the
program again and pipe the output to 'psql'.  I don't care how good
the program is, I prefer a manual inspection before I allow it to mess
with my database.

The perl script will allow you to rebuild your indexes (indices?) without
having to kick out all of your users.  It reclaims all of the "holes" in
the index files.  It does NOT preserve the "primary key" setting for an
index, because as far as I can tell this doesn't actually make any
difference, primary keys are just an UNIQUE index.  I have used it since
7.0.3, and it has worked fine for me, but I guarantee nothing about your
database (lawyers told me I have to say that ;).  The only caveat I can
think of is that it places a read lock on the table, so while you are
rebuilding the index noone can write to the table (I am not sure on this
might wanna ask Tom).

I will put this and a couple of other scripts that I have written for
maintenance on PostgreSQL on my anonymous FTP server.  They will be at:

ftp://china.maxbaud.net/pub/PostgreSQL/

BTW, the script also fixes triggers.  I had a problem once upon a time
where I did some tinkering with tables and their names, and when I was
done my triggers no longer pointed at the correct table.  The name in the
trigger definition was correct, but the oid (or whatever the field in
pg_trigger is), pointed to the old database oid.  So, the triggers all
failed to run.  I don't know if this is still possible, but it will still
fix it.

- brian


On Wed, 19 Jun 2002, Jean-Christophe ARNU wrote:

>
> Hello all,
>      I've a hard working pgsql db running on 7/7x365 server. Each night
> (when
>  activity is less intensive) I run vacuum and vacuum analyze on tables where
>  principal activity is performed.
>      Tables are cleaned (files sizes are decreasing), but indices files
> keeps on
>  growing in size....
>
>      I run PgSQL 7.1.3, I cannot upgrade (immediately) to 7.2.x... So what
> can I
>  do to keep these indices' size at a constant and reasonnable size?
>
>      Thanks a lot
>
>
> --
> Jean-Christophe ARNU
> s/w developer
> Paratronic France
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>

Wm. Brian McCane                    | Life is full of doors that won't open
Search http://recall.maxbaud.net/   | when you knock, equally spaced amid those
Usenet http://freenews.maxbaud.net/ | that open when you don't want them to.
Auction http://www.sellit-here.com/ | - Roger Zelazny "Blood of Amber"