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(at)michvhf(dot)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
==========================================================================