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
