Date: Fri, 13 Feb 2009 23:34:53 +0100
From: Tino Schwarze <postgresql@tisc.de>
To: pgsql-admin@postgresql.org
Subject: Re: Vacuum wait time problem
Message-ID: <20090213223453.GH12562@easy2.in-chemnitz.de>
References: <4995ABC0.4090401@musicreports.com>
	<dcc563d10902131321h548104a0vdf3d16f0dc27f015@mail.gmail.com>
	<4995EC90.5020200@musicreports.com>
	<dcc563d10902131413x3de3936fkdf4348a3ec22dbe6@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <dcc563d10902131413x3de3936fkdf4348a3ec22dbe6@mail.gmail.com>
User-Agent: Mutt/1.5.13 (2006-08-11)

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.

Thanks,

Tino.

-- 
"What we nourish flourishes." - "Was wir nähren erblüht."

www.lichtkreis-chemnitz.de
www.craniosacralzentrum.de