From: Scott Marlowe <scott.marlowe@gmail.com>
To: Radhika S <radhika.sambamurti@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Difference between Vacuum and Vacuum full
Date: Tue, 2 Oct 2007 20:55:35 -0500
Message-ID: <dcc563d10710021855l7ac247ebv62c5fc44a321ee1f@mail.gmail.com> (raw)
In-Reply-To: <fe27bfd40710021845r59796586t8f9e672d0f4142d3@mail.gmail.com>
References: <fe27bfd40710021845r59796586t8f9e672d0f4142d3@mail.gmail.com>
On 10/2/07, Radhika S <radhika.sambamurti@gmail.com> 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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: scott.marlowe@gmail.com, radhika.sambamurti@gmail.com
Subject: Re: Difference between Vacuum and Vacuum full
In-Reply-To: <dcc563d10710021855l7ac247ebv62c5fc44a321ee1f@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox