Re: VACUUM FULL versus CLUSTER ON - Mailing list pgsql-general

How long does it take do a database dump (with gzip -1 via | and > ), drop
this database
and create the database and restore it from the backup. That is my solution,
but I dont
know how long it will take to restore your database and i dont have so large
databases.

> Secondly this sounds like a perfect time for you to consider upgrading to
8.1

I would not do this without a test (not only a dump/restore test, I would
test it with
your application too)

Greetings,

-Franz

-----Ursprüngliche Nachricht-----
Von: Sven Willenberger [mailto:sven@dmv.com]
Gesendet: Freitag, 7. Juli 2006 19:26
An: Joshua D. Drake
Cc: pgsql-general@postgresql.org
Betreff: Re: [GENERAL] 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


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to majordomo@postgresql.org so that your
       message can get through to the mailing list cleanly