Re: Triggering autovacuum

Reuven M. Lerner wrote:
> Could this be because we're only doing INSERTs and SELECTs? In such a
> case, then we would never reach the threshold of modified tuples that
> autovacuum looks for, and thus it would never run. That would, by my
> reasoning, mean that we'll never tag dead tuples (which isn't a big
> deal if we're never deleting or updating rows), but also that we'll
> never run ANALYZE as part of autovacuum. Which would mean that we'd
> be running with out-of-date statistics.

The computation for whether the auto-analyze portion of autovacuum runs
takes into account INSERT traffic, so the stats don't go too far out of
data on this style of workload. The one for the vacuum work only
considers dead rows. So your case should be seeing regular entries for
the last auto-analyze, but possibly not for last auto-vacuum.

Eventually autovacuum will kick in anyway for transaction id wraparound,
and that might be traumatic when it does happen. You might want to
schedule periodic manual vacuum on these tables to at least have that
happen at a good time. Wraparound autovacuum has this bad habit of
finally kicking in only during periods of peak busy on the server.

--
Greg Smith 2ndQuadrant US greg(at)2ndQuadrant(dot)com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books