Re: Vaccum (Was: Re: [HACKERS] Hot Backup Ability)

On Wed, 30 Jun 1999, The Hermit Hacker wrote:

> On Wed, 30 Jun 1999, Bruce Momjian wrote:
> 
> > > Would it be easy to come up with a scheme for the vacuum function defrag a
> > > set number of pages and such, release its locks if there is another
> > > process blocked and waiting, then resume after that process is finished?
> > 
> > That is a very nice idea.  We could just release and reaquire the lock,
> > knowing that if there is someone waiting, they would get the lock. 
> > Maybe someone can comment on this?
> 
> My first thought is "doesn't this still require the 'page-reusing'
> functionality to exist"?  Which virtually eliminates the problem...
> 
> If not, then why can't something be done where this is transparent
> altogther?  Have some sort of mechanism that keeps track of "dead
> space"...a trigger that says after X tuples have been deleted, do an
> automatic vacuum of the database?
> 
> The automatic vacuum would be done in a way similar to Michael's
> suggestion above...scan through for the first 'dead space', lock the table
> for a short period of time and "move records up".  How many tuples could
> you move in a very short period of time, such that it is virtually
> transparent to end-users?
> 
> As a table gets larger and larger, a few 'dead tuples' aren't going to
> make much of a different in performance, so make the threshold some
> percentage of the size of the table, so at it grows, the number of 'dead
> tuples' has to be larger...
> 
> And leave out the truncate at the end...
> 
> The 'manual vacuum' would still need to be run periodically, for the
> truncate and for stats...
> 
> Just a thought...:)

Why not one step further.  Constant background vacuuming.  Do away with
the need to vacuum altogether.  Have something in the backend always 
scanning for dead tuples/dead space and when it finds some it can lock-
move-unlock as it goes.  This way it's not working with a set number or
looking for a certain threshold, just a constant maintenance process.

No?

Vince.
-- 
==========================================================================
Vince Vielhaber -- KA8CSH   email: vev@michvhf.com   flame-mail: /dev/null      # include <std/disclaimers.h>
       TEAM-OS2       Online Campground Directory    http://www.camping-usa.com      Online Giftshop Superstore
http://www.cloudninegifts.com
==========================================================================