Re: index keeps on growing

From: Brian McCane <bmccane(at)mccons(dot)net>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: <pgsql-admin(at)postgresql(dot)org>
Subject: Re: index keeps on growing
Date: 2002-06-19 23:28:07
Message-ID: 20020619165216.C2042-100000@fw.mccons.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin


Since BEGIN/COMMIT should work, that would give the original poster what
they wanted (they have 7.1.3). Usually on a fairly active database (like
mine) running my 'fixtable.pl' script gives me back around 10-20% of my
disk space (and improves my performance for about a week). The longer it
has been, the greater the return on disk space. However, I do around
300,000 insert and 100,000 updates a day in one of my tables. When I get
to the end of the internet I'll let everyone know ;).

The biggest problem that I have with PostgreSQL nowadays is that I have my
files spread across 6 separate SCSI "drives" attached to my database
server (a couple are 36GB RAID 5+0). When I run this script on my largest
table I need about 20GB of free space in my PGDATA directory for the index
files that get created during the recreate and don't end up where they
used to be. Also, the symlinks get removed by the "DROP INDEX" call, but
the actual file is still out on the drive where I keep them and I have to
remove them manually, which is very nerve wracking. I have now started
doing that table 1 index at a time, then I use my 'moveindex.pl' script to
put the index back where it came from :).

Incidentally, how long has the "pg_indexes" view existed? I didn't find
it when I originally started writing this script back on 7.0.x. I am
rewriting my script to use the view instead of 'pg_dump' which seems much
cleaner to me.

Finally, is there something like 'pg_indexes' for triggers? It would be
nice to not have to call pg_dump at all, but I don't want to try and
figure out the query needed to generate the 'CREATE TRIGGER' rules.

necessity is a mother,

- brian

On Wed, 19 Jun 2002, Tom Lane wrote:

>
> Brian McCane <bmccane(at)mccons(dot)net> writes:
> > I assume that if I do it in a BEGIN..COMMIT block I won't lose
> > anything, but I am not sure if a "DROP INDEX" can be rolled back.
>
> DROP INDEX can be rolled back in the same releases that allow DROP
> TABLE to be rolled back. I think we allowed that beginning in
> 7.0, but check the release notes.
>
> So basically you'd want
>
> BEGIN;
> DROP INDEX foo;
> either CREATE INDEX ...
> or ALTER TABLE ADD PRIMARY KEY ...;
> COMMIT;
>
> Note this will imply peak disk usage equal to size of old index
> plus size of new, since the old file can't physically be removed
> till commit.
>
> regards, tom lane
>

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"

In response to

Browse pgsql-admin by date

  From Date Subject
Next Message Brian McCane 2002-06-19 23:32:04 Re: time stamp
Previous Message nimeshb 2002-06-19 22:28:38 time stamp