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 9A853D1C9FB for ; Thu, 8 Apr 2004 09:51:16 -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 07503-08 for ; Thu, 8 Apr 2004 09:51:14 -0300 (ADT) Received: from hotmail.com (bay8-dav64.bay8.hotmail.com [64.4.26.199]) by svr1.postgresql.org (Postfix) with ESMTP id 3E19FD1D282 for ; Thu, 8 Apr 2004 09:51:12 -0300 (ADT) Received: from mail pickup service by hotmail.com with Microsoft SMTPSVC; Thu, 8 Apr 2004 05:51:15 -0700 Received: from 80.21.203.111 by bay8-dav64.bay8.hotmail.com with DAV; Thu, 08 Apr 2004 12:51:15 +0000 X-Originating-IP: [80.21.203.111] X-Originating-Email: [reina_ga@hotmail.com] X-Sender: reina_ga@hotmail.com From: "Tony and Bryn Reina" To: "Uwe C. Schroeder" , References: <272e4be7.0404080115.39f1cb36@posting.google.com> <200404080257.46102.uwe@oss4u.com> Subject: Re: Database storage bloat Date: Thu, 8 Apr 2004 14:51:14 +0200 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1158 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 Message-ID: X-OriginalArrivalTime: 08 Apr 2004 12:51:15.0748 (UTC) FILETIME=[2DEC4640:01C41D68] X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200404/141 X-Sequence-Number: 13114 Yep. That's after a 'vacuum verbose analyze'. -Tony ----- Original Message ----- From: "Uwe C. Schroeder" To: "Tony Reina" ; Sent: Thursday, April 08, 2004 11:57 AM Subject: Re: [ADMIN] Database storage bloat -----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 - -- 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= =BoL3 -----END PGP SIGNATURE-----