Auto Vacuum Daemon (again...)

Several months ago tried to implement a special postgres backend as an
Auto Vacuum Daemon (AVD), somewhat like the stats collector. I failed
due to my lack of experience with the postgres source.

On Sep 23, Shridhar Daithankar released an AVD written in C++ that acted
as a client program rather than part of the backend. I rewrote it in C,
and have been playing with it ever since. At this point I need feedback
and direction from the hacker group.

First: Do we want AVD integrated into the main source tree, or should it
remain a "tool" that can be downloaded from gborg. I would think it
should be controlled by the postmaster, and configured from GUC (at
least basic on off settings)

Second: Assuming we want it integrated into the source tree, can it
remain a client app? Can a non backend program that connects to the
postmaster using libpq be a child of the postmaster that the postmaster
can control (start and stop).

Third: If a special backend version is preferred, I don't personally
know how to have a backend monitor and vacuum multiple databases. I
guess it could be similar to the client app and fire up new back
everytime a database needs to be vacuumed.

Fourth: I think AVD is a feature that is needed in some form or
fashion. I am willing to work on it, but if it needs to be a backend
version I will probably need some help.

Anyway for you reading pleasure, I have attached a plot of results from
a simple test program I wrote. As you can see from the plot, AVD keeps
the file size under control. Also, the first few Xacts are faster in
the non AVD case, but after that AVD keeps the average Xact time down.
The periodic spikes in the AVD run correspond to when the AVD has fired
off a vacuum. Also when the table file gets to approx 450MB performance
drops off horribly I assume this is because my system can no longer
cache the whole file (I have 512M in my machine). Also, I had been
developing against 7.2.3 until recently, and I wound up doing some of
these benchmarks against both 7.2.3 and 7.3devel and 7.3 perfoms much
better, that is it 7.2 slowed down much sooner under this test.

Thanks,

Matthew

ps, The test program performs the following:

create table pgavdtest_table (id int,num numeric(10,2),txt char(512))

while i<1000
insert into pgavdtest_table (id,num,txt) values (i,i.i,'string i')

while i<1000
update pgavdtest_table set num=num+i, txt='update string %i'

pps, I can post the source (both the AVD and the test progam) to the
list, or email it to individuals if they would like.