Re: vacuum process taking more than 33 hours

On Mon, Jan 15, 2007 at 06:23:23AM -0800, Mario Behring wrote:
> Hi all,
>
> I've executed a VACUUM FULL on a database 33GB in size. The process
> was going fine until it reached a index (I guess it's an index) and
> there it stopped for more than 30 hours...........the whole
> weekend......

It may not have been doing anything. VACUUM FULL needs to take an
exclusive lock on each table it is processing. It may have been
waiting for that lock.

> I've canceled it but I desperately need to free some space at the
> server's disk. I was thinking about using the TRUNCATE statement at
> the table I know to be the largest one. I have some questions
> though:

TRUNCATE and VACUUM are different beasts. VACUUM recovers space from
deleted or updated rows. If you have done neither of those things,
then it won't recover any space. TRUNCATE is like DELETE on
steriods: it simply removes all the data from your table.

TRUNCATE will indeed recover disk, although I can't remember whether
it actually returns that disk space to the operating system, or
whether it remains allocated for the table in question by postgres.
If the latter, a VACUUM FULL on the table in question ought to be
enough to get you the space back.

A

--
Andrew Sullivan | ajs(at)crankycanuck(dot)ca
The fact that technology doesn't work is no bar to success in the marketplace.
--Philip Greenspun