Re: How to determine whether to VACUUM or CLUSTER

ken shaw <kshaw987(at)yahoo(dot)com> writes:
> It looks to me as if the only way to determine whether to issue a
> VACUUM (on a non-clustered table) or a CLUSTER (on a clustered table)
> is to query the table "pg_index", much like view "pg_indexes" does,
> for the column "indisclustered". Is this right?

indisclustered is certainly the ground truth here, and [ ... digs around
in the source code ... ] it doesn't look like there are any views that
present the information in a different fashion. So yup, that's what
you gotta do.

> Also, how expensive is CLUSTER compared to VACUUM?

Well, it's definitely expensive compared to plain VACUUM, but compared
to VACUUM FULL the case is not clear-cut. I would say that if you had
a seriously bloated table (where VACUUM FULL would have to move all or
most of the live tuples in order to compact the table completely) then
CLUSTER will be faster --- not to mention any possible future benefits
from having the table more or less in order with respect to the index.

As near as I can tell, VACUUM FULL was designed to work nicely when you
had maybe 10%-25% free space in the table and you want it all compacted
out. In a scenario where it has to move all the tuples it is certainly
not faster than CLUSTER; plus the end result is much worse as far as the
state of the indexes goes, because VACUUM FULL does *nothing* for
compacting indexes.

regards, tom lane