Re: Impact of vacuum full...

Erik,

On Fri, 2006-07-21 at 17:13, Erik Jones wrote:
> Hello, I was wondering if someone could enlighten me as to the impact to
> the entire database of running VACUUM FULL against a single table. The
> reason I ask is that at company we work for we have a very large number
> of queue type tables that fill up and empty out on a regular basis, on
> the average every two days per table would be a good estimate.

We also do have here some queue-like tables, though they tend to be
small and our clean-empty rate is minutes not days. I solved this kind
of problem using the CLUSTER command, which completely rebuilds the
table, and as an added benefit it will be physically ordered using the
index you have chosen to cluster on.

I think clustering is faster than vacuum full, and cleans your indexes
too (which vacuum full won't do). One interesting detail is that CLUSTER
is not respecting MVCC, i.e. it will clean all dead tuples regardless if
there are older transactions running which could see them. This might be
a problem for you, but for my queue-like tables was a big help to stay
clean, as CLUSTER is able to shrink them even in the presence
long-running transactions which normally would prevent cleaning dead
tuples back to the oldest running transaction.

For small tables CLUSTER is a nice feature... but beware that it locks
the table exclusively, so if you have a big table you might have a long
down-time during the clustering operation where the table is not
accessible. That said, I use it for fairly big tables too occasionally
when I need to clean up stuff...

Cheers,
Csaba.