Re: VACUUM optimization ideas.

> Alfred Perlstein wrote:
> > #1
> > 
> > Reducing the time vacuum must hold an exlusive lock on a table:
> > 
> > The idea is that since rows are marked deleted it's ok for the
> > vacuum to fill them with data from the tail of the table as
> > long as no transaction is in progress that has started before
> > the row was deleted.
> > 
> > This may allow the vacuum process to copyback all the data without
> > a lock, when all the copying is done it then aquires an exlusive lock
> > and does this:
> > 
> > Aquire an exclusive lock.
> > Walk all the deleted data marking it as current.
> > Truncate the table.
> > Release the lock.
> > 
> > Since the data is still marked invalid (right?) even if valid data
> > is copied into the space it should be ignored as long as there's no
> > transaction occurring that started before the data was invalidated.
> 
> Yes, but nothing prevents newer transactions from modifying the _origin_ side of
> the copied data _after_ it was copied, but before the Lock-Walk-Truncate-Unlock
> cycle takes place, and so it seems unsafe. Maybe locking each record before
> copying it up ...

Seems a read-lock would be necessary during the moving, but still a win.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026