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@2ndQuadrant.com www.2ndQuadrant.us