pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedDifference between Vacuum and Vacuum full
5+ messages / 4 participants
[nested] [flat]
* Difference between Vacuum and Vacuum full
@ 2007-10-03 01:45 Radhika S <radhika.sambamurti@gmail.com>
2007-10-03 01:55 ` Re: Difference between Vacuum and Vacuum full Scott Marlowe <scott.marlowe@gmail.com>
2007-10-03 01:56 ` Re: Difference between Vacuum and Vacuum full Rodrigo De León <rdeleonp@gmail.com>
2007-10-03 02:02 ` Re: Difference between Vacuum and Vacuum full D'Arcy J.M. Cain <darcy@druid.net>
0 siblings, 3 replies; 5+ messages in thread
From: Radhika S @ 2007-10-03 01:45 UTC (permalink / raw)
To: pgsql-performance
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.
Thank you.
Radhika
--
It is all a matter of perspective. You choose your view by choosing
where to stand. --Larry Wall
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Difference between Vacuum and Vacuum full
2007-10-03 01:45 Difference between Vacuum and Vacuum full Radhika S <radhika.sambamurti@gmail.com>
@ 2007-10-03 01:55 ` Scott Marlowe <scott.marlowe@gmail.com>
2007-10-03 03:05 ` Re: Difference between Vacuum and Vacuum full Radhika S <radhika.sambamurti@gmail.com>
2 siblings, 1 reply; 5+ messages in thread
From: Scott Marlowe @ 2007-10-03 01:55 UTC (permalink / raw)
To: Radhika S <radhika.sambamurti@gmail.com>; +Cc: pgsql-performance
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Difference between Vacuum and Vacuum full
2007-10-03 01:45 Difference between Vacuum and Vacuum full Radhika S <radhika.sambamurti@gmail.com>
2007-10-03 01:55 ` Re: Difference between Vacuum and Vacuum full Scott Marlowe <scott.marlowe@gmail.com>
@ 2007-10-03 03:05 ` Radhika S <radhika.sambamurti@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Radhika S @ 2007-10-03 03:05 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Difference between Vacuum and Vacuum full
2007-10-03 01:45 Difference between Vacuum and Vacuum full Radhika S <radhika.sambamurti@gmail.com>
@ 2007-10-03 01:56 ` Rodrigo De León <rdeleonp@gmail.com>
2 siblings, 0 replies; 5+ messages in thread
From: Rodrigo De León @ 2007-10-03 01:56 UTC (permalink / raw)
To: Radhika S <radhika.sambamurti@gmail.com>; +Cc: pgsql-performance
On 10/2/07, Radhika S <radhika.sambamurti@gmail.com> wrote:
> ... why have vacuum full...
See:
http://www.postgresql.org/docs/8.2/static/routine-vacuuming.html
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Difference between Vacuum and Vacuum full
2007-10-03 01:45 Difference between Vacuum and Vacuum full Radhika S <radhika.sambamurti@gmail.com>
@ 2007-10-03 02:02 ` D'Arcy J.M. Cain <darcy@druid.net>
2 siblings, 0 replies; 5+ messages in thread
From: D'Arcy J.M. Cain @ 2007-10-03 02:02 UTC (permalink / raw)
To: Radhika S <radhika.sambamurti@gmail.com>; +Cc: pgsql-performance
On Tue, 2 Oct 2007 21:45:37 -0400
"Radhika S" <radhika.sambamurti@gmail.com> wrote:
> 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.
Not quite. "VACUUM FULL" returns space to the system. "VACUUM" only
frees the space for use by the database. In most cases a simple VACUUM
is all you need since you are going to just be asking for the space
back anyway eventually as your database grows.
--
D'Arcy J.M. Cain <darcy@druid.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2007-10-03 03:05 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-10-03 01:45 Difference between Vacuum and Vacuum full Radhika S <radhika.sambamurti@gmail.com>
2007-10-03 01:55 ` Scott Marlowe <scott.marlowe@gmail.com>
2007-10-03 03:05 ` Radhika S <radhika.sambamurti@gmail.com>
2007-10-03 01:56 ` Rodrigo De León <rdeleonp@gmail.com>
2007-10-03 02:02 ` D'Arcy J.M. Cain <darcy@druid.net>
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