DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:to:subject:mime-version:content-type;
	b=eY5ZUjThbhQlXQzciDhr6JYmRsxzIHbfo2saQ8YgN+LSGFxntt/iw074AF0+9Ri3+K92OjIbKM+ihsHc+nfng3sw29P1vBzH06rMgeBUHv2KLvln3ZPgVu79BAfXfyRT382QhHqZ0ZMqLCK53Piey95lgoM1y/l3NRBLI3gwQMs=
Message-ID: <357fa7590612130844q58ef3ca5q31e164f9b006e0fc@mail.gmail.com>
Date: Wed, 13 Dec 2006 11:44:19 -0500
From: "Steven Flatt" <steven.flatt@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Insertion to temp table deteriorating over time
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_140820_24849456.1166028259590"

------=_Part_140820_24849456.1166028259590
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

Hi,

Our application is using Postgres 7.4 and I'd like to understand the root
cause of this problem:

To speed up overall insert time, our application will write thousands of
rows, one by one, into a temp table (same structure as a permanent table),
then do a bulk insert from the temp table to the permanent table.  After
this bulk insert is done, the temp table is truncated and the process is
repeated.  We do this because Postgres can do many individual inserts to a
temp table much faster than to a permanent table.

The problem we are seeing is that over time, the cost of a single insert to
the temp table seems to grow.  After a restart of postgres, a single insert
to the temp table takes about 3ms.  Over a few days, this grows to about
60ms per insert.  Restarting postgres drops this insert time back to 3ms,
supposedly because the temp table is re-created.  Our workaround right now
is to restart the database every few days, but we don't like this solution
much.

Any idea where the bloat is happening?  I believe that if we were dropping
and re-creating the temp table over and over, that could result in pg_class
bloat (among other catalog tables), but what is going wrong if we use the
same table over and over and truncate it?

Thanks,
Steve

------=_Part_140820_24849456.1166028259590
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

<div>Hi,</div>
<div>&nbsp;</div>
<div>Our application is using Postgres 7.4 and I'd like to understand the root cause of this problem:</div>
<div>&nbsp;</div>
<div>To speed up overall insert time, our application will write thousands of rows, one by one, into a temp table (same structure as a permanent table), then do a bulk insert from the temp table to the permanent table.&nbsp; After this bulk insert is done, the temp table is truncated and the process is repeated.&nbsp; We do this because Postgres can do many individual inserts to a temp table much faster than to a permanent table.
</div>
<div>&nbsp;</div>
<div>The problem we are seeing is that over time, the cost of a single insert to the temp table seems to grow.&nbsp; After a restart of postgres, a single insert to the temp table takes about 3ms.&nbsp; Over a few days, this grows to about 60ms per insert.&nbsp; Restarting postgres drops this insert time back to 3ms, supposedly&nbsp;because the temp table is re-created.&nbsp; Our workaround right now is to restart the database every few days, but we don't like this solution much.
</div>
<div>&nbsp;</div>
<div>Any idea where the bloat is happening?&nbsp; I believe that if we were dropping and re-creating the temp table over and over, that could result in pg_class bloat (among other catalog tables), but what is going wrong if we use the same table over and over and truncate it?
</div>
<div>&nbsp;</div>
<div>Thanks,</div>
<div>Steve</div>

------=_Part_140820_24849456.1166028259590--