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

On Sun, 10 Mar 2019 at 10:20, <reg_pg_stefanz(at)perfexpert(dot)ch> 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