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