Re: DELETE taking too much memory

> On Thu, 2011-07-07 at 15:34 +0200, vincent dephily wrote:
>> Hi,
>>
>> I have a delete query taking 7.2G of ram (and counting) but I do not
>> understant why so much memory is necessary. The server has 12G, and
>> I'm afraid it'll go into swap. Using postgres 8.3.14.
>>
>> I'm purging some old data from table t1, which should cascade-delete
>> referencing rows in t2. Here's an anonymized rundown :
>>
>> # explain delete from t1 where t1id in (select t1id from t2 where
>> foo=0 and bar < '20101101');

It looks as though you're hitting one of the known issues with
PostgreSQL and FKs. The FK constraint checks and CASCADE actions are
implemented using AFTER triggers, which are queued up during the query
to be executed at the end. For very large queries, this queue of
pending triggers can become very large, using up all available memory.

There's a TODO item to try to fix this for a future version of
PostgreSQL (maybe I'll have another go at it for 9.2), but at the
moment all versions of PostgreSQL suffer from this problem.

The simplest work-around for you might be to break your deletes up
into smaller chunks, say 100k or 1M rows at a time, eg:

delete from t1 where t1id in (select t1id from t2 where foo=0 and bar
< '20101101' limit 100000);

Regards,
Dean