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