From: Chris Mair <chris@1006.org>
To: oaglagul@cittio.com
Cc: pgsql-performance@postgresql.org
Subject: Re: Drop table vs Delete record
Date: Wed, 23 May 2007 22:24:23 +0200
Message-ID: <4654A2F7.5000409@1006.org> (raw)
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: chris@1006.org, oaglagul@cittio.com
Subject: Re: Drop table vs Delete record
In-Reply-To: <4654A2F7.5000409@1006.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox