Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 27A1D9FA48B for ; Sun, 26 Nov 2006 08:43:19 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-new, port 10024) with ESMTP id 56372-02 for ; Sun, 26 Nov 2006 08:43:09 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.172]) by postgresql.org (Postfix) with ESMTP id C8CAA9FA285 for ; Sun, 26 Nov 2006 08:43:13 -0400 (AST) Received: by ug-out-1314.google.com with SMTP id k3so811962ugf for ; Sun, 26 Nov 2006 04:43:12 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=IfT4/H5WZ9U20ht7rlJK9+xmSL3Fk7OkvenHLGrNLK3qFfSIm+Zjaer5JpAm0agj4tzLkOR6vHEs502KURwxVvocZg/euEeeJILMAXUejMQcyriQ5Mhsi5j8myZXYdmgxxdjDSrAbZCGpxj6xGZSZw8DMK6zQZ+DfKUFtecuO4w= Received: by 10.66.243.2 with SMTP id q2mr10298298ugh.1164544991475; Sun, 26 Nov 2006 04:43:11 -0800 (PST) Received: by 10.66.223.9 with HTTP; Sun, 26 Nov 2006 04:43:11 -0800 (PST) Message-ID: Date: Sun, 26 Nov 2006 09:43:11 -0300 From: "Marcelo Costa" To: "Joost Kraaijeveld" Subject: Re: When to vacuum a table? Cc: pgsql-performance@postgresql.org In-Reply-To: <1164540257.7902.0.camel@panoramix> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_9627_2871005.1164544991421" References: <1164540257.7902.0.camel@panoramix> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200611/218 X-Sequence-Number: 21988 ------=_Part_9627_2871005.1164544991421 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Hi, From: http://www.postgresql.org/docs/7.4/interactive/sql-vacuum.html "VACUUM reclaims storage occupied by deleted tuples. In normal PostgreSQLoperation, tuples that are deleted or obsoleted by an update are not physically removed from their table; they remain present until a VACUUM is done. Therefore it's necessary to do VACUUM periodically, especially on frequently-updated tables." "The "vacuum analyze" form additionally collects statistics on the disbursion of columns in the database, which the optimizer uses when it calculates just how to execute queries. The availability of this data can make a tremendous difference in the execution speed of queries. This comman= d can also be run from cron, but it probably makes more sense to run this command as part of your nightly backup procedure - if "vacuum" is going to screw up the database, you'd prefer it to happen immediately after (not before!) you've made a backup! The "vacuum" command is very reliable, but conservatism is the key to good system management. So, if you're using the export procedure described above, you don't need to do this extra step". All its tables constantly manipulated (INSERT, UPDATE, DELETE) they need a VACUUM, therefore the necessity to execute at least one time to the day normally of dawn if its database will be very great . [],s Marcelo Costa Secretaria Executiva de Educa=E7=E3o do Par=E1 Amazonia - Par=E1 - Brazil 2006/11/26, Joost Kraaijeveld : > > Hi, > > Are there guidelines (or any empirical data) available how to determine > how often a table should be vacuumed for optimum performance or is this > an experience / trial-and-error thing? > > TIA > > -- > Groeten, > > Joost Kraaijeveld > Askesis B.V. > Molukkenstraat 14 > 6524NB Nijmegen > tel: 024-3888063 / 06-51855277 > fax: 024-3608416 > web: www.askesis.nl > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > --=20 Marcelo Costa ------=_Part_9627_2871005.1164544991421 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Hi,

From: http://www.postgresql.org/docs/7.4/interactive/sql-vacuum.= html


"VACUUM reclaims storag= e occupied by deleted tuples. In normal=20 PostgreSQL operation, tuples that are deleted or obsoleted by an update are not physically removed from their table; they remain present until a VACUUM is done. Therefore it's necessary to do VACUUM periodically, especially on frequently-updated tables."

"The &quo= t;vacuum analyze" form additionally collects statistics on the=20 disbursion of columns in the database, which the optimizer uses when it calculates just how to execute queries. The availability of= this data can make a tremendous difference in the execution speed of queries. This command can also be run from cron, but it probably make= s more sense to run this command as part of your nightly backup procedure - if "vacuum" is going to screw up the dat= abase, you'd prefer it to happen immediately after (not before!) you've made a backup! The "vacuum" command is very reliable, but conserva= tism is the key to good system management. So, if you're using the export procedure described above, you don't need to do this extra step".

=09 =20 =09 <= input name=3D"kls" value=3D"0" type=3D"hidden">All its tables constantly ma= nipulated =09 (INSERT, UPDATE, DELETE) they need a VACUUM, therefore the necessity = to execute at least one time to the day normally of dawn if its database wi= ll be very great .

[],s

Marcelo Costa
Secretaria Executiva de Educa=E7=E3o = do Par=E1
Amazonia - Par=E1 - Brazil
 
2006/11/26, = Joost Kraaijeveld < J.Kraaijeveld@askesis.nl>= ;:
Hi,
<= br> Are there guidelines (or any empirical data) available how to determine
= how often a table should be vacuumed for optimum performance or is this
= an experience / trial-and-error thing?

TIA

--
Groeten,

Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Ni= jmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
web: www.askesis.nl

---------------------= ------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate       subscribe-nomail command to majordomo@postgresql.org so that y= our
       message can get through to the = mailing list cleanly



--
Marcelo Costa ------=_Part_9627_2871005.1164544991421--