very delayed autovacuum on certain tables

[Stuart Brooks <stuartb(at)cat(dot)co(dot)za>]

I am experiencing a strange problem where autovacuum appears to be 
vacuuming 1 table in preference to another even through they have very 
similar usage patterns.

For this test case I have 2 tables, 'transactions' and 'lineitems', and 
the ratio of writes is approx 1:3. I am filling these tables as fast as 
possible and once I reach approx 1'000'000 transactions (3mil lineitems) 
they are both periodically trimmed to keep their sizes constant.

The transactions table gets autovacuumed periodically, roughly when I 
would expect, but the autovacuum only seems to run on the lineitems 
table well after the trigger point (I am using the autovacuum defaults 
in postgresql.conf). According to pg_stat_user_tables there were 1.7m 
dead rows and 3.1m live rows when it decided to run the vacuum.

I have been logging data every minute from pg_stat_user_tables 
(n_tup_ins,n_live_tup,n_dead_tup,pg_total_relation_size()) for each of 
the tables and it makes interesting reading. For one thing, the number 
of dead tuples drops every now and again without the vacuum being run 
(is it possible that a vacuum is starting and then being terminated 
before completing?) and also the size of the lineitems table continues 
increasing where the transactions table levels off as expected.

I was wondering if there is any way I can get more logging information 
about the autovacuum decision making to find out exactly what is 
happening? I also read that the stats are not always accurate under 
high-load and was wondering if this could be affecting the vacuum.

Thanks
Stuart

PS. Running 8.3.1 on NetBSD 3.
PS2. I have attached the postgresql log and the data log (tab-separated).
PS3. I am not (to my knowledge) doing anything other than inserting rows 
into the database and periodically (every minute) pulling stats from 
pg_stat_user_tables. I am not running vacuum or analyze manually.

Attachment: pglog.tgz
Description: Binary data

Attachment: datalog.tgz
Description: Binary data