Robert Haas wrote:
I don't have a stake in the ground on what the right settings are, but I think it's fair to say that if you vacuum OR analyze much less frequently than what we recommend my default, it might break.
I think the default settings are essentially minimum recommended frequencies. They aren't too terrible for the giant data warehouse case Josh was suggesting they came from--waiting until there's 20% worth of dead stuff before kicking off an intensive vacuum is OK when vacuum is expensive and you're mostly running big queries anyway. And for smaller tables, the threshold helps it kick in a little earlier. It's unlikely anyone wants to *increase* those, so that autovacuum runs even less; out of the box it's not tuned to run very often at all.
If anything, I'd expect people to want to increase how often it runs, for tables where much less than 20% dead is a problem. The most common situation I've seen where that's the case is when you have a hotspot of heavily updated rows in a large table, and this may match some of the situations that Robert was alluding to seeing. Let's say you have a big table where 0.5% of the users each update their respective records heavily, averaging 30 times each. That's only going to result in 15% dead rows, so no autovacuum. But latency for those users will suffer greatly, because they might have to do lots of seeking around to get their little slice of the data.
-- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support greg@xxxxxxxxxxxxxxx www.2ndQuadrant.us -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
