> > I still think, that for best results the vacuums should happen continuously
> > for single pages based on a hook in wal or the buffer manager.
> 
> Not possible unless you are willing to have SELECTs grab much stronger
> locks than they do now (viz, the same kind of lock that VACUUM does).

I am talking about slots, that are marked deleted before oldest tx in progress.
It should be possible to overwrite those only with the "pin" on the page.
A pageread for a select does wait (spinlock) while a new txinfo is written,
this is necessary since the txinfo is more than one byte, no ?
It should be more or less the same situation like using a slot from the 
freelist, no ?
Update and delete would need to check the "old page" for %free and add it to 
the freelist, like vacuum does.

This would avoid the (imho large) overhead vacuum imposes of reading static 
pages that have not been modified in ages.

Andreas

Thu, 7 Mar 2002 08:30:48 +0100
"Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at>
Re: Postgresql backend to perform vacuum automatically 

If I may pipe in to this discussion, I have some experience with this.
With threaded postgres, the thread that is reponsible for writing out
buffer pages keeps track of the number of writes on a particular relation
(implied update, insert, or delete but not always).  That is multiplied by
a tolerance factor and accumulated until a limit is reached.  After much
trial and error I came up with this formula for the tolerance factor.

live_tuple_count of previous vacuum * 0.01 
+
dead_tuple_count of previous vacuum * 0.1
+
100 to force vacuums on small relations

all divided by live_tuple_count + 1

with bounds on the change in tolerance factor from 
the last vacuum (3.0x max increase or 80% max decrease)

Lastly, the second phase of vacuum_lazy only gets triggered 
if more than 10% of the relation is dead.

All this seems to keep relations pretty trim without getting
in the way.  Tables that have a large ratio of dead to live tuples 
get vacuumed more frequently which actually helps performance by clearing 
out old tuples which don't have to be scanned by other threads doing
updates.

Myron Scott
mkscott@sacadia.com

On Thu, 7 Mar 2002, Zeugswetter Andreas SB SD wrote:

> > > I still think, that for best results the vacuums should happen continuously
> > > for single pages based on a hook in wal or the buffer manager.
> > 
> > Not possible unless you are willing to have SELECTs grab much stronger
> > locks than they do now (viz, the same kind of lock that VACUUM does).
> 
> I am talking about slots, that are marked deleted before oldest tx in progress.
> It should be possible to overwrite those only with the "pin" on the page.
> A pageread for a select does wait (spinlock) while a new txinfo is written,
> this is necessary since the txinfo is more than one byte, no ?
> It should be more or less the same situation like using a slot from the 
> freelist, no ?
> Update and delete would need to check the "old page" for %free and add it to 
> the freelist, like vacuum does.
> 
> This would avoid the (imho large) overhead vacuum imposes of reading static 
> pages that have not been modified in ages.
> 
> Andreas
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
> http://www.postgresql.org/users-lounge/docs/faq.html
> 


Thu, 7 Mar 2002 00:51:22 -0800 (PST)
<mkscott@sacadia.com>
Re: Postgresql backend to perform vacuum automatically 
"Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at> writes:
>> Not possible unless you are willing to have SELECTs grab much stronger
>> locks than they do now (viz, the same kind of lock that VACUUM does).

> I am talking about slots, that are marked deleted before oldest tx in progress.
> It should be possible to overwrite those only with the "pin" on the page.

No, it is not.

You can't physically remove a tuple before you've removed all index
entries that point to it.  That requires, at minimum, a table lock that
ensures that no new indexes are being created meanwhile.  Which is what
VACUUM uses.

There's also a serious question about whether this would really improve
performance.  "Retail" deletion of index tuples would be fairly
inefficient over the long run, compared to the bulk deletion technique
used by VACUUM.

			regards, tom lane

Thu, 07 Mar 2002 10:01:40 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Postgresql backend to perform vacuum automatically 