Received: from localhost (unknown [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 729A02E3074 for ; Fri, 4 Jan 2008 01:42:47 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 68797-06-2 for ; Fri, 4 Jan 2008 01:42:41 -0400 (AST) Received: from eagle.jhcloos.com (eagle.jhcloos.com [207.210.242.212]) by postgresql.org (Postfix) with ESMTP id 916A02E307E for ; Fri, 4 Jan 2008 01:42:42 -0400 (AST) Received: by eagle.jhcloos.com (Postfix, from userid 10) id E883040197; Fri, 4 Jan 2008 05:42:39 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=jhcloos.com; s=eagle; t=1199425360; bh=lIrPn4AeTYIcQEeVqzCjKkKN1Teim2z2AJTkcR/lU VM=; h=X-Hashcash:From:To:Subject:User-Agent:Face:Copyright:OpenPGP: OpenPGP-Fingerprint:Date:Message-ID:Lines:MIME-Version: Content-Type; b=kY8gQc+VgU2GVvswC6D3rZvbt2SBxHUWxyhjmJUd1lz5Q6nKVH rQMhJlhPowwHQESjYwUAvh4oPYviiww68SHsZVUPcCrjxqmYz4je2Pk3PXFuqQEstg6 pjPUyKFPUyZhqEHxFcHGTBjrZujJ4F8qwciYDUHR1eqxcNuvZGoU4U= Received: by lugabout.jhcloos.org (Postfix, from userid 500) id 1D18021419E; Fri, 4 Jan 2008 05:42:28 +0000 (UTC) X-Hashcash: 1:23:080104:pgsql-admin@postgresql.org::CeK0DyAWIGb3dzPE:00000000000000000000000000000000000OXts From: James Cloos To: pgsql-admin@postgresql.org Subject: When does VACUUM FULL not clean out all deleted data? 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 Date: Fri, 04 Jan 2008 05:42:27 +0000 Message-ID: Lines: 36 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200801/50 X-Sequence-Number: 27824 Today I did a backup, dump, drop, create and restore on a db which seemed to be taking up too much disk space after a vacuum full. The db was using about six gigs (as backed up) and now uses about two thirds of a gig after the restore (plus many inserts and a few deletes). Right after the restore the db took up less than ten percent as much space as the backup. (For the backup I stopped pg and used rsync on the /var/lib/postgresql/data directory.) Why was the db using that extra five plus gigs? Some more background on the table: Some time back I did alter most of the tables to replace various int8 columns with int4. When I did that the disk space used (after a vacuum full) was reduced almost in half; it didn't appear that anything failed to be reclaimed.... When I did that int8 to int4 change, the table ended up at about 12 or 13 gigs. That was for about 250k to 300k entries, where each entry generates one row in a few tables, two rows (including an auto-toasted bytea column) in one table and usually around 50 rows in another table. The other day, while doing some testing, I had stopped the incoming application and allowed all of the volatile data to expire. So the only data in the db was in the non-volatile tables: about 7k rows in two tables and about 1k in another, plus less than 6 in a couple more. A vacuum full at that point got the table down to five and a half gigs. Which is why I thought to try out a dump & restore, though I didn't actually do so until it had been back in production for a day or two. -JimC -- James Cloos OpenPGP: 1024D/ED7DAEA6