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.