Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 0AD292E3E56 for ; Fri, 4 Jan 2008 11:57:44 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 24674-03-3 for ; Fri, 4 Jan 2008 11:57:29 -0400 (AST) Received: from eagle.jhcloos.com (eagle.jhcloos.com [207.210.242.212]) by postgresql.org (Postfix) with ESMTP id D29C42E3C56 for ; Fri, 4 Jan 2008 11:36:14 -0400 (AST) Received: by eagle.jhcloos.com (Postfix, from userid 10) id 42BD640197; Fri, 4 Jan 2008 15:36:11 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=jhcloos.com; s=eagle; t=1199460971; bh=DKcauxpwmQ6B8wS0uaWF/pHC27rWWVgdKagmPyaMj 7A=; h=From:To:Cc:Subject:In-Reply-To:References:User-Agent:Face: Copyright:OpenPGP:OpenPGP-Fingerprint:X-Hashcash:X-Hashcash:Date: Message-ID:Lines:MIME-Version:Content-Type; b=zuWhY9/+EPELk+U9jr3b bOsnMp5/j8vOeFAjAmFBmKlWOP1atFy5ghmH9C6/AWjHcXj/rueBqOfVfqVUmPLsS78 TzWQrmJiTyaD2dprAtK6UDXrqyDJcwtLIrpzNk/ZWjTiYB843NvmGTZ/TCI1Z0ViSjq 8LfnWiVbNUe6/phkg= Received: by lugabout.jhcloos.org (Postfix, from userid 500) id 4173F214492; Fri, 4 Jan 2008 15:36:00 +0000 (UTC) From: James Cloos To: Tom Lane Cc: pgsql-admin@postgresql.org Subject: Re: When does VACUUM FULL not clean out all deleted data? In-Reply-To: <10656.1199426607@sss.pgh.pa.us> (Tom Lane's message of "Fri, 04 Jan 2008 01:03:27 -0500") References: <10656.1199426607@sss.pgh.pa.us> User-Agent: Gnus/5.110007 (No Gnus v0.7) Emacs/23.0.60 (gnu/linux) Face: iVBORw0KGgoAAAANSUhEUgAAABAAAAAQCAYAAAAf8/9hAAAABHNCSVQICAgIfAhkiAAAAI1J REFUOE+lU9ESgCAIg64P1y+ngUdxhl5H8wFbbM0OmUiEhKkCYaZThXCo6KE5sCbA1DDX3genvO4d eBQgEMaM5qy6uWk4SfBYfdu9jvBN9nSVDOKRtwb+I3epboOsOX5pZbJNsBJFvmQQ05YMfieIBnYX FK2N6dOawd97r/e8RjkTLzmMsiVgrAoEugtviCM3v2WzjgAAAABJRU5ErkJggg== Copyright: Copyright 2007 James Cloos OpenPGP: ED7DAEA6; url=http://jhcloos.com/public_key/0xED7DAEA6.asc OpenPGP-Fingerprint: E9E9 F828 61A4 6EA9 0F2B 63E7 997A 9F17 ED7D AEA6 X-Hashcash: 1:23:080104:tgl@sss.pgh.pa.us::WD6+iAe6gV13PWfE:000000000000000000000000000000000000000000009Mx2 X-Hashcash: 1:23:080104:pgsql-admin@postgresql.org::uXzaQmEzf3vKQXA8:00000000000000000000000000000000000KTHt Date: Fri, 04 Jan 2008 15:35:59 +0000 Message-ID: Lines: 21 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200801/56 X-Sequence-Number: 27830 >>>>> "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 The app at least felt faster with the daily vacuum, even though autovacuum was running. I don't have a good explanation for that, either. -JimC -- James Cloos OpenPGP: 1024D/ED7DAEA6