DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type:content-transfer-encoding;
	b=MNRJFbKuS7S0sTJY51ga3cLUFdWyBwt8K292SxPR2Ns5cfHjpS3yYm+nU4XwEixANd
	x2py3Vniw0cKpDxmvlVtLDPWuIPZ+HBAE925gritaIRsw/Rnavx8EFG9fa8y4byGQxAq
	aNygNY1D9py3wVeJmCAOLyeEwAG4RTJld380E=
MIME-Version: 1.0
In-Reply-To: <20090213223453.GH12562@easy2.in-chemnitz.de>
References: <4995ABC0.4090401@musicreports.com>
	<dcc563d10902131321h548104a0vdf3d16f0dc27f015@mail.gmail.com>
	<4995EC90.5020200@musicreports.com>
	<dcc563d10902131413x3de3936fkdf4348a3ec22dbe6@mail.gmail.com>
	<20090213223453.GH12562@easy2.in-chemnitz.de>
Date: Fri, 13 Feb 2009 15:40:04 -0700
Message-ID: <dcc563d10902131440i342daae8w30c1864b4c8fd613@mail.gmail.com>
Subject: Re: Vacuum wait time problem
From: Scott Marlowe <scott.marlowe@gmail.com>
To: Tino Schwarze <postgresql@tisc.de>
Cc: pgsql-admin@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

On Fri, Feb 13, 2009 at 3:34 PM, Tino Schwarze <postgresql@tisc.de> wrote:
> On Fri, Feb 13, 2009 at 03:13:28PM -0700, Scott Marlowe wrote:
>
> [...]
>
>> Yeah, that's pretty bad.  ~2 Million live rows and ~18 Million dead
>> ones is a pretty badly bloated table.
>>
>> Vacuum full is one way to reclaim that lost space.  You can also dump
>> and restore that one table, inside a drop / create restraints in a
>> transaction during maintenance if you can.  A Vacuum full is quite
>> intrusive, so avoid it during normal working hours.  Dumping and
>> reloading the whole db may be faster than either a vacuum full or a
>> cluster.    A common trick is to do something like:
>>
>> begin;
>> select * into ordermydata from bigbloatedtable order by some_field;
>> delete * from bigbloatedtable;
>> insert into bigbloatedtable select * from ordermydata;
>> commit;
>>
>> This will both put your table in some order which might help, and
>> remove the bloat.
>
> Really? Wouldn't that add even more bloat? How does that work? (I'd
> expect a drop table/create table instead of the delete...)
> Note: I suppose that you know a lot more about PG than I do, so I'm just
> curious.

Whoops, meant
truncate bigbloatedtable;
sheesh, long week.