Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 0E1D39FB279 for ; Fri, 6 Oct 2006 12:38:01 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 15406-03 for ; Fri, 6 Oct 2006 15:37:55 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey- Received: from webmail.enterprisedb.com (webmail.enterprisedb.com [63.246.7.176]) by postgresql.org (Postfix) with ESMTP id CDEA59FB28B for ; Fri, 6 Oct 2006 12:37:54 -0300 (ADT) Received: from [192.168.42.113] (natpool.bovine.net [67.100.216.14]) by webmail.enterprisedb.com (Postfix) with ESMTP id 786A7A24DCE; Fri, 6 Oct 2006 10:37:53 -0500 (CDT) In-Reply-To: <4526326C.2000009@noaa.gov> References: <45080D4C.2020709@noaa.gov> <20060922170557.GB571@wolff.to> <452531AB.2050709@noaa.gov> <4526326C.2000009@noaa.gov> Mime-Version: 1.0 (Apple Message framework v752.2) Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed Message-Id: <8A3D37C4-8DA1-4ABE-A41E-A02FF03C2149@enterprisedb.com> Cc: Bruno Wolff III , pgsql-admin@postgresql.org, Sean Webb Content-Transfer-Encoding: 7bit From: Jim Nasby Subject: Re: Disk space consumed by pk not returned after vacuum or reindex Date: Fri, 6 Oct 2006 10:37:51 -0500 To: Kevin Johnson X-Mailer: Apple Mail (2.752.2) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200610/46 X-Sequence-Number: 23349 On Oct 6, 2006, at 5:39 AM, Kevin Johnson wrote: > You are correct, it is 7.4 we are running on a RHEL3 system. > The database itself is vacuumed via cron 6 times a day. > > Jim Nasby wrote: >> I didn't see you mention what version you're running; index bloat >> shouldn't be a big issue in 7.4 and above. You also didn't mention >> how often you're vacuuming the table. If you don't vacuum the >> table frequently enough, you're going to get bloat, plain and simple. For a heavy update load, even 6 times a day may not be enough. Also, remember that the size you get after a REINDEX will be unreasonably low, because there will be very little free space in the index. That's fine if you won't be updating the table much, but otherwise the index will have no choice but to grow. -- Jim Nasby jimn@enterprisedb.com EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)