Re: How to determine whether to VACUUM or CLUSTER - Mailing list pgsql-performance

From Tom Lane
Subject Re: How to determine whether to VACUUM or CLUSTER
Date
Msg-id 15401.1118980053@sss.pgh.pa.us
Whole thread Raw
In response to How to determine whether to VACUUM or CLUSTER  (ken shaw <kshaw987@yahoo.com>)
List pgsql-performance
ken shaw <kshaw987@yahoo.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

pgsql-performance by date:

Previous
From: Todd Landfried
Date:
Subject: Re: Needed: Simplified guide to optimal memory
Next
From: Tom Lane
Date:
Subject: Re: How does the transaction buffer work?