On Sun, 7 Oct 2001, Marc Spitzer wrote: > In article <[EMAIL PROTECTED]>, > Joshua Franklin wrote: > > I've got a large database that's getting too big. I > > checked with the users and deleted approximately 1/6th > > of the records (we have archived backups) and then ran > > a VACUUM, which took a really long time to complete > > (several days). But, the disk space usage doesn't seem > > to have changed. Do I need to VACUUM twice or > > something? Should it be faster the second time? > > > > Thanks. > > > > __________________________________________________ > > Do You Yahoo!? > > NEW from Yahoo! GeoCities - quick and easy web site hosting, just $8.95/month. > > http://geocities.yahoo.com/ps/info1 > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 5: Have you checked our extensive FAQ? > > > > http://www.postgresql.org/users-lounge/docs/faq.html > > vacuum does not reclame space in indexes, if you use a command called > reindex to recreate the indexes it may reclame space. It needs space > to write the new index's though. Reindex should be run in sigle user > mode only, per the docs. > > marc > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org >
Being the perpetually lazy person that I am, I have written a PERL script that takes care of indexes on a live system. You tell the script the database and table to reindex. It then finds a list of indexes for the table and creates exactly the same index, with a slightly modified name. After the copy is made the old index is DROP'd, and the new one is ALTER'd to the old name. Unfortunately, this means that you must have enough room for the new index plus the old at the same time, but I am sure that most people have lots of spare room. I seem to remember that the last time I ran this, it didn't even stop my websites from using the tables at the same time. I assume this is because of the versioning system used in PostgreSQL. - brian 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" ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
