X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 065E656408 for ; Wed, 9 Mar 2005 02:15:39 +0000 (GMT) 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 81286-05 for ; Wed, 9 Mar 2005 02:15:37 +0000 (GMT) Received: from tigger.fuhr.org (tigger.fuhr.org [63.214.45.158]) by svr1.postgresql.org (Postfix) with ESMTP id A5587563F6 for ; Wed, 9 Mar 2005 02:15:35 +0000 (GMT) Received: from winnie.fuhr.org (winnie.fuhr.org [10.1.0.1]) by tigger.fuhr.org (8.13.3/8.13.3) with ESMTP id j292FYNs011649 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK); Tue, 8 Mar 2005 19:15:36 -0700 (MST) (envelope-from mfuhr@winnie.fuhr.org) Received: from winnie.fuhr.org (localhost [127.0.0.1]) by winnie.fuhr.org (8.13.3/8.13.3) with ESMTP id j292FYdu014052; Tue, 8 Mar 2005 19:15:34 -0700 (MST) (envelope-from mfuhr@winnie.fuhr.org) Received: (from mfuhr@localhost) by winnie.fuhr.org (8.13.3/8.13.3/Submit) id j292FXLP014051; Tue, 8 Mar 2005 19:15:33 -0700 (MST) (envelope-from mfuhr) Date: Tue, 8 Mar 2005 19:15:33 -0700 From: Michael Fuhr To: Gaetano Mendola Cc: "pgsql-performance@postgresql.org" Subject: Re: vacuum full, why multiple times ? Message-ID: <20050309021533.GB13883@winnie.fuhr.org> References: <422E4B15.60806@bigfoot.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <422E4B15.60806@bigfoot.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.004 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200503/151 X-Sequence-Number: 10856 On Wed, Mar 09, 2005 at 02:02:13AM +0100, Gaetano Mendola wrote: > Basically I'm noticing that a simple vacuum full is not enough to > shrink completelly the table: > > # vacuum full verbose url; > INFO: vacuuming "public.url" > INFO: "url": found 268392 removable, 21286 nonremovable row versions in 8563 pages > DETAIL: 22 dead row versions cannot be removed yet. How busy is the database? I'd guess that each time you run VACUUM, there are still open transactions that have visibility to the dead rows, so VACUUM doesn't touch them. Those transactions eventually complete, and eventually VACUUM FULL does what you're expecting. I don't know if that's the only possible cause, but I get results similar to yours if I have transactions open when I run VACUUM. -- Michael Fuhr http://www.fuhr.org/~mfuhr/