Re: DELETE queries slow down

Galantucci Giovanni wrote:
> I have a problem with DELETE performance with postgres 7.4.

You should consider upgrading. While I don't recall any particular
enhancements that would directly help with this problem, 8.2 is
generally faster.

> 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 suspect that at first the tables fit in memory, and operations are
therefore fast. But after they grow beyond a certain point, they no
longer fit in memory, and you start doing I/O which is slow.

> I do a vacuum analyze every minute.

I'd suggest doing a VACUUM (no analyze) after every DELETE.

Have you checked the EXPLAIN ANALYZE output of the DELETE? It might be
choosing a bad plan after the table grows.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com