Re: Impact of vacuum full...

On Fri, 21 Jul 2006 11:40:32 -0500
Erik Jones <erik(at)myemma(dot)com> wrote:

> No!!! The table is filled and entries are deleted one at a time, or in
> groups, but definitely not all at once. So, then what is the
> difference between scheduling regular vacuum on specific tables v.
> scheduling vacuum full on specific tables? Basically, what I want to do
> is to ensure that when I clean out a table row or rows at a time, the
> space is immediately freed up.

Why would you want to do that? PostgreSQL is not designed to operate in
that fashon, and it's ineffecient. You're forcing PostgreSQL to constantly
increase and decrease the amount of disk space allocated for the table,
which isn't the best way to do things.

Much better is to schedule frequent VACUUMs so that the table size reaches
an equilibrium. This way there is always a little free space in the table
so that rows can be added/updated without increasing the table size. This
is how PostgreSQL is designed to run.

If you don't have enough disk space to do this, then you didn't purchase
large enough drives to hold your DB efficiently. You can do the VACUUM
FULL as you describe, but it's non-optimal.

To answer your original question directly: VACUUM FULL is expensive. It
needs to lock out the table for the duration of its work, and other
transactions will block during the operation. Depending on the table
size, it could be prohibitively time-consuming.

--
Bill Moran
Potential Technologies
http://www.potentialtech.com