Re: Drop table vs Delete record

["Orhan Aglagul" <oaglagul(at)cittio(dot)com>]

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

-----Original Message-----
From: Andreas Kostyrka [mailto:andreas(at)kostyrka(dot)org] 
Sent: Tuesday, May 22, 2007 11:49 AM
To: Orhan Aglagul
Cc: <pgsql-performance(at)postgresql(dot)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(at)cittio(dot)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 periodically deletes expired records (also in thousands) from the same table (expired ones).  So, we have one thread adding a row while the other thread is trying to delete a row. In a short time the overall performance of any sql statements on 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 new table t1 (for new inserts) and just drop the tables with expired records rather then doing a delete record? (t1 is a simple table with many rows and no constraints). 

(I know I could run vacuum analyze) 

Thanks,

Orhan A.