Question about clustering indexes and restores

From: Harold A(dot) Giménez Ch(dot) <harold(dot)gimenez(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Question about clustering indexes and restores
Date: 2009-01-22 19:52:12
Message-ID: c807ef1a0901221152v3517b9e3q809ea4bcb7262107@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Hi list,

Clustering my indexes dramatically improves the query performance of many of
my queries. Also, the actual clustering takes a very long time for big
databases, roughly 20 hours. I have two questions about how to improve this:

1. I've tweaked maintenance_mem_max and effective_cache_size to a point
where the cluster operation uses a good chunk of my physical RAM, and the OS
does not start swapping. Is there any other parameter I should look at?

2. Reading the documentation for cluster at
http://www.postgresql.org/docs/8.3/interactive/sql-cluster.html, I see that
all clustering does is reorder the data on disk to 'match' the order of the
clustered index. My question is, if I dump a clustered database using
pg_dump in custom format, is it necessary to cluster after restoring it? Or
does a dump/restore not guarantee that the order of the data restored is the
same as the original dumped database?

3. Somewhat related to #2, what is the best way to move data from a staging
database on one server, to the production environment on a different server?
I've been using pg_dump/pg_restore, but there must be a better way...

Thanks for any pointers,

-Harold

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Kenneth Marshall 2009-01-22 19:58:06 Re: Question about clustering indexes and restores
Previous Message Alvaro Herrera 2009-01-22 19:00:12 Re: postgresql 8.3 tps rate