X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 9BAE9D1B4E1 for ; Wed, 17 Sep 2003 20:15:20 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 87902-10 for ; Wed, 17 Sep 2003 17:15:10 -0300 (ADT) Received: from davinci.ethosmedia.com (unknown [209.128.84.228]) by svr1.postgresql.org (Postfix) with ESMTP id 5AD4FD1B4EF for ; Wed, 17 Sep 2003 17:15:08 -0300 (ADT) Received: from [66.219.92.2] (HELO temoku) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2) with ESMTP id 3651204; Wed, 17 Sep 2003 13:15:40 -0700 Content-Type: text/plain; charset="iso-8859-1" From: Josh Berkus Reply-To: josh@agliodbs.com Organization: Aglio Database Solutions To: "Matt Clark" , Subject: Re: Is there a reason _not_ to vacuum continuously? Date: Wed, 17 Sep 2003 13:13:31 -0700 User-Agent: KMail/1.4.3 References: In-Reply-To: MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Message-Id: <200309171313.31078.josh@agliodbs.com> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200309/247 X-Sequence-Number: 3613 Matt, > Is there any reason for me not to run continuous sequential vacuum analyz= es? > At least for the 6 tables that see a lot of updates? No. You've already proven that the performance gain on queries offsets the= =20 loss from the vacuuming. There is no other "gotcha".=20=20=20 However:=20 1) You may be able to decrease the required frequency of vacuums by adjusti= ng=20 your FSM_relations parameter. Have you played with this at all? The defau= lt=20 is very low. 2) Are you sure that ANALYZE is needed? Vacuum is required whenever lots = of=20 rows are updated, but analyze is needed only when the *distribution* of=20 values changes significantly. 3) using PG 7.3 or less, you will also need to REINDEX these tables+indexes= =20 often (daily?). This issue will go away in 7.4, which should make you an= =20 early adopter of 7.4. > I hear 10% of tuples updated as a good time to vac-an, but does my typical > count of 3 indexes per table affect that? Not until 7.4. --=20 -Josh Berkus Aglio Database Solutions San Francisco