Autovacuum help..

Hi,

I need your help/suggestions with a problem I am facing related to autovacuum.

I am using PostgreSQL 8.1.2 through a JDBC connection. The connection is
long lived (established when the application starts up and is closed only when
the application is shutdown).

I have enabled the autovacuum daemon and setup additional parameters (for
instance, stats_row_level=on) as specified in the PostgreSQL documentation.

In the database, I have a table that has a fairly high rate of inserts and
deletes (~10 rows a second). The table is basically a FIFO queue that can have
a maximum of 800 entries. As new rows are added to the table, oldest rows are
deleted such that the table always about 800 rows.

The problem I see is that the database size (disk usage) is continually
increasing even though I have the autovacuum daemon enabled and the PostgreSQL
log file indicates that the autovacuum daemon is processing the databases
every minute as expected.

On digging in further, I noticed that the reltuples (in pg_catalog.pg_class)
for the relevant table keeps increasing continually. I also noticed a large
number of dead unremovable rows when I ran the vacuum analyze command.

After shutting down my application, if I watch the reltuples, it continues to
stay high until I run the analyze command (analyze verbose <table_name>) after
which the reltuples drops to about 800 immediately. The analyze command output
also indicates that the dead rows have been removed and I notice that the space
is reused for future inserts when I restart the application.

I am pretty sure that I don't have any transaction that is held open forever
(the work flow is insert, commit, insert commit etc).

My question is, is autovacuum expected to work in situations like this where I
have a long lived connection to the database ? After I shutdown my application,

why am required to run the "analyze" command before the dead rows are removed
(autovacuum is not able to remove the dead rows until I run the "analyze"
command) ?

I'll appreciate your thoughts since I seem to be running out of things to try..

Thanks

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com