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 0B1FED1BAF7 for ; Thu, 8 Apr 2004 06:40:26 -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 49156-08 for ; Thu, 8 Apr 2004 06:40:24 -0300 (ADT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by svr1.postgresql.org (Postfix) with ESMTP id 72E87D1BAEA for ; Thu, 8 Apr 2004 06:40:23 -0300 (ADT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.12.9/8.12.9) with ESMTP id i389eNSx086724 for ; Thu, 8 Apr 2004 09:40:23 GMT (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.12.9/8.12.9/Submit) id i389F5IY082460 for pgsql-admin@postgresql.org; Thu, 8 Apr 2004 09:15:05 GMT From: reina_ga@hotmail.com (Tony Reina) X-Newsgroups: comp.databases.postgresql.admin Subject: Database storage bloat Date: 8 Apr 2004 02:15:07 -0700 Organization: http://groups.google.com Lines: 28 Message-ID: <272e4be7.0404080115.39f1cb36@posting.google.com> Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit X-Complaints-To: groups-abuse@google.com To: pgsql-admin@postgresql.org X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200404/137 X-Sequence-Number: 13110 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