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 EB2C0D1D174 for ; Fri, 9 Apr 2004 02:09:09 -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 50193-05 for ; Fri, 9 Apr 2004 02:09:07 -0300 (ADT) Received: from hotmail.com (bay8-dav64.bay8.hotmail.com [64.4.26.199]) by svr1.postgresql.org (Postfix) with ESMTP id B840CD1D105 for ; Fri, 9 Apr 2004 02:09:07 -0300 (ADT) Received: from mail pickup service by hotmail.com with Microsoft SMTPSVC; Thu, 8 Apr 2004 22:09:07 -0700 Received: from 80.21.203.111 by bay8-dav64.bay8.hotmail.com with DAV; Fri, 09 Apr 2004 05:09:07 +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: "Tom Lane" Cc: "Stephan Szabo" , References: <272e4be7.0404080115.39f1cb36@posting.google.com> <20040408063838.Q93611@megazone.bigpanda.com> <15732.1081446563@sss.pgh.pa.us> Subject: Re: Database storage bloat Date: Fri, 9 Apr 2004 07:09:11 +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: 09 Apr 2004 05:09:07.0729 (UTC) FILETIME=[C9261810:01C41DF0] X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200404/174 X-Sequence-Number: 13147 > I don't see orders-of-magnitude bloat here though. You've got 16 bytes > of useful data per row (which I suppose was 12 bytes in the flat file?). > There will be 28 bytes of overhead per table row. In addition the index > will require 12 data bytes + 12 overhead bytes per entry; allowing for > the fact that b-tree only likes to pack pages about 2/3ds full, we could > estimate index size as about 36 bytes per original row, giving an > aggregate bloat factor of 6.67X compared to a binary flat file if the > flat file needed 12 bytes per row. > > The only way I could see to get to a 65X bloat factor would be if you'd > repeatedly updated the table rows without vacuuming. Thanks Tom (and everyone else). At least I feel more comfortable that there's not something obvious. I did perform a 'vacuum full', but still no dice. At least thanks to Stephan I've narrowed my search down to one table and I have a ballpark theoretical bloat to shoot for (6 .67X). I'm going to go back and try to determine if I'm correctly interpretting the part of the flat file that holds this table's data. -Tony