Skip site navigation (1) Skip section navigation (2)

Re: vacuum full 100 mins plus?

From: Mike Rylander <miker(at)purplefrog(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: vacuum full 100 mins plus?
Date: 2004-07-15 15:14:37
Message-ID: cd67ah$20vi$1@news.hub.org (view raw or flat)
Thread:
Lists: pgsql-performance
Tom Lane wrote:

> Christopher Browne <cbbrowne(at)acm(dot)org> writes:
>> A long time ago, in a galaxy far, farpliers PHatcher(at)macys(dot)com (Patrick
>> Hatcher) wrote:
>>> Answered my own question.  I gave up the vacuum full after 150 mins.  I
>>> was able to export to a file, vacuum full the empty table, and reimport
>>> in less
>>> than 10 mins.  I suspect the empty item pointers and the sheer number of
>>> removable rows was causing an issue.
> 
>> In that case, you'd be a little further better off if the steps were:
>>  - drop indices;
>>  - copy table to file (perhaps via pg_dump -t my_table);
>>  - truncate the table, or drop-and-recreate, both of which make
>>    it unnecessary to do _any_ vacuum of the result;
>>  - recreate indices, probably with SORT_MEM set high, to minimize
>>    paging to disk
>>  - analyze the table (no need to vacuum if you haven't created any
>>    dead tuples)
>>  - cut SORT_MEM back down to "normal" sizes
> 
> Rather than doing all this manually, you can just CLUSTER on any handy
> index.  In 7.5, another possibility is to issue one of the forms of
> ALTER TABLE that force a table rewrite.
> 
> The range of usefulness of VACUUM FULL is really looking narrower and
> narrower to me.  I can foresee a day when we'll abandon it completely.

I would love to see this 10lb sledge hammer go away when we have enough tiny
screwdrivers and needlenose pliers to make it obsolete!

> 
> regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
>                http://www.postgresql.org/docs/faqs/FAQ.html


In response to

pgsql-performance by date

Next:From: Scott MarloweDate: 2004-07-15 16:20:34
Subject: Re: Swapping in 7.4.3
Previous:From: Jim EwertDate: 2004-07-15 13:49:33
Subject: Re: Swapping in 7.4.3

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group