pg.ddx.io pgsql-performance@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Drop table vs Delete record 4+ messages / 3 participants [nested] [flat]
* Drop table vs Delete record @ 2007-05-22 18:38 Orhan Aglagul <oaglagul@cittio.com> 0 siblings, 0 replies; 4+ messages in thread From: Orhan Aglagul @ 2007-05-22 18:38 UTC (permalink / raw) To: pgsql-performance 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. ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Drop table vs Delete record @ 2007-05-22 18:48 Andreas Kostyrka <andreas@kostyrka.org> 0 siblings, 1 reply; 4+ messages in thread From: Andreas Kostyrka @ 2007-05-22 18:48 UTC (permalink / raw) To: Orhan Aglagul <oaglagul@cittio.com>; +Cc: pgsql-performance; pgsql-performance Consider table partitioning (it's described in the manual). Andreas -- Ursprüngl. Mitteil. -- Betreff: [PERFORM] Drop table vs Delete record Von: "Orhan Aglagul" <oaglagul@cittio.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. ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Drop table vs Delete record @ 2007-05-22 18:53 Orhan Aglagul <oaglagul@cittio.com> parent: Andreas Kostyrka <andreas@kostyrka.org> 0 siblings, 0 replies; 4+ messages in thread From: Orhan Aglagul @ 2007-05-22 18:53 UTC (permalink / raw) To: Andreas Kostyrka <andreas@kostyrka.org>; +Cc: pgsql-performance Checking out right now.... Thanks for the fast response. -----Original Message----- From: Andreas Kostyrka [mailto:andreas@kostyrka.org] Sent: Tuesday, May 22, 2007 11:49 AM To: Orhan Aglagul Cc: <pgsql-performance@postgresql.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@cittio.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. ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Drop table vs Delete record @ 2007-05-23 20:24 Chris Mair <chris@1006.org> 0 siblings, 0 replies; 4+ messages in thread From: Chris Mair @ 2007-05-23 20:24 UTC (permalink / raw) To: oaglagul@cittio.com; +Cc: pgsql-performance 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. ^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2007-05-23 20:24 UTC | newest] Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2007-05-22 18:38 Drop table vs Delete record Orhan Aglagul <oaglagul@cittio.com> 2007-05-22 18:48 Re: Drop table vs Delete record Andreas Kostyrka <andreas@kostyrka.org> 2007-05-22 18:53 ` Orhan Aglagul <oaglagul@cittio.com> 2007-05-23 20:24 Re: Drop table vs Delete record Chris Mair <chris@1006.org>
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