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 9FD91D1D09E for ; Thu, 8 Apr 2004 08:43:35 -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 95209-04 for ; Thu, 8 Apr 2004 08:43:35 -0300 (ADT) Received: from outbound0.sv.meer.net (outbound0.sv.meer.net [205.217.152.13]) by svr1.postgresql.org (Postfix) with ESMTP id 003CED1D098 for ; Thu, 8 Apr 2004 08:43:33 -0300 (ADT) Received: from mail.meer.net (mail.meer.net [209.157.152.14]) by outbound0.sv.meer.net (8.12.10/8.12.6) with ESMTP id i38BfJGO033356; Thu, 8 Apr 2004 04:41:20 -0700 (PDT) (envelope-from trainor@uic.edu) Received: from uic.edu (c-24-12-179-158.client.comcast.net [24.12.179.158]) by mail.meer.net (8.12.1/8.12.2/meer) with ESMTP id i38BfDmU000306; Thu, 8 Apr 2004 04:41:19 -0700 (PDT) (envelope-from trainor@uic.edu) Message-ID: <40753A71.3060104@uic.edu> Date: Thu, 08 Apr 2004 06:41:37 -0500 From: Douglas Trainor User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.6) Gecko/20040113 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Tony Reina Cc: pgsql-admin@postgresql.org Subject: Re: Database storage bloat References: <272e4be7.0404080115.39f1cb36@posting.google.com> In-Reply-To: <272e4be7.0404080115.39f1cb36@posting.google.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200404/140 X-Sequence-Number: 13113 Saying "we've set field sizes to their theoretical skinniness" makes me think that you may have the wrong data types. For example, you may have used CHAR and not VARCHAR. douglas 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, >[...] > >