Re: delete/vacuum not freeing disk space

On Sun, 7 Oct 2001, Marc Spitzer wrote:

> In article <20011006165604(dot)72133(dot)qmail(at)web20005(dot)mail(dot)yahoo(dot)com>,
> 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"