Vacuum full locks the whole table currently. I was thinking if you used a similar to a hard drive defragment that only 2 rows would need to be locked at a time. When you're done vacuum/defragmenting you shorten the file to discard the dead tuples that are located after your useful data. There might be a need to lock the table for a little while at the end but it seems like you could reduce that time greatly.
I had one table that is heavily updated and it grew to 760 MB even with regular vacuuming. A vacuum full reduced it to 1.1 MB. I am running 7.2.0 (all my vacuuming is done by superuser). On Wednesday 16 October 2002 09:30 am, (Via wrote: > On Wed, 2002-10-16 at 02:29, Gavin Sherry wrote: > > On 16 Oct 2002, Hannu Krosing wrote: > > > On Wed, 2002-10-16 at 05:22, Gavin Sherry wrote: > > > > Hi all, > > > > > > > > I'm thinking that there is an improvement to vacuum which could be > > > > made for 7.4. VACUUM FULLing large, heavily updated tables is a pain. > > > > There's very little an application can do to minimise dead-tuples, > > > > particularly if the table is randomly updated. Wouldn't it be > > > > beneficial if VACUUM could have a parameter which specified how much > > > > of the table is vacuumed. That is, you could specify: > > > > > > > > VACUUM FULL test 20 precent; > > > > > > What about > > > > > > VACUUM FULL test WORK 5 SLEEP 50; > > > > > > meaning to VACUUM FULL the whole table, but to work in small chunks and > > > relaese all locks and let others access the tables between these ? > > > > Great idea. I think this could work as a complement to the idea I had. To > > answer Tom's question, how would we know what we've vacuumed, we could > > store the range of tids we've vacuumed in pg_class. Or, we could store > > the block offset of where we left off vacuuming before and using stats, > > run for another X% of the heap. Is this possible? > > Why couldn't you start your % from the first rotten/dead tuple? Just > reading through trying to find the first tuple to start counting from > wouldn't hold locks would it? That keeps you from having to track stats > and ensures that X% of the tuples will be vacuumed. > > Greg ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
