Thank you much for such a precise explanation. That was very helpful. Regards, Radhika
On 10/2/07, Scott Marlowe <[EMAIL PROTECTED]> wrote: > > On 10/2/07, Radhika S <[EMAIL PROTECTED]> 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
