How long does it take do a database dump (with gzip -1 via | and > ), drop this database and create the database and restore it from the backup. That is my solution, but I dont know how long it will take to restore your database and i dont have so large databases. > Secondly this sounds like a perfect time for you to consider upgrading to 8.1 I would not do this without a test (not only a dump/restore test, I would test it with your application too) Greetings, -Franz -----Ursprüngliche Nachricht----- Von: Sven Willenberger [mailto:sven@xxxxxxx] Gesendet: Freitag, 7. Juli 2006 19:26 An: Joshua D. Drake Cc: pgsql-general@xxxxxxxxxxxxxx Betreff: Re: [GENERAL] VACUUM FULL versus CLUSTER ON On Fri, 2006-07-07 at 09:55 -0700, Joshua D. Drake wrote: > On Friday 07 July 2006 08:19, Sven Willenberger wrote: > > Postgresql 8.0.4 on FreeBSD 5.4 > > > > I have a table consisting of some 300million rows that, every couple of > > months, has 100 million rows deleted from it (an immediately vacuumed > > afterward). Even though it gets routinely vacuumed (the only > > deletions/updates are just the quarterly ones), the freespace map was > > not increased in size to keep up with the growing size of the other > > tables in the database which do experience many updates,etc. > > Based on the size of the table, you may want to: > > Backup the table > Drop the table > Restore the table > > Is is possible that this will be faster in this instance. > > Secondly this sounds like a perfect time for you to consider upgrading to 8.1 > and making use of table partitioning. That way you can just truncate the child > table containing the old data. > > Sincerely, > > Joshua D. Drake Doing a quick check reveals that the relation in question currently consumes 186GB of space (which I highly suspect is largely bloat). The delete was just run this past weekend as was the recreation of the indexes. I have 50GB of disk space left; If I vacuum full, it does not need to create a temporary copy of the relation and indexes like cluster does, does it? At this point, I think CLUSTER ON is out of the question due to the need to create the temporary table and indexes (I will run out of space during the operation). I do plan on migrating the whole mess to a new server which will run 8.1 (I had looked at inheritance for partitioning, I am glad to see that 8.1 took the concept and ran with it further :) ) This new server will use an external SAS array so I should simply be able to add another array as the need arises and partition to it via tablespace. Thanks to all who offered suggestions; it would appear that at this stage my only option to buy some time is try a vacuum full. My final question: can I leave the indexes in place when I vacuum full? I assume this will only operate on the table itself? Sven ---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your message can get through to the mailing list cleanly
