Thread: Drop table vs Delete record
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.
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 periodicallydeletes expired records (also in thousands) from the same table (expired ones). So, we have one thread addinga row while the other thread is trying to delete a row. In a short time the overall performance of any sql statementson 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 newtable t1 (for new inserts) and just drop the tables with expired records rather then doing a delete record? (t1 is a simpletable with many rows and no constraints). (I know I could run vacuum analyze) Thanks, Orhan A.
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 periodicallydeletes expired records (also in thousands) from the same table (expired ones). So, we have one thread addinga row while the other thread is trying to delete a row. In a short time the overall performance of any sql statementson 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 newtable t1 (for new inserts) and just drop the tables with expired records rather then doing a delete record? (t1 is a simpletable with many rows and no constraints). (I know I could run vacuum analyze) Thanks, Orhan A.
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.