auto vacuum

Hi all.

We had a crisis this week that was resolved by tuning pg_autovacuum for a particular table. The table is supposed to contain a small number of items at any given point in time (typically around 10,000-30,000). The items are inserted when we send out a message, and are selected, then deleted when a reply to the message arrives. This may be done at a rather high rate - sometimes a thousand a minute or around that.

We found out that the table's response depends on the rate of ANALYZE being performed. We have tuned the values in pg_autovacuum so that we have around one analyze per minute.

What is bothering me is that sometimes the auto vacuum daemon decides to perform a vacuum analyze rather than just analyze. If it just does a vacuum independent of the analyze, we don't see much impact on performance. But if it does vacuum analyze, it means that until vacuum is over, it doesn't do another analyze, and this may take about five minutes, in which our performance under load conditions might deteriorate.

Is there any way to cause pg_autovacuum not to choose vacuum analyze?

I thought of changing the vacuum frequency to be rare - but then it might take even longer to vacuum, and if a long vacuum analyze falls on a high load time, although the chances are smaller, the risk is higher. We can't afford a slowdown in that table.

# select * from pg_stat_user_tables where relname = 'transient';
-[ RECORD 1 ]----+------------------------------
relid | 17866
schemaname | public
relname | transient
seq_scan | 49633
seq_tup_read | 1388557648
idx_scan | 9200950
idx_tup_fetch | 9960245
n_tup_ins | 6572067
n_tup_upd | 0
n_tup_del | 6466085
n_tup_hot_upd | 0
n_live_tup | 81060
n_dead_tup | 10097
last_vacuum | 2010-04-11 00:29:52.266617+03
last_autovacuum | 2010-04-14 11:47:43.13062+03
last_analyze | 2010-04-11 00:29:52.266617+03
last_autoanalyze | 2010-04-14 12:04:39.090055+03

Thank you,
Herouth