Search Postgresql Archives

Re: VACUUM FULL versus CLUSTER ON

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxx] 
Gesendet: Freitag, 7. Juli 2006 19:26
An: Joshua D. Drake
Cc: pgsql-general@xxxxxxxxxxxxxx
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@xxxxxxxxxxxxxx so that your
       message can get through to the mailing list cleanly


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux