Huge amount of memory consumed during transaction

henk de wit <henk53602@xxxxxxxxxxx> · Thu, 11 Oct 2007 16:04:38 +0200

Hi,

I'm running into a problem with PostgreSQL 8.2.4 (running on 32 bit Debian Etch/2x dual core C2D/8GB mem). The thing is that I have a huge transaction that does 2 things: 1) delete about 300.000 rows from a table with about 15 million rows and 2) do some (heavy) calculations and re-insert a litlte more than 300.000 new rows.

My problem is that this consumes huge amounts of memory. The transaction runs for about 20 minutes and during that transaction memory usage peaks to about 2GB. Over time, the more rows that are involved in this transaction, the higher the peak memory requirements.

Lately we increased our shared_buffers to 1.5GB, and during this transaction we reached the process memory limit, causing an out of memory and a rollback of the transaction:

BEGIN
DELETE 299980
ERROR:  out of memory
DETAIL:  Failed on request of size 4194304.
ROLLBACK
DROP SEQUENCE

real	19m45.797s
user	0m0.024s
sys	0m0.000s

On my development machine, which has less than 2GB of memory, I can not even finish the transaction.

Is there a way to tell PG to start swapping to disk instead of using ram memory during such a transaction?

Thanks in advance for all help

Express yourself instantly with MSN Messenger! MSN Messenger