How to determine whether to VACUUM or CLUSTER

From: "Ken Shaw" <kshaw(at)belarc(dot)com>
To: <pgsql-performance(at)postgresql(dot)org>
Subject: How to determine whether to VACUUM or CLUSTER
Date: 2005-06-15 15:34:18
Message-ID: 011301c571bf$b83c77f0$800101df@xyz
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Hi All,

I have an app that updates a PostgreSQL db in a batch fashion. After
each batch (or several batches), it issues VACUUM and ANALYZE calls on
the updated tables. Now I want to cluster some tables for better
performance. I understand that doing a VACUUM and a CLUSTER on a table
is wasteful as the CLUSTER makes the VACUUM superfluous. The app does
not have a built-in list of the tables and whether each is clustered or
not. It looks to me as if the only way to determine whether to issue a
VACUUM (on a non-clustered table) or a CLUSTER (on a clustered table) is
to query the table "pg_index", much like view "pg_indexes" does, for the
column "indisclustered". Is this right?

Also, how expensive is CLUSTER compared to VACUUM? Does CLUSTER read in
the whole table, sort it, and write it back out? Or write out a
completely new file? Is the time for a CLUSTER the same whether one row
is out of place or the table is completely disordered?

Thanks,
Ken

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Karim Nassar 2005-06-15 16:52:02 Re: Index ot being used
Previous Message Bruno Wolff III 2005-06-15 14:19:49 Re: Needed: Simplified guide to optimal memory configuration