Re: VACUUM FULL versus CLUSTER ON

On Fri, 2006-07-07 at 09:55 -0700, Joshua D. Drake wrote:
> On Friday 07 July 2006 08:19, Sven Willenberger wrote:
> > Postgresql 8.0.4 on FreeBSD 5.4
> >
> > I have a table consisting of some 300million rows that, every couple of
> > months, has 100 million rows deleted from it (an immediately vacuumed
> > afterward). Even though it gets routinely vacuumed (the only
> > deletions/updates are just the quarterly ones), the freespace map was
> > not increased in size to keep up with the growing size of the other
> > tables in the database which do experience many updates,etc.
>
> Based on the size of the table, you may want to:
>
> Backup the table
> Drop the table
> Restore the table
>
> Is is possible that this will be faster in this instance.
>
> Secondly this sounds like a perfect time for you to consider upgrading to 8.1
> and making use of table partitioning. That way you can just truncate the child
> table containing the old data.
>
> Sincerely,
>
> Joshua D. Drake

Doing a quick check reveals that the relation in question currently
consumes 186GB of space (which I highly suspect is largely bloat). The
delete was just run this past weekend as was the recreation of the
indexes. I have 50GB of disk space left; If I vacuum full, it does not
need to create a temporary copy of the relation and indexes like cluster
does, does it? At this point, I think CLUSTER ON is out of the question
due to the need to create the temporary table and indexes (I will run
out of space during the operation).

I do plan on migrating the whole mess to a new server which will run 8.1
(I had looked at inheritance for partitioning, I am glad to see that 8.1
took the concept and ran with it further :) ) This new server will use
an external SAS array so I should simply be able to add another array as
the need arises and partition to it via tablespace.

Thanks to all who offered suggestions; it would appear that at this
stage my only option to buy some time is try a vacuum full. My final
question: can I leave the indexes in place when I vacuum full? I assume
this will only operate on the table itself?

Sven