DELETE queries slow down

Hi all,

I have a problem with DELETE performance with postgres 7.4.

I have a database with 2 great tables (about 150,000 rows) continuously
updated, with 1000 - 1200 INSERT per second and 2 or 3 huge DELETE per
minute, in which we delete almost all the rows inserted in the 2 tables
during the previous minute.

I have a single, indexed foreign key between the 2 tables.

In this scenario we have always a problem with the delete:

For 1 or 2 hours we update only one table, and everything goes ok, where
DELETE last at most 6 or 7 seconds.

Then for a minute we do INSERT on both table, and everything continue
going ok, with DELETE that last about 10 seconds.

From that moment on, DELETES become timeless, and last for 240 and more
seconds!

Then I can't recover from this state because INSERT continue with the
same rate and DELETE become more and more slow.

I do a vacuum analyze every minute.

What can I do to avoid or at least limit that problem?

I will be graceful to everyone who could help me.

Hi,

Gianluca

Internet Email Confidentiality Footer
-----------------------------------------------------------------------------------------------------
La presente comunicazione, con le informazioni in essa contenute e ogni documento o file allegato, e' rivolta unicamente alla/e persona/e cui e' indirizzata ed alle altre da questa autorizzata/e a riceverla. Se non siete i destinatari/autorizzati siete avvisati che qualsiasi azione, copia, comunicazione, divulgazione o simili basate sul contenuto di tali informazioni e' vietata e potrebbe essere contro la legge (art. 616 C.P., D.Lgs n. 196/2003 Codice in materia di protezione dei dati personali). Se avete ricevuto questa comunicazione per errore, vi preghiamo di darne immediata notizia al mittente e di distruggere il messaggio originale e ogni file allegato senza farne copia alcuna o riprodurne in alcun modo il contenuto.

This e-mail and its attachments are intended for the addressee(s) only and are confidential and/or may contain legally privileged information. If you have received this message by mistake or are not one of the addressees above, you may take no action based on it, and you may not copy or show it to anyone; please reply to this e-mail and point out the error which has occurred.
-----------------------------------------------------------------------------------------------------