Date: Mon, 14 Apr 2008 11:33:54 +0200
To: "Adrian Moisey" <adrian@careerjunction.co.za>
Subject: Re: db size
From: PFC <lists@peufeu.com>
Cc: pgsql-performance@postgresql.org
Content-Type: text/plain; format=flowed; delsp=yes; charset=utf-8
MIME-Version: 1.0
References: <4802F9E4.3000704@careerjunction.co.za>
	<op.t9lf4tvrcigqcu@apollo13.peufeu.com>
	<48032237.4040604@careerjunction.co.za>
Content-Transfer-Encoding: Quoted-Printable
Message-ID: <op.t9lgusticigqcu@apollo13.peufeu.com>
In-Reply-To: <48032237.4040604@careerjunction.co.za>
User-Agent: Opera Mail/9.24 (Linux)

>
>>     If you suspect your tables or indexes are bloated, restore your  =

>> dump to a test box.
>>     Use fsync=3Doff during restore, you don't care about integrity on=
 the  =

>> test box.
>>     This will avoid slowing down your production database.
>>     Then look at the size of the restored database.
>>     If it is much smaller than your production database, then you hav=
e  =

>> bloat.
>
> I have done that, and I get the following:
>
> the live one is 113G
> the restored one is 78G

	Ah.
	Good news for you is that you know that you can do something ;)

	Now, is the bloat in the tables (which tables ?) or in the indexes (whi=
ch  =

indexes ?), or in the toast tables perhaps, or in the system catalogs or=
  =

all of the above ? Or perhaps there is a long-forgotten process that got=
  =

zombified while holding a huge temp table ? (not very likely, but who  =

knows).
	Use pg_relation_size() and its friends to get an idea of the size of  =

stuff.
	Perhaps you have 1 extremely bloated table or index, or perhaps  =

everything is bloated.
	The solution to your problem depends on which case you have.