On Mon, 20 Nov 2006 14:21:13 +0000, Peter Corlett wrote:
> On Mon, Nov 20, 2006 at 01:59:31PM +0000, Peter Haworth wrote: [...]
> > Here's my limited understanding:
> > * "analyze" is what rebuilds the stats
> > * "vacuum" is like defragging a disk
> > * "vacuum analyze" combines the first two
> > * "vacuum full" is like vacuum, but also truncates the files to
> >   the minimum possible size
>
> The analogy is poor; CLUSTER is more akin to defragging a disk: it
> sorts tables by an index so that sequential access is faster. Plain
> VACUUM is just a garbage-collector and doesn't make any attempt to
> consolidate the allocated regions. I *think* a VACUUM FULL might
> also do a CLUSTER as a side-effect.

You're right; "vacuum" isn't much like defragging. The point I was
trying to make was that a plain vacuum doesn't decrease the disk space
used; it just allows some of it to be reused.

A full vacuum doesn't do clustering. Although it requires an exclusive
lock on the table, because it's processing the data in sequential scan
order, "vacuum full" can easily reuse the existing files holding the
table's data.

On the other hand, because clustering is done in index order, it has
to create entirely new table files (you'll see that
pg_class.relfilenode changes to point to the new files once it's
finished). This means that clustering requires huge amounts of disk
space for tables on which it's actually useful. There's a table in our
stats database which contains half a billion records, and I always
have to negotiate carefully with the systems team for an extra 100GB
of temporary disk space when it needs to be reclustered. Fortunately,
this doesn't happen very often.

-- 
	Peter Haworth	pmh@edison.ioppublishing.com
"Rather than go to the effort of a public stoning, the easiest thing would be
 for people to just mail some stones to me, and I can throw them at myself
 until I'm dead.  No more than 3 stones per entrant, please, and remember,
 anything over 45 lbs is just a waste of all our time."       -- CR Hobbs