Hello,

Does running "cluster" remove the need to run "vacuum"?

I get a feeling that since cluster is already physically reordering
the rows, it may as well remove the dead rows... no?

My second question is, if vacuum is still needed, does it matter
whether I run vacuum first or cluster first?

Here's our current weekly db maintenance routine:

1. vacuum full
2. cluster
3. reindex
4. analyze

Thanks,
Jimmy

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Jimmy Choi wrote:
> Hello,
>
> Does running "cluster" remove the need to run "vacuum"?

My understanding is that `CLUSTER' creates a new table file, then swaps
it out for the old one.

http://www.postgresql.org/docs/8.3/s...l-cluster.html

" During the cluster operation, a temporary copy of the table is created
that contains the table data in the index order. Temporary copies of
each index on the table are created as well. Therefore, you need free
space on disk at least equal to the sum of the table size and the index
sizes. "

It's not stated explicitly, but I'm pretty sure discussion here has
mentioned that too. Given that, VACUUM FULL on a just-CLUSTERed table
should be redundant.

The easy way to be sure is to use ANALYZE VERBOSE to examine the dead
row counts etc before and after each operation.

--
Craig Ringer

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Craig Ringer escribió:

> It's not stated explicitly, but I'm pretty sure discussion here has
> mentioned that too. Given that, VACUUM FULL on a just-CLUSTERed table
> should be redundant.

It is, and a REINDEX is redundant too because CLUSTER does it
internally.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Presumably, even if CLUSTER does reindexing internally, it only does
that for the index used for clustering. Since REINDEX includes all
indices, CLUSTER cannot truly replace REINDEX. Correct?

Jimmy

On Wed, Apr 16, 2008 at 12:06 PM, Alvaro Herrera
<alvherre@commandprompt.com> wrote:
> Craig Ringer escribió:
>
>
> > It's not stated explicitly, but I'm pretty sure discussion here has
> > mentioned that too. Given that, VACUUM FULL on a just-CLUSTERed table
> > should be redundant.
>
> It is, and a REINDEX is redundant too because CLUSTER does it
> internally.
>
> --
> Alvaro Herrera http://www.CommandPrompt.com/
> PostgreSQL Replication, Consulting, Custom Development, 24x7 support
>

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Jimmy Choi escribió:
> Presumably, even if CLUSTER does reindexing internally, it only does
> that for the index used for clustering. Since REINDEX includes all
> indices, CLUSTER cannot truly replace REINDEX. Correct?

No. Cluster rewrites all indexes (otherwise their entries would end up
pointing to incorrect places in the heap.)

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general