Re: Drop table vs Delete record

Hi,

with that setup you should vacuum aggressivley.
I'd send a vacuum statement in a third thread every 15 minutes or so.

The table renaming trick doesn't sound very handy or even
necessary...

Bye,
Chris.



 > Date: Tue, 22 May 2007 14:38:40 -0400
 > From: "Orhan Aglagul" <oaglagul@cittio.com>
 > To: <pgsql-performance@postgresql.org>
 > Subject: Drop table vs Delete record
 > Message-ID: <868BCE5A6576F44A862F1FBBC3E14A0103A595AB@ms17.mse9.exchange.ms>
 >
 >
 > 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.