Received: from localhost (unknown [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id C64D22E023A for ; Fri, 4 Jan 2008 13:02:41 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 54079-02 for ; Fri, 4 Jan 2008 13:02:19 -0400 (AST) Received: from ro-out-1112.google.com (ro-out-1112.google.com [72.14.202.181]) by postgresql.org (Postfix) with ESMTP id 380BC2E01D8 for ; Fri, 4 Jan 2008 13:02:36 -0400 (AST) Received: by ro-out-1112.google.com with SMTP id p4so1473381roc.5 for ; Fri, 04 Jan 2008 09:02:35 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; bh=FYtCSw/tWFjymV4de7TW7zzFx1TIBgXTOeLTu2m7pNo=; b=B6Mqzi+iWkHBBu6a2xQRCyw6tvkaJTlQWmC1W7996iEl43fS+rnpE7ECcsXnb/o1gH5rxutVDPp71r6Olr4tw1wHpf2Cs8ZLMNIOVqzoAqhjFIiI2THFhjzMVLbzjJB8oZWbXu5BDWf8MZ7Lrt7uvXrbKdsx1hzFqXgUHwRB1Do= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=hxjPLW58kvZBJYzRqyvmDCXCuf0ltt2oK3/6A0yU4K9+IWvjYoW7g38dh/b8fRRSTdt8uvTgSRiZN1RSzmoJT9xW7rozjxByxLIMt/j7ex3h+QrQXKOyUJk8uKOhKMxFeTZO8kS7tYVfO9CTwqgNwX55YFvSR6YjQ4FaUvrNiUk= Received: by 10.114.60.19 with SMTP id i19mr17047waa.142.1199466153977; Fri, 04 Jan 2008 09:02:33 -0800 (PST) Received: by 10.114.131.4 with HTTP; Fri, 4 Jan 2008 09:02:33 -0800 (PST) Message-ID: Date: Fri, 4 Jan 2008 11:02:33 -0600 From: "Scott Marlowe" To: "James Cloos" Subject: Re: When does VACUUM FULL not clean out all deleted data? Cc: "Tom Lane" , pgsql-admin@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <10656.1199426607@sss.pgh.pa.us> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200801/65 X-Sequence-Number: 27839 On Jan 4, 2008 9:35 AM, James Cloos wrote: > >>>>> "Tom" == Tom Lane writes: > > >> Why was the db using that extra five plus gigs? > > Tom> Smells like a hadn't-been-vacuuming-often-enough problem. > > Sorry, I forgot to mention: autovacuum was enabled and I also ran > this script every night via cron: > > #!/bin/bash > FULL='' > test $(date +%w) -eq 0 && FULL='FULL' > exec psql -U dbm -c "VACUUM ${FULL} VERBOSE ANALYZE" dbm Note that routine vacuum FULL is not a good idea actually. vacuum full is something you run when things have gone wrong (i.e. not enough regular vacuuming) and it is usually best followed by reindexing all your indexes. It's far better to keep track of bloat and run vacuum full, if at all, by hand, and only when needed. Running it regularly with no reindexing often results in index bloat which makes the database slower not faster.