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

> Hi
>
> We currently have a 16CPU 32GB box running postgres 8.2.
>
> When I do a pg_dump with the following parameters "/usr/bin/pg_dump -E=
  =

> UTF8 -F c -b" I get a file of 14GB in size.
>
> But the database is 110GB in size on the disk.  Why the big difference=
  =

> in size?  Does this have anything to do with performance?

	I have a 2GB database, which dumps to a 340 MB file...
	Two reasons :

	- I have lots of big fat but very necessary indexes (not included in du=
mp)
	- Dump is compressed with gzip which really works well on database data=
.

	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 t=
est  =

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 have bloa=
t.
	Time to CLUSTER, or REINDEX, or VACUUM FULL (your choice), on the table=
s  =

that are bloated, and take note to vacuum those more often (and perhaps =
 =

tune the autovacuum).
	Judicious use of CLUSTER on that small, but extremely often updated tab=
le  =

can also be a very good option.
	8.3 and its new HOT feature are also a good idea.