Received: from maia.hub.org (maia-3.hub.org [200.46.204.243])
	by mail.postgresql.org (Postfix) with ESMTP id 45B5BB5DBBF
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Wed, 20 Jul 2011 18:05:10 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia,
	port 10024) with ESMTP id 48202-06 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Wed, 20 Jul 2011 21:05:04 +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 DBD82B5DC11
	for <pgsql-admin@postgresql.org>; Wed, 20 Jul 2011 18:05:03 -0300 (ADT)
Received: from betelgeuse.pinpointresearch.com
	(betelgeuse.pinpointresearch.com [192.168.1.179])
	by polaris.pinpointresearch.com (Postfix) with ESMTP id 21175E00EC83;
	Wed, 20 Jul 2011 14:05:00 -0700 (PDT)
Message-ID: <4E2742FB.40201@pinpointresearch.com>
Date: Wed, 20 Jul 2011 14:04:59 -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>
In-Reply-To: <1311191898.49250.YahooMailNeo@web120015.mail.ne1.yahoo.com>
Content-Type: multipart/alternative;
	boundary="------------050404030806010207070506"
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/149
X-Sequence-Number: 37928

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

On 07/20/2011 12:58 PM, A J wrote:
> I understand that 'cluster' performs the role of defrag (along with 
> rewriting in index order) in Postgres.
> How frequently does one have to run cluster ? Any thumb-rules  or 
> experience ? How do I find if my table is fragmented enough to need a 
> cluster ?
> We are still to use Postgres in production, just evaluating at this point.
>
> Thanks for any pointers.
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).

When you update a row in PostgreSQL, the server inserts the "new" 
version of the row but also keeps the "old" row available until it is no 
longer visible to any running queries after which that space can be 
reclaimed. Locating reclaimable space and making it available for reuse 
is the job of the autovacuum process. If everything is tuned properly, 
PostgreSQL will keep refilling the "holes" in the table on its own.

There are some exceptions. A query that updates all rows in a table as 
might happen in some maintenance operations may increase the table-size 
more than you desire. This can be corrected using the cluster command.

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.

If you are evaluating PG, you might want to post some info on the nature 
of the application. People are here because they like and use PostgreSQL 
but most are quite open about saying when it isn't an appropriate 
solution as well. You may also get tips on avoiding some common pitfalls 
that can prevent PostgreSQL from showing its full potential.

Cheers,
Steve


--------------050404030806010207070506
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 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
          (along with rewriting in index order) in Postgres.</div>
        <div>How frequently does one have to run cluster ? Any
          thumb-rules &nbsp;or experience ? How do I find if my table is
          fragmented enough to need a cluster ?</div>
        <div>We are still to use Postgres in production, just evaluating
          at this point.</div>
        <div><br>
        </div>
        <div>Thanks for any pointers.</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>
    When you update a row in PostgreSQL, the server inserts the "new"
    version of the row but also keeps the "old" row available until it
    is no longer visible to any running queries after which that space
    can be reclaimed. Locating reclaimable space and making it available
    for reuse is the job of the autovacuum process. If everything is
    tuned properly, PostgreSQL will keep refilling the "holes" in the
    table on its own.<br>
    <br>
    There are some exceptions. A query that updates all rows in a table
    as might happen in some maintenance operations may increase the
    table-size more than you desire. This can be corrected using the
    cluster command.<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>
    <br>
    If you are evaluating PG, you might want to post some info on the
    nature of the application. People are here because they like and use
    PostgreSQL but most are quite open about saying when it isn't an
    appropriate solution as well. You may also get tips on avoiding some
    common pitfalls that can prevent PostgreSQL from showing its full
    potential.<br>
    <br>
    Cheers,<br>
    Steve<br>
    <br>
  </body>
</html>

--------------050404030806010207070506--