Received: from localhost (unknown [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id 82BD76504C8
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Wed, 30 Jul 2008 19:14:14 -0300 (ADT)
Received: from postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 93229-07 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Wed, 30 Jul 2008 19:14:07 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from s402.sureserver.com (s402.sureserver.com [216.35.197.71])
	by postgresql.org (Postfix) with ESMTP id 60A126504CF
	for <pgsql-performance@postgresql.org>;
	Wed, 30 Jul 2008 19:14:10 -0300 (ADT)
Received: (qmail 6006 invoked by uid 504); 30 Jul 2008 22:14:07 -0000
Received: from unknown (HELO ?97.0.177.44?) (mailing?lists@70.102.219.22)
	by ns1.s402.sureserver.com with ESMTPA; 30 Jul 2008 22:14:07 -0000
Subject: Re: Database size Vs performance degradation
From: Mark Roberts <mailing_lists@pandapocket.com>
To: Miernik <public@public.miernik.name>
Cc: pgsql-performance@postgresql.org
In-Reply-To: <20080730215834.5323.0.NOFFLE@turbacz.local>
References: 
 <35FABCF85D99464FB00BC5123DC2A70A051D0FB8@s228130hz1ew09.apptix-01.savvis.net>
	<276541.4189.qm@web25805.mail.ukl.yahoo.com>
	<20080730215834.5323.0.NOFFLE@turbacz.local>
Content-Type: text/plain
Date: Wed, 30 Jul 2008 15:14:19 -0700
Message-Id: <1217456059.6288.52.camel@localhost>
Mime-Version: 1.0
X-Mailer: Evolution 2.12.1 
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none
X-Spam-Level: 
X-Archive-Number: 200807/260
X-Sequence-Number: 30860

On Wed, 2008-07-30 at 23:58 +0200, Miernik wrote:

> I have a similar, but different situation, where I TRUNCATE a table
> with
> 60k rows every hour, and refill it with new rows. Would it be better
> (concerning bloat) to just DROP the table every hour, and recreate it,
> then to TRUNCATE it? Or does TRUNCATE take care of the boat as good as
> a
> DROP and CREATE?
> 
> I am running 8.3.3 in a 48 MB RAM Xen, so performance matters much.

I've successfully used truncate for this purpose (server 8.2.6):

-----------------------------

psql=> select pg_relation_size(oid) from pg_class where relname =
'asdf';
 pg_relation_size 
------------------
            32768
(1 row)

Time: 0.597 ms
psql=> truncate asdf;
TRUNCATE TABLE
Time: 1.069 ms
psql=> select pg_relation_size(oid) from pg_class where relname =
'asdf';
 pg_relation_size 
------------------
                0
(1 row)

-Mark