delete on table with many partitions uses a lot of ram

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

delete on table with many partitions uses a lot of ram

reg_pg_stefanz
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





Reply | Threaded
Open this post in threaded view
|

Re: delete on table with many partitions uses a lot of ram

David Rowley-3
On Sun, 10 Mar 2019 at 10:20, <[hidden email]> wrote:
> I noticed that a delete on a table with many partitions seems to be
> using a lot of ram.

> Is this a known behaviour or related to my setup?

Yeah, It's known. There's a warning against what you're doing in
https://www.postgresql.org/docs/10/ddl-partitioning.html

I see that note has been changed in v11's documents, but I really
don't think v11 should have changed that. The memory problem still
exists with v11.  The only thing that was improved on that front was
with how partition pruning works, which only saves CPU, not RAM.

It's down to how DELETE and UPDATE plans are generated with
partitioned table or inheritance parents.   The memory growth is
basically quadratic with the number of partitions.  It's possible we
may have a solution for this by the time PostgreSQL 13 is out, but it
won't be fixed for 12.  However, nothing is entirely certain that far
out.

It's probably best to reduce the number of partitions.

--
 David Rowley                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Reply | Threaded
Open this post in threaded view
|

Re: delete on table with many partitions uses a lot of ram

reg_pg_stefanz
I must have missed this, I did not immediately realize there was a
difference between select and delete
Thanks for the explanation and outlook.
Stefan