From: Radhika S <radhika.sambamurti@gmail.com>
To: Scott Marlowe <scott.marlowe@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Difference between Vacuum and Vacuum full
Date: Tue, 2 Oct 2007 23:05:14 -0400
Message-ID: <fe27bfd40710022005t6a7a19c0u19203de612a15c55@mail.gmail.com> (raw)
In-Reply-To: <dcc563d10710021855l7ac247ebv62c5fc44a321ee1f@mail.gmail.com>
References: <fe27bfd40710021845r59796586t8f9e672d0f4142d3@mail.gmail.com>
<dcc563d10710021855l7ac247ebv62c5fc44a321ee1f@mail.gmail.com>
Thank you much for such a precise explanation. That was very helpful.
Regards,
Radhika
On 10/2/07, Scott Marlowe <scott.marlowe@gmail.com> wrote:
>
> 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.
>
--
It is all a matter of perspective. You choose your view by choosing where to
stand. --Larry Wall
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: radhika.sambamurti@gmail.com, scott.marlowe@gmail.com
Subject: Re: Difference between Vacuum and Vacuum full
In-Reply-To: <fe27bfd40710022005t6a7a19c0u19203de612a15c55@mail.gmail.com>
References: <fe27bfd40710021845r59796586t8f9e672d0f4142d3@mail.gmail.com> <dcc563d10710021855l7ac247ebv62c5fc44a321ee1f@mail.gmail.com> <fe27bfd40710022005t6a7a19c0u19203de612a15c55@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