pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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

view thread (5+ messages)

Message-ID: <fe27bfd40710022005t6a7a19c0u19203de612a15c55@mail.gmail.com>
Permalink:  ../fe27bfd40710022005t6a7a19c0u19203de612a15c55@mail.gmail.com/
Also on:    postgresql.org/message-id/fe27bfd40710022005t6a7a19c0u19203de612a15c55@mail.gmail.com

 · 

reply

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