Re: Vacuum improvement

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