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(at)cittio(dot)com>
> To: <pgsql-performance(at)postgresql(dot)org>
> Subject: Drop table vs Delete record
> Message-ID: <868BCE5A6576F44A862F1FBBC3E14A0103A595AB(at)ms17(dot)mse9(dot)exchange(dot)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.