Re: Drop table vs Delete record

Checking out right now....
Thanks for the fast response.

-----Original Message-----
From: Andreas Kostyrka [mailto:andreas@kostyrka.org]
Sent: Tuesday, May 22, 2007 11:49 AM
To: Orhan Aglagul
Cc: <pgsql-performance@postgresql.org>
Subject: AW: [PERFORM] Drop table vs Delete record

Consider table partitioning (it's described in the manual).

Andreas

-- Ursprüngl. Mitteil. --
Betreff:    [PERFORM] Drop table vs Delete record
Von:    "Orhan Aglagul" <oaglagul@cittio.com>
Datum:        22.05.2007 18:42


My application has two threads, one inserts thousands of  records per second into a table  (t1) and the  other thread
periodicallydeletes expired records (also in thousands) from the same table (expired ones).  So, we have one thread
addinga row while the other thread is trying to delete a row. In a short time the overall performance of any sql
statementson that instance degrades. (ex.  Select count(*) from  t1 takes  more then few seconds with less than 10K
rows).

My question is: Would any sql statement perform better if I would rename the table to t1_%indx periodically, create a
newtable t1 (for new inserts) and just drop the tables with expired records rather then doing a delete record? (t1 is a
simpletable with many rows and no constraints).  

(I know I could run vacuum analyze)

Thanks,

Orhan A.