Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 70CEA9FA6B0 for ; Sat, 6 Jan 2007 15:33:22 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-new, port 10024) with ESMTP id 73759-09 for ; Sat, 6 Jan 2007 15:33:15 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from lynet.oppetid.no (oppetid.no [80.65.51.2]) by postgresql.org (Postfix) with ESMTP id 997FF9FA31B for ; Sat, 6 Jan 2007 15:33:17 -0400 (AST) Received: by lynet.oppetid.no (Postfix, from userid 1019) id 6D3C611A748; Sat, 6 Jan 2007 20:33:13 +0100 (CET) Date: Sat, 6 Jan 2007 20:33:13 +0100 From: Tobias Brox To: Daryl Herzmann Cc: pgsql-performance@postgresql.org Subject: Re: Missing the point of autovacuum Message-ID: <20070106193313.GB30486@oppetid.no> References: Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: Organization: Group Nordicbet User-Agent: Mutt/1.5.9i X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200701/78 X-Sequence-Number: 22679 [Daryl Herzmann - Sat at 12:59:03PM -0600] > As the months have gone by, I notice many of my tables having *lots* of > unused item pointers. For example, Probably not the issue here, but we had some similar issue where we had many long-running transactions - i.e. some careless colleague entering "begin" into his psql shell and leaving it running for some days without entering "commit" or "rollback", plus some instances where the applications started a transaction without closing it. > Perhaps I shouldn't be concerned with this? In all, I've got around 400 > GB of data on postgresql, but am not sure how much of it is old data. I didn't count the zeroes, but autovacuum does have rules saying it will not touch the table until some percentages of it needs to be vacuumed off. This is of course configurable. > Do I need to be running old fashioned 'vacuumdb' routinely as well? I > guess I just don't understand why autovacuum is not automatically doing > this for me and I have tables with so many unused item pointers. If you have some period of the day with less activity than else, it is a good idea running an old-fashionated vacuum as well. The regular vacuum process will benefit from any work done by the autovacuum.