Re: Vacuum improvement - Mailing list pgsql-hackers

But doesn't the solution I offer present a possible work around?  The
table wouldn't need to be locked (I think) until the first dead tuple
were located.  After that, you would only keep the locks until you've
scanned X% of the table and shrunk as needed.  The result, I think,
results in incremental vacuuming with shorter duration locks being
held.  It's not ideal (locks) but may shorten the duration behind help
by locks.

I'm trying to figure out if the two approaches can't be combined
somehow.  That is, a percent with maybe even a max lock duration?

Greg



On Wed, 2002-10-16 at 11:33, David Walker wrote:
> 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 majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly