Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 3553A9FBD01 for ; Wed, 16 May 2007 12:48:17 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 11825-03 for ; Wed, 16 May 2007 12:48:07 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from noel.decibel.org (noel.decibel.org [67.100.216.10]) by postgresql.org (Postfix) with ESMTP id 5BE339FBD0C for ; Wed, 16 May 2007 12:48:13 -0300 (ADT) Received: by noel.decibel.org (Postfix, from userid 1001) id BA5A256494; Wed, 16 May 2007 10:48:12 -0500 (CDT) Received: (hashcash-sendmail, from uid 1001); Wed, 16 May 2007 10:48:09 -0500 Date: Wed, 16 May 2007 10:48:09 -0500 From: "Jim C. Nasby" To: Guillaume Cottenceau Cc: Michael Stone , pgsql-performance@postgresql.org Subject: Re: [doc patch] a slight VACUUM / VACUUM FULL doc improvement proposal Message-ID: <20070516154809.GL14548@nasby.net> References: <87lkfqavg9.fsf@meuh.mnc.lan> <20070515174427.GG1785@mathom.us> <87hcqdb4g5.fsf@meuh.mnc.lan> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <87hcqdb4g5.fsf@meuh.mnc.lan> X-Operating-System: FreeBSD 6.1-RELEASE-p10 amd64 X-Distributed: Join the Effort! http://www.distributed.net User-Agent: Mutt/1.5.13 (2006-08-11) X-Hashcash: 1:20:070516:gc@mnc.ch::HHWj5xk9BVur4i5s:000000000ltc X-Hashcash: 1:20:070516:mstone+postgres@mathom.us::s8yF8YSEdQQFx7qn:000000000000 0000000000000000000000001s2B X-Hashcash: 1:20:070516:pgsql-performance@postgresql.org::bakdp/OU6mDWy1EJ:00000 0000000000000000000000007kX4 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200705/286 X-Sequence-Number: 24676 On Wed, May 16, 2007 at 09:41:46AM +0200, Guillaume Cottenceau wrote: > Michael Stone writes: > > > On Tue, May 15, 2007 at 06:43:50PM +0200, Guillaume Cottenceau wrote: > > >patch - basically, I think the documentation under estimates (or > > >sometimes misses) the benefit of VACUUM FULL for scans, and the > > >needs of VACUUM FULL if the routine VACUUM hasn't been done > > >properly since the database was put in production. > > > > It's also possible to overestimate the benefit of vacuum full, leading > > to people vacuum full'ing almost constantly, then complaining about > > performance due to the associated overhead. I think there have been > > more people on this list whose performance problems were caused by > > unnecessary full vacs than by those whose performance problems were > > caused by insufficient full vacs. > > Come on, I don't suggest to remove several bold warnings about > it, the best one being "Therefore, frequently using VACUUM FULL > can have an extremely negative effect on the performance of > concurrent database queries." My point is to add the few > additional mentions; I don't think the claims that VACUUM FULL > physically compacts the data, and might be useful in case of too > long time with infrequent VACUUM are incorrect, are they? Unfortunately they are, to a degree. VACUUM FULL can create a substantial amount of churn in the indexes, resulting in bloated indexes. So often you have to REINDEX after you VACUUM FULL. Long term I think we should ditch 'VACUUM FULL' altogether and create a COMPACT command (it's very easy for users to get confused between "vacuum all the databases in the cluster" or "vacuum the entire database" and "VACUUM FULL"). -- Jim Nasby decibel@decibel.org EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)