Received: from maia.hub.org (maia-2.hub.org [200.46.204.251])
	by mail.postgresql.org (Postfix) with ESMTP id 074F0B5DC0D
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Wed, 20 Jul 2011 18:16:39 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia,
	port 10024) with ESMTP id 68927-06 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Wed, 20 Jul 2011 21:16:33 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from polaris.pinpointresearch.com (cerberus.pinpointresearch.com
	[66.7.238.130])
	by mail.postgresql.org (Postfix) with ESMTP id C751EB5DC0A
	for <pgsql-admin@postgresql.org>; Wed, 20 Jul 2011 18:16:32 -0300 (ADT)
Received: from betelgeuse.pinpointresearch.com
	(betelgeuse.pinpointresearch.com [192.168.1.179])
	by polaris.pinpointresearch.com (Postfix) with ESMTP id 46917E00EC83;
	Wed, 20 Jul 2011 14:16:32 -0700 (PDT)
Message-ID: <4E2745B0.1010405@pinpointresearch.com>
Date: Wed, 20 Jul 2011 14:16:32 -0700
From: Steve Crawford <scrawford@pinpointresearch.com>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.2.18) Gecko/20110617 Lightning/1.0b2 Thunderbird/3.1.11
MIME-Version: 1.0
To: A J <s5aly@yahoo.com>
CC: PG Admin <pgsql-admin@postgresql.org>
Subject: Re: How frequently to defrag(cluster)
References: <1311191898.49250.YahooMailNeo@web120015.mail.ne1.yahoo.com>
	<4E2742FB.40201@pinpointresearch.com>
In-Reply-To: <4E2742FB.40201@pinpointresearch.com>
Content-Type: multipart/alternative;
	boundary="------------010107010907010908070003"
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-3.092 tagged_above=-10 required=5
	tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, RP_MATCHES_RCVD=-1.193
X-Spam-Level: 
X-Archive-Number: 201107/150
X-Sequence-Number: 37929

This is a multi-part message in MIME format.
--------------010107010907010908070003
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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


--------------010107010907010908070003
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
    <meta content="text/html; charset=ISO-8859-1"
      http-equiv="Content-Type">
  </head>
  <body bgcolor="#ffffff" text="#000000">
    On 07/20/2011 02:04 PM, Steve Crawford wrote:
    <blockquote cite="mid:4E2742FB.40201@pinpointresearch.com"
      type="cite">
      <meta content="text/html; charset=ISO-8859-1"
        http-equiv="Content-Type">
      On 07/20/2011 12:58 PM, A J wrote:
      <blockquote
        cite="mid:1311191898.49250.YahooMailNeo@web120015.mail.ne1.yahoo.com"
        type="cite">
        <div style="color: rgb(0, 0, 0); background-color: rgb(255, 255,
          255); font-family: times new roman,new york,times,serif;
          font-size: 12pt;">
          <div>I understand that 'cluster' performs the role of defrag
            ...</div>
        </div>
      </blockquote>
      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).<br>
      <br>
      <br>
      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.<br>
    </blockquote>
    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.<br>
    <br>
    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.<br>
    <br>
    Cheers,<br>
    Steve<br>
    <br>
  </body>
</html>

--------------010107010907010908070003--