Thread: VACUUM vs VACUUM ANALYZE vs CLUSTER

Hi folks,

Could someone point me at any documentation that explains how VACUUM,
VACUUM ANAYLZE and CLUSTER relate to one another?  From my reading of
the PostgreSQL documentation and the FAQ, I am confused on these points:

1) Is VACUUM ANALYZE a complete superset of VACUUM?

2) VACUUM and CLUSTER both affect the arrangement of rows on disk.  Does
   CLUSTER leave holes behind, and if so is there a run order for VACUUM
   and CLUSTER that results in well-packed _and_ well-orderd rows on
   disk?

I don't think I need to be spoonfed and am more than willing to read
whole chapters to find my answer, but I'm pretty certain the PostgreSQL
docs (as distributed with 7.1.3) don't resolve this confusion.

My thanks in advance to any who choose to respond.

Ciao,
Sheldon.

Sheldon Hearn <sheldonh@starjuice.net> writes:
> 1) Is VACUUM ANALYZE a complete superset of VACUUM?

Yes.  There used to be some documentation suggesting otherwise, but
I thought we'd fixed it all.  If you still see something unclear on
this, please cite chapter and verse.

> 2) VACUUM and CLUSTER both affect the arrangement of rows on disk.  Does
>    CLUSTER leave holes behind, and if so is there a run order for VACUUM
>    and CLUSTER that results in well-packed _and_ well-orderd rows on
>    disk?

CLUSTER should create a tightly-packed file; there's no need for VACUUM
afterwards.  (But VACUUM ANALYZE, or just ANALYZE in 7.2, would be
worthwhile to ensure that the planner has good data statistics for the
updated table.  It won't save you even one byte of table space, mind
you, only update pg_statistic entries.)

Conversely, VACUUM just before CLUSTER is a complete waste of time,
since any dead tuples that VACUUM might remove won't be copied by
CLUSTER anyway.

> I don't think I need to be spoonfed and am more than willing to read
> whole chapters to find my answer, but I'm pretty certain the PostgreSQL
> docs (as distributed with 7.1.3) don't resolve this confusion.

Feel free to submit suggested documentation patches... if you are
confused, so will be those who follow, so tell us how to make it
clearer!

            regards, tom lane

On Wed, 19 Sep 2001 23:37:56 -0400, Tom Lane wrote:

> Conversely, VACUUM just before CLUSTER is a complete waste of time,
> since any dead tuples that VACUUM might remove won't be copied by
> CLUSTER anyway.

Okay.  So I'm right in thinking that

-> VACUUM then CLUSTER            VACUUM pointless
-> VACUUM ANALYZE then CLUSTER        ditto
-> CLUSTER then VACUUM            ditto
-> CLUSTER then VACUUM ANALYZE        ANALYZE useful

> Feel free to submit suggested documentation patches... if you are
> confused, so will be those who follow, so tell us how to make it
> clearer!

Sure.  Is the HTML the documentation source format as well, or is there
some SGML or something else that I should create patches against?

BTW, you mentioned PostgreSQL 7.2.  I assume you were just pointing out
that you can ANALYZE independently of a VACUUM in 7.2, rather than
recommending its use in a hard-working production environment?

Last thing... any chance that CLUSTER will learn some time soon to
automatically recreate my indeces and grants for me? :-)

Thanks!

Ciao,
Sheldon.

Sheldon Hearn <sheldonh@starjuice.net> writes:
>> Feel free to submit suggested documentation patches...

> Sure.  Is the HTML the documentation source format as well, or is there
> some SGML or something else that I should create patches against?

SGML is the source format.  The doc sources are included in our source
tarballs.

> BTW, you mentioned PostgreSQL 7.2.  I assume you were just pointing out
> that you can ANALYZE independently of a VACUUM in 7.2,

Yes...

> rather than recommending its use in a hard-working production environment?

Er, what?  Depending on your situation, I think a separate ANALYZE might
be useful in production.

> Last thing... any chance that CLUSTER will learn some time soon to
> automatically recreate my indeces and grants for me? :-)

It's on the TODO list.  Dunno when someone will get around to it.

            regards, tom lane

Sheldon Hearn <sheldonh@starjuice.net> writes:
> Oh for _sure_!  I was just clarifying your position on the use of 7.2 in
> a production environment.

Ah.  No, I wouldn't recommend pre-beta software for use in production ;-)
I was just pointing out that the next release will have more
flexibility.

            regards, tom lane

On Thu, 20 Sep 2001 09:58:02 -0400, Tom Lane wrote:

> Er, what?  Depending on your situation, I think a separate ANALYZE might
> be useful in production.

Oh for _sure_!  I was just clarifying your position on the use of 7.2 in
a production environment.

> It's on the TODO list.  Dunno when someone will get around to it.

Once I got past the horror of losing my primary key and finding that
ALTER TABLE doesn't support ADD CONSTRAINT ... PRIMARY KEY, things got
better. :-)

Ciao,
Sheldon.