Re: Difference between Vacuum and Vacuum full

Thank you much for such a precise explanation. That was very helpful.

Regards,
Radhika

On 10/2/07, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> wrote:
>
> On 10/2/07, Radhika S <radhika(dot)sambamurti(at)gmail(dot)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