delete on table with many partitions uses a lot of ram

[reg_pg_stefanz@xxxxxxxxxxxxx]

Hi,

I noticed that a delete on a table with many partitions seems to be 
using a lot of ram.
It seems to occur during the planing phase, as  explain behaves the same 
as the actual execution of the delete.

On the simplified test below for 4000 partitions it seems to be using 
for a short time over 5Gb of Memory,  as if for each partition more than 
1 MB of Ram is allocated,
if a few concurrent sessions are doing this, the server is fast running 
out of memory

Only a delete is showing this behaviour, insert or select do not; I have 
not tested update.
Is this a known behaviour or related to my setup?

Versions 10, 11, even 12 complied from github source, showed similar 
behaviour.

Regards
Stefan


a simplified test with 4000 partitions:

drop table if exists big;

CREATE TABLE big (i int, j int)
 PARTITION BY RANGE (i);

CREATE TABLE big_0  PARTITION OF big
   FOR VALUES FROM (-1) TO (0);
CREATE INDEX ON big_0 (i);

do $$
DECLARE
 v_part varchar(100);
 v_sql TEXT;
 r record;
 dt date;
begin
  for r in (select generate_series(1,4000,1) nr)
    loop
       v_part:='big_'||r.nr;
       v_sql := format( 'CREATE TABLE %s PARTITION OF %s
                         FOR VALUES FROM (''%s'') TO (''%s'');
                         CREATE INDEX ON %s (i);',
                         v_part,'big',
                         r.nr-1,r.nr,
                         v_part);
        EXECUTE v_sql;
     end loop;
 END;
$$ LANGUAGE plpgsql;

select name, setting, short_desc from pg_settings
  where name in ('max_connections','max_locks_per_transaction');

begin;

select locktype, virtualtransaction, pid, mode, granted, fastpath, count(*)
  from pg_locks
 group by locktype, virtualtransaction, pid, mode, granted, fastpath;

-- delete from big where i=3 and j=0;
explain delete from big where i=3 and j=0;

select locktype, virtualtransaction, pid, mode, granted, fastpath, count(*)
  from pg_locks
 group by locktype, virtualtransaction, pid, mode, granted, fastpath;

rollback;

\q