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 6F197D1D283 for ; Thu, 8 Apr 2004 14:26:32 -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 25211-08 for ; Thu, 8 Apr 2004 14:26:29 -0300 (ADT) Received: from polaris.pinpointresearch.com (66-7-238-130.cust.telepacific.net [66.7.238.130]) by svr1.postgresql.org (Postfix) with ESMTP id 1363ED1D282 for ; Thu, 8 Apr 2004 14:26:27 -0300 (ADT) Received: from betelgeuse.pinpointresearch.com (betelgeuse.pinpointresearch.com [192.168.1.179]) by polaris.pinpointresearch.com (Postfix) with ESMTP id 77A831040F; Thu, 8 Apr 2004 10:26:26 -0700 (PDT) From: Steve Crawford Organization: Pinpoint Research To: "Tony and Bryn Reina" , "Uwe C. Schroeder" , Subject: Re: Database storage bloat Date: Thu, 8 Apr 2004 10:26:22 -0700 User-Agent: KMail/1.5.1 References: <272e4be7.0404080115.39f1cb36@posting.google.com> <200404080257.46102.uwe@oss4u.com> In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200404081026.22195.scrawford@pinpointresearch.com> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200404/158 X-Sequence-Number: 13131 On Thursday 08 April 2004 5:51 am, Tony and Bryn Reina wrote: > Yep. That's after a 'vacuum verbose analyze'. No, he asked if you had run a "vacuum full". A "standard" vacuum just marks space available for reuse - it does not shrink file sizes. A "vacuum full" will shrink the files on disk. Are you doing many updates on the table that is the space-using culprit? Each record that is updated is created anew. The old record is marked as "defunct" when no remaining transactions need that record. If you were to, say, update the entire table to change a single character field you would basically double the size of the table. A vacuum will mark the space that is no longer used as available for reuse so if you run regular vacuums you should achieve a fairly static size. One thing to consider: since this is how PostgreSQL achieves MVCC you may want to try to avoid updates to a table that has huge record sizes. For example, say you wanted to store a "last viewed date" for each file. If you had that date in the same table with your data every view would add considerable "bloat" to your table. If, instead, your file is in a table by itself along with a sequence or other unique key then the "last viewed date" or other changing data could be put into a separate table linked to the file storage table by that unique ID. Updating the date or other info about the file would only increase the bloat in the other table and the bloat would be small. Performance would probably improve as well due to smaller file sizes and less read/write action on the disks. Cheers, Steve