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 48D25D1D1B8 for ; Thu, 8 Apr 2004 10:47: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 41989-01 for ; Thu, 8 Apr 2004 10:47:16 -0300 (ADT) Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210]) by svr1.postgresql.org (Postfix) with ESMTP id E1443D1D09E for ; Thu, 8 Apr 2004 10:47:13 -0300 (ADT) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 3B82C35243; Thu, 8 Apr 2004 06:46:20 -0700 (PDT) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 39F9635236; Thu, 8 Apr 2004 06:46:20 -0700 (PDT) Date: Thu, 8 Apr 2004 06:46:20 -0700 (PDT) From: Stephan Szabo To: Tony Reina Cc: pgsql-admin@postgresql.org Subject: Re: Database storage bloat In-Reply-To: <272e4be7.0404080115.39f1cb36@posting.google.com> Message-ID: <20040408063838.Q93611@megazone.bigpanda.com> References: <272e4be7.0404080115.39f1cb36@posting.google.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200404/147 X-Sequence-Number: 13120 On Thu, 8 Apr 2004, 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). Well, an important question is where is that space going? It'd be interesting to give a breakup by the directories and then which files (and using the contrib/oid2name to get which table/indexes/etc they are). At least 16MB of that is probably going into the transaction log (IIRC that's the default size for the segments) in pg_xlog.