To: Greg Smith <greg@2ndquadrant.com>
cc: Robert Haas <robertmhaas@gmail.com>, Josh Berkus <josh@agliodbs.com>,
	Rick <richard.branton@ca.com>, pgsql-performance@postgresql.org
Subject: Re: autovacuum strategy / parameters 
In-reply-to: <4BDC60A9.60108@2ndquadrant.com> 
References: <c2d2b6cf-895f-4a4b-aa8b-3adaac33c794@z3g2000yqz.googlegroups.com>
	<y2g603c8f071004221155h3874a3c0ybbe2c790e3c1bf80@mail.gmail.com>
	<65d9d15d-41d2-4bfa-8cc3-6148b4fb9022@z3g2000yqz.googlegroups.com>
	<h2o603c8f071004271755v3e56a408hbd23b5c355058aa@mail.gmail.com>
	<4BDB5ECE.4010607@agliodbs.com>
	<AANLkTik9xSarBYBapF1CVrPoRuqimyGAFAgOaGgvHpLR@mail.gmail.com>
	<4BDC60A9.60108@2ndquadrant.com>
Comments: In-reply-to Greg Smith <greg@2ndquadrant.com>
	message dated "Sat, 01 May 2010 13:11:05 -0400"
Date: Sat, 01 May 2010 13:25:40 -0400
Message-ID: <5266.1272734740@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Greg Smith <greg@2ndquadrant.com> writes:
> 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.

With a little luck, HOT will alleviate that case, since HOT updates can
be reclaimed without running vacuum per se.  I agree there's a risk
there though.

Now that partial vacuum is available, it'd be a real good thing to
revisit these numbers.

			regards, tom lane