X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 1B1655E46CC for ; Fri, 13 Aug 2004 14:36:30 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 28686-01 for ; Fri, 13 Aug 2004 17:36:21 +0000 (GMT) Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210]) by svr1.postgresql.org (Postfix) with ESMTP id 2F82A5E46CB for ; Fri, 13 Aug 2004 14:36:21 -0300 (ADT) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 8FAEE35537; Fri, 13 Aug 2004 10:36:20 -0700 (PDT) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 8E40935532; Fri, 13 Aug 2004 10:36:20 -0700 (PDT) Date: Fri, 13 Aug 2004 10:36:20 -0700 (PDT) From: Stephan Szabo To: Thomas Madsen Cc: pgsql-admin@postgresql.org Subject: Re: TOAST tables keeps growing! In-Reply-To: <64F70386AE9AB64D8F36DD6FC355555E570F32@hal9000.softcom.local> Message-ID: <20040813103247.D4124@megazone.bigpanda.com> References: <64F70386AE9AB64D8F36DD6FC355555E570F32@hal9000.softcom.local> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200408/108 X-Sequence-Number: 14503 On Thu, 12 Aug 2004, Thomas Madsen wrote: > I'm running a production server with PostgreSQL version 7.2.1, that does You really should upgrade. If not to 7.4.x, at least to the last 7.2 release. > The problem is that TOAST tables keeps springing forth and consume disk > space. > We do a VACUUM every night, but it does not reduce or stop the TOAST > table growth. > > I searched the mailing list for answers to this problem, but found only > other people describing the same problem. > Namely, that their max_fsm_pages were too low and that postgresql now > has lost track of the surplus TOAST data pages. > > In our postgresql.conf we ourselves had the default value of 10000 for > max_fsm_pages, which I have now raised to around a million. > I restarted psql and did a vacuum to see if that would reduce the disk > usage, but no change was seen. Generally, raising fsm and revacuuming (without full) will mean that more of the space will get re-used so that it should stop growing from day to day, but it's not really going to remove space already taken (excepting blank pages at the end if it can get appropriate locks I think). Vacuum Full should remove the blank space at the cost of an exclusive lock on the table.