Re: Database size Vs performance degradation

From: Mark Roberts <mailing_lists(at)pandapocket(dot)com>
To: Miernik <public(at)public(dot)miernik(dot)name>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Database size Vs performance degradation
Date: 2008-07-30 22:14:19
Message-ID: 1217456059.6288.52.camel@localhost
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-performance

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

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message tarcizioab 2008-07-31 00:19:15 Difference between "Explain analyze" and "\timing"
Previous Message Miernik 2008-07-30 22:11:58 what is less resource-intensive, WHERE id IN or INNER JOIN?