Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id B26BE9F9AB2 for ; Tue, 2 Oct 2007 22:55:42 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 34380-08 for ; Tue, 2 Oct 2007 22:55:29 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.172]) by postgresql.org (Postfix) with ESMTP id A87B79F9A6D for ; Tue, 2 Oct 2007 22:55:38 -0300 (ADT) Received: by ug-out-1314.google.com with SMTP id j3so43420ugf for ; Tue, 02 Oct 2007 18:55:36 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=beta; 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=U1XVZXJ/u2+dO4MvbCq3jN626svxse3ShSEdizT9yXw=; b=GRJAoONxvvfePoEIvnQwwauai5x5uUDFRza0va+IilCOJ0+oibY0n1IGiccEd0V8IW/DPmmsP+sCEMJp8RtMrvsQ9urmZaZrIRIkbxzBa3C82vxOK+vZvZ1DvAYiWHitmikhEMDlXQD2ZZWnTOHijmS5qy1XCVMVgeCvbpfA98Y= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=iTaq8Tbvzl8FP44HTHdN5eTveF0RRLsmtGUgZXsyZZ6y7PusuJchdclSejBAWnxv2ov61iuo6vIxDc9uV7GtdJm8iuRQhlC1PuTGcun8HG9gTaltGLQYwYtI1dqsCSMKZUrm3GxGKInUuijPLiHKdS0gM6rfBqRU9NY3fIwG3Jc= Received: by 10.67.29.12 with SMTP id g12mr1877022ugj.1191376535702; Tue, 02 Oct 2007 18:55:35 -0700 (PDT) Received: by 10.66.220.7 with HTTP; Tue, 2 Oct 2007 18:55:35 -0700 (PDT) Message-ID: Date: Tue, 2 Oct 2007 20:55:35 -0500 From: "Scott Marlowe" To: "Radhika S" Subject: Re: Difference between Vacuum and Vacuum full Cc: pgsql-performance@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: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200710/14 X-Sequence-Number: 27036 On 10/2/07, Radhika S wrote: > Hi, > > I have recently had to change our nightly jobs from running vacuum > full, as it has caused problems for us. Upon doing more reading on > this topic, I understand that vacuum full needs explicit locks on the > entire db and explicit locking conflicts with all other locks. > > But this has bought me to the question of what exactly is the > difference between vacuum and vacuum full. If both give back free > space to the disk, then why have vacuum full. Vacuum analyzes the tables and indexes, and marks deleted entries as free and available and puts and entry into the free space map for them. The next time that table or index is updated, instead of appending the new tuple to the end it can be placed in the middle of the table / index. this allows the database to reuse "empty" space in the database. Also, if there are dead tuples on the very end of the table or index, it can truncate the end of the file and free that space up. Vaccum full basically re-writes the whole file minus all the dead tuples, which requires it to lock the table while it is doing so. Generally speaking, regular vacuum is preferable. Vacuum full should only be used to recover lost space due to too infrequent regular vacuums or too small of a free space map. vacuum full is much more invasive and should be avoided unless absolutely necessary.