From: Steve Crawford <scrawford@pinpointresearch.com>
To: A J <s5aly@yahoo.com>
Cc: PG Admin <pgsql-admin@postgresql.org>
Subject: Re: How frequently to defrag(cluster)
Date: Wed, 20 Jul 2011 14:16:32 -0700
Message-ID: <4E2745B0.1010405@pinpointresearch.com> (raw)
In-Reply-To: <4E2742FB.40201@pinpointresearch.com>
References: <1311191898.49250.YahooMailNeo@web120015.mail.ne1.yahoo.com>
<4E2742FB.40201@pinpointresearch.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: scrawford@pinpointresearch.com, s5aly@yahoo.com
Subject: Re: How frequently to defrag(cluster)
In-Reply-To: <4E2745B0.1010405@pinpointresearch.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox