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.