Re: How frequently to defrag(cluster)

On 07/20/2011 02:04 PM, Steve Crawford wrote:
> On 07/20/2011 12:58 PM, A J wrote:
>> I understand that 'cluster' performs the role of defrag ...
> As with everything the answer is "it depends". For a "typical"
> workload where the rows updated by a single query are one or a few
> rowsl, the automatic vacuum process should handle everything for you
> without intervention (some tuning of the vacuum settings may be in
> order but it runs by itself).
>
>
> Also, there are some workloads that have queries that grab chunks of
> data, say all records for a given date, where keeping the associated
> data physically close can improve performance.
I should have explained better. Cluster physically orders the data in
the table in the same order as the index selected for the cluster. It is
this physical reordering of data that can improve performance by keeping
the data you are likely to retrieve in the same physical area. (Yes, the
data can be scattered by the OS but clustering still helps). Also note
that as you perform updates, the data will become more and more
unordered. If your workload benefits substantially from clustering, it
will have to be done periodically based on your workload and observed
performance degradation.

Another place where cluster is useful is following deletion of large
chunks of data as when archiving. For many workloads, however, table
partitioning using parent/child tables organized so that archiving can
be as simple as backing up then dropping a child table can be preferable.

Cheers,
Steve