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 <[EMAIL PROTECTED]>
software development
emma(r)
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
