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
