X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id 9B0E49DCDF7;
	Wed,  1 Feb 2006 17:16:49 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 32989-10; Wed,  1 Feb 2006 17:16:50 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from noel.decibel.org (noel.decibel.org [67.100.216.10])
	by postgresql.org (Postfix) with ESMTP id D8EBE9DCDF3;
	Wed,  1 Feb 2006 17:16:46 -0400 (AST)
Received: by noel.decibel.org (Postfix, from userid 1001)
	id 0624C39841; Wed,  1 Feb 2006 15:16:33 -0600 (CST)
Date: Wed, 1 Feb 2006 15:16:33 -0600
From: "Jim C. Nasby" <jnasby@pervasive.com>
To: pgsql-admin@postgresql.org, pgsql-performance@postgresql.org
Subject: Default autovacuum settings too conservative
Message-ID: <20060201211633.GM95850@pervasive.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
X-Operating-System: FreeBSD 6.0-RELEASE amd64
X-Distributed: Join the Effort!  http://www.distributed.net
User-Agent: Mutt/1.5.11
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.104 required=5 tests=[AWL=0.104]
X-Spam-Score: 0.104
X-Spam-Level: 
X-Archive-Number: 200602/8
X-Sequence-Number: 20429

As I recall, the idea behind vacuum_threshold was to prevent
too-frequent vacuuming of small tables. I'm beginning to question this
reasoning:

Small tables vacuum very, very quickly, so 'extra' vacuuming is very
unlikely to hurt system performance.

Small tables are most likely to have either very few updates (ie: a
'lookup table') or very frequent updates (ie: a table implementing a
queue). In the former, even with vacuum_threshold = 0 vacuum will be a
very rare occurance. In the later case, a high threshold is likely to
cause a large amount of un-nececcasry bloat.

Also, vacuum_scale_factor of 0.4 seems unreasonably large. It means
tables will be 40% dead space, which seems excessively wasteful.
Something between 0.1 and 0.2 seems much better.

Has anyone looked at how effective these two settings are?
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461