pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedHow frequently to defrag(cluster)
3+ messages / 2 participants
[nested] [flat]
* How frequently to defrag(cluster)
@ 2011-07-20 19:58 A J <s5aly@yahoo.com>
0 siblings, 1 reply; 3+ messages in thread
From: A J @ 2011-07-20 19:58 UTC (permalink / raw)
To: pgsql-admin
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: How frequently to defrag(cluster)
@ 2011-07-20 21:04 Steve Crawford <scrawford@pinpointresearch.com>
parent: A J <s5aly@yahoo.com>
0 siblings, 1 reply; 3+ messages in thread
From: Steve Crawford @ 2011-07-20 21:04 UTC (permalink / raw)
To: A J <s5aly@yahoo.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: How frequently to defrag(cluster)
@ 2011-07-20 21:16 Steve Crawford <scrawford@pinpointresearch.com>
parent: Steve Crawford <scrawford@pinpointresearch.com>
0 siblings, 0 replies; 3+ messages in thread
From: Steve Crawford @ 2011-07-20 21:16 UTC (permalink / raw)
To: A J <s5aly@yahoo.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2011-07-20 21:16 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-07-20 19:58 How frequently to defrag(cluster) A J <s5aly@yahoo.com>
2011-07-20 21:04 ` Steve Crawford <scrawford@pinpointresearch.com>
2011-07-20 21:16 ` Steve Crawford <scrawford@pinpointresearch.com>
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