Received: from localhost (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id B5C0F634AAD
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Sat, 21 Mar 2009 10:32:09 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024)
	with ESMTP id 90951-08 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Sat, 21 Mar 2009 10:31:59 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from yx-out-2324.google.com (yx-out-2324.google.com [74.125.44.28])
	by mail.postgresql.org (Postfix) with ESMTP id BCE43634A7F
	for <pgsql-admin@postgresql.org>; Sat, 21 Mar 2009 10:32:06 -0300 (ADT)
Received: by yx-out-2324.google.com with SMTP id 8so942090yxm.73
	for <pgsql-admin@postgresql.org>; Sat, 21 Mar 2009 06:32:04 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:mime-version:in-reply-to:references:date
	:received:message-id:subject:from:to:cc:content-type;
	bh=yLC4IAmyqBUcy7Y8THeQYkzN/ikEgMjo9t0Y4E2euc4=;
	b=xgP0GHxAxHZcKRky8ZQtMRcHbrdiIhuy4S3q7PXVoTi8vVx3ckX7GZF6e2rhbjFz0p
	HDkhrPQgUeFlrZmxub1o/AFUA5TZOdSyACCkPVawAd2USSw4sM5t+ni+I2RcFhtppUrd
	PS7k2gWrPmjCpA9w9+yDiG42wSYW5TWTnssag=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type;
	b=JcHiiPzMSn8+KIpZ0u+/vn3JpxeJIaHmFNgvOLI7BifX6n9nu7Sy8q7PsGghkmwolG
	DI0bXzUclScbqTYHGSptnQgUhkBc0zY3BM7CJDEDGQyyNYbWBOHEVUyOuODwCP8uWy9+
	/t5qaajIDorw6oQWBmnWMW220+4BtqzItm1bI=
MIME-Version: 1.0
In-Reply-To: <FE44E0D7EAD2ED4BB2165071DB8E328C04E84A32@egcrc-ex01.egcrc.org>
References: <FE44E0D7EAD2ED4BB2165071DB8E328C04E84A32@egcrc-ex01.egcrc.org>
Date: Sat, 21 Mar 2009 09:31:49 -0400
Received: by 10.231.11.137 with SMTP id t9mr1117371ibt.49.1237642324385; Sat,
	21 Mar 2009 06:32:04 -0700 (PDT)
Message-ID: <c82353130903210631x71e1e8a0u65d2684ce2d48292@mail.gmail.com>
Subject: Re: vacuuming and reindexing
From: Diogenes Caraballo <diogns@gmail.com>
To: Tena Sakai <tsakai@gallo.ucsf.edu>
Cc: pgsql-admin@postgresql.org
Content-Type: multipart/alternative; boundary=000325574c3e1aa20c0465a10f12
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.001 tagged_above=0 required=5
	tests=HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 200903/210
X-Sequence-Number: 31710

--000325574c3e1aa20c0465a10f12
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

do you also update rows of that table?

because that action delete old one's and insert new one's..

if you are just inserting rows and not updating or deleting, i think is
correct your point.


Regards,
--
"Sin desaf=EDos la vida es una rutina, una lenta agon=EDa"

     Di=F3genes Caraballo


On Fri, Mar 20, 2009 at 18:03, Tena Sakai <tsakai@gallo.ucsf.edu> wrote:

>  Hi Everybody,
>
> I have a table that I add rows every evening.
> A rule I have for this table is that no row
> ever gets deleted.  It has 600+ million rows.
>
> The last time I did it, it took nearly 4 hours
> to vacuum and 13 hours 40 minutes to reindex.
>
> My rudimental understanding of vacuuming is
> that when the rows get "deleted" it is only
> marked as "deleted" but the data is left alone
> until the act of vacuuming takes place.  The
> vacuuming really gets rid of rows and pushes
> the data in such the way there is no "hole."
> And therefore after vacuuming, it is necessary
> to reindex (and analyze) the table.
>
> Is this a correct understanding?
>
> If it is (and as new rows get added new indexes
> are also built for the new rows), the fact that
> there is no deletion means there is no necessity
> for vacuuming this particular table?
>
> I appreciate any thoughts on this matter.
>
> Regards,
>
> Tena Sakai
> tsakai@gallo.ucsf.edu
>
>
>
>

--000325574c3e1aa20c0465a10f12
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

do you also update rows of that table?<br><br>because that action delete ol=
d one&#39;s and insert new one&#39;s..<br><br>if you are just inserting row=
s and not updating or deleting, i think is correct your point.<br><br><br>

Regards,<br clear=3D"all">--<br>&quot;Sin desaf=EDos la vida es una rutina,=
 una lenta agon=EDa&quot;<br><br> =A0 =A0 =A0Di=F3genes Caraballo<br>
<br><br><div class=3D"gmail_quote">On Fri, Mar 20, 2009 at 18:03, Tena Saka=
i <span dir=3D"ltr">&lt;<a href=3D"mailto:tsakai@gallo.ucsf.edu">tsakai@gal=
lo.ucsf.edu</a>&gt;</span> wrote:<br><blockquote class=3D"gmail_quote" styl=
e=3D"border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; =
padding-left: 1ex;">








<div>


<p><font size=3D"2">Hi Everybody,<br>
<br>
I have a table that I add rows every evening.<br>
A rule I have for this table is that no row<br>
ever gets deleted.=A0 It has 600+ million rows.<br>
<br>
The last time I did it, it took nearly 4 hours<br>
to vacuum and 13 hours 40 minutes to reindex.<br>
<br>
My rudimental understanding of vacuuming is<br>
that when the rows get &quot;deleted&quot; it is only<br>
marked as &quot;deleted&quot; but the data is left alone<br>
until the act of vacuuming takes place.=A0 The<br>
vacuuming really gets rid of rows and pushes<br>
the data in such the way there is no &quot;hole.&quot;<br>
And therefore after vacuuming, it is necessary<br>
to reindex (and analyze) the table.<br>
<br>
Is this a correct understanding?<br>
<br>
If it is (and as new rows get added new indexes<br>
are also built for the new rows), the fact that<br>
there is no deletion means there is no necessity<br>
for vacuuming this particular table?<br>
<br>
I appreciate any thoughts on this matter.<br>
<br>
Regards,<br>
<br>
Tena Sakai<br>
<a href=3D"mailto:tsakai@gallo.ucsf.edu" target=3D"_blank">tsakai@gallo.ucs=
f.edu</a><br>
<br>
<br>
<br>
</font>
</p>

</div>
</blockquote></div><br>

--000325574c3e1aa20c0465a10f12--