Re: Database size Vs performance degradation

[Mark Roberts <mailing_lists(at)pandapocket(dot)com>]

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