Search Postgresql Archives

delete on table with many partitions uses a lot of ram

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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








[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux