Skip site navigation (1) Skip section navigation (2)

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

From: Vince Vielhaber <vev(at)michvhf(dot)com>
To: The Hermit Hacker <scrappy(at)hub(dot)org>
Cc: Bruce Momjian <maillist(at)candle(dot)pha(dot)pa(dot)us>, Michael Richards <miker(at)scifair(dot)acadiau(dot)ca>, pgsql-hackers(at)postgreSQL(dot)org
Subject: Re: Vaccum (Was: Re: [HACKERS] Hot Backup Ability)
Date: 1999-06-30 14:27:35
Message-ID: Pine.BSF.4.05.9906301024020.4581-100000@paprika.michvhf.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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
==========================================================================




In response to

pgsql-hackers by date

Next:From: Michael RichardsDate: 1999-06-30 14:28:43
Subject: Re: [HACKERS] Getting number of tuples affected
Previous:From: Peter MountDate: 1999-06-30 14:02:11
Subject: RE: Vaccum (Was: Re: [HACKERS] Hot Backup Ability)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group