From: Andrew Sullivan <ajs@crankycanuck.ca>
To: pgsql-sql@postgresql.org
Subject: Re: vacuum process taking more than 33 hours
Date: Mon, 15 Jan 2007 09:32:47 -0500
Message-ID: <20070115143247.GA13011@phlogiston.dyndns.org> (raw)
In-Reply-To: <2529.5826.qm@web30013.mail.mud.yahoo.com>
References: <2529.5826.qm@web30013.mail.mud.yahoo.com>
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@crankycanuck.ca
The fact that technology doesn't work is no bar to success in the marketplace.
--Philip Greenspun
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ajs@crankycanuck.ca
Subject: Re: vacuum process taking more than 33 hours
In-Reply-To: <20070115143247.GA13011@phlogiston.dyndns.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox