Re: index keeps on growing
От | Brian McCane |
---|---|
Тема | Re: index keeps on growing |
Дата | |
Msg-id | 20020619165216.C2042-100000@fw.mccons.net обсуждение исходный текст |
Ответ на | Re: index keeps on growing (Tom Lane <tgl@sss.pgh.pa.us>) |
Список | 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@mccons.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"
В списке pgsql-admin по дате отправления: