Re: Impact of vacuum full...

Csaba Nagy wrote:
> 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...
>
That is an excellent idea, however, what are the effects of CLUSTER on
empty tables? Considering that most of our queue tables sit empty until
their used, our main concern is keep the disk space that they use
available and our 'cleaning' activities will be done whenever the tables
empty out (unless they are scheduled for use within, say, an hour) I
have to wonder at whether or not CLUSTER would do anything to an empty
table with no actual data to cluster.

--
erik jones <erik(at)myemma(dot)com>
software development
emma(r)