massive memory allocation until machine crashes - Mailing list pgsql-general

Hello,

given is a postgres database in version
------------------------------------------------------------------------
 PostgreSQL 7.4.8 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.2

and there is a table "visit" with 26 million tuples using 8 GB of space

SELECT relname, reltuples, relpages*8/1024 as relpages_in_MB FROM pg_class
ORDER BY relpages DESC limit 10;
     relname      | reltuples  | relpages_in_mb
------------------+------------+----------------
 visit            | 2.6348e+07 |           7673

The table is from ofbiz and for logging accesses to the webapplication.
Running a delete command which deletes all but a few tuples causes the
postmaster to allocate memory:
---    10903 postgres  25   0  214M 213M 10412 R    95.3 10.5   6:07
postmaster

Until all memory and swap is gone - that was 1.4GB of top:SIZE
---    delete from visit where date(created_stamp) <
date(current_timestamp - '7 days'::interval);

I just do not know why it needs allocating so much memory.

I solved the problem in dividing the affected tuples in parts and
deleting it part by part.
---    delete from visit where date(created_stamp) <
date(current_timestamp - '300 days'::interval);
---    delete from visit where date(created_stamp) <
date(current_timestamp - '240 days'::interval);
---    ...

Why does the postmaster need so much memory to delete tuples?

Thanks in advance.
    Alexander Elgert