X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id E5F7BD1BB7B for ; Thu, 8 Apr 2004 06:58:46 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 53132-06 for ; Thu, 8 Apr 2004 06:58:45 -0300 (ADT) Received: from pop.sanbrunocable.com (pop.sanbrunocable.com [65.172.230.120]) by svr1.postgresql.org (Postfix) with ESMTP id 56241D1BAEA for ; Thu, 8 Apr 2004 06:58:43 -0300 (ADT) Received: from oss4u.com (qmailr@cpe-65-172-234-54.sanbrunocable.com [65.172.234.54]) by pop.sanbrunocable.com (8.11.6/8.11.6) with SMTP id i389wGS22778 for ; Thu, 8 Apr 2004 02:58:16 -0700 Received: (qmail 22417 invoked from network); 8 Apr 2004 09:56:50 -0000 Received: from tiburon.oss4u.com (192.168.1.4) by gw.oss4u.com (192.168.1.1) with ESMTP; 08 Apr 2004 09:56:50 -0000 From: "Uwe C. Schroeder" Organization: OSS4U, LLC To: reina_ga@hotmail.com (Tony Reina), pgsql-admin@postgresql.org Subject: Re: Database storage bloat Date: Thu, 8 Apr 2004 01:57:45 -0800 User-Agent: KMail/1.5.3 References: <272e4be7.0404080115.39f1cb36@posting.google.com> In-Reply-To: <272e4be7.0404080115.39f1cb36@posting.google.com> MIME-Version: 1.0 Content-Type: Text/Plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Content-Description: clearsigned data Content-Disposition: inline Message-Id: <200404080257.46102.uwe@oss4u.com> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200404/138 X-Sequence-Number: 13111 -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Did you run vacuum full after your import ? On Thursday 08 April 2004 02:15 am, Tony Reina wrote: > I'm developing a database for scientific recordings. These recordings > are traditionally saved as binary flat files for simplicity and > compact storage. Although I think ultimately having a database is > better than 1,000s of flat files in terms of data access, I've found > that the database (or at least my design) is pretty wasteful on > storage space compared with the binary flat files. > > In particular, I tried importing all of the data from a binary flat > file that is 1.35 MB into a PostgreSQL database (a very small test > file; average production file is probably more like 100 MB). The > database directory ballooned from 4.1 MB to 92 MB (a bloat of 65X the > original storage of the binary flat file). > > Now I know that table design and normalizing is important. As far as > my partner and I can tell, we've made good use of normalizing (no > redundancy), we've set field sizes to their theoretical skinniness, > and we've made use of foreign keys and views. I'm also aware that > indicies/keys and other database internals will necessarily make the > DBMS solution bloated in terms of storage space. However, a 65X bloat > in space seems excessive. > > Has anyone run across similar storage concerns? I'd be interested in > knowing if I just have really poorly designed tables, or if something > else is going on here. I figure a bloat of 3-4X would be permissible > (and possibly expected). But this bloat just seems too much. > > Thanks. > -Tony > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org - --=20 UC - -- Open Source Solutions 4U, LLC 2570 Fleetwood Drive Phone: +1 650 872 2425 San Bruno, CA 94066 Cell: +1 650 302 2405 United States Fax: +1 650 872 2417 -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.3 (GNU/Linux) iD8DBQFAdSIajqGXBvRToM4RApqaAJ9wOZa8NAWqgPk4ZZpWgeh1L2VwqQCdHKgv 3ruwuPZRC/rOxX3nb1q6khE=3D =3DBoL3 -----END PGP SIGNATURE-----