auto vacuum

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.