Re: [HACKERS] Autovacuum Improvements

On Sun, 2007-01-21 at 14:26 -0600, Jim C. Nasby wrote:
> On Sun, Jan 21, 2007 at 11:39:45AM +0000, Heikki Linnakangas wrote:
> > Russell Smith wrote:
> > >Strange idea that I haven't researched,  Given Vacuum can't be run in a 
> > >transaction, it is possible at a certain point to quit the current 
> > >transaction and start another one.  There has been much chat and now a 
> > >TODO item about allowing multiple vacuums to not starve small tables.  
> > >But if a big table has a long running vacuum the vacuum of the small 
> > >table won't be effective anyway will it?  If vacuum of a big table was 
> > >done in multiple transactions you could reduce the effect of long 
> > >running vacuum.  I'm not sure how this effects the rest of the system 
> > >thought.
> > 
> > That was fixed by Hannu Krosing's patch in 8.2 that made vacuum to 
> > ignore other vacuums in the oldest xmin calculation.
> 
> And IIRC in 8.1 every time vacuum finishes a pass over the indexes it
> will commit and start a new transaction. 

err...It doesn't do this now and IIRC didn't do that in 8.1 either.

> That's still useful even with
> Hannu's patch in case you start a vacuum with maintenance_work_mem too
> small; you can abort the vacuum some time later and at least some of the
> work it's done will get committed.

True, but not recommended, though for a variety of reasons.

The reason is not intermediate commits, but just that the work of VACUUM
is mostly non-transactional in nature, apart from the various catalog
entries when it completes.

-- 
  Simon Riggs             
  EnterpriseDB   http://www.enterprisedb.com