memory problems and crash of db when deleting data from table with thousands of partitions

Hi people,

I know this is actually known problem (
https://stackoverflow.com/questions/49291451/postgres-10-3-heavily-partitioned-table-and-cannot-delete-any-records/58521850#58521850).

I would just like to add my voice and description of use case to this
topic. If this could be repaired it would be amazing because we use new
native partitioning really a lot in our company and we like it - well, not
counting this problem into it....

I have this problem on PostgreSQL 11 (PostgreSQL 11.5 (Debian
11.5-3.pgdg90+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian
6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit) with partitioned table having
more levels of partitioning. Main table is partitioned by shops and each
shops months (year-month) over several past years. Together several
thousands of partitions and number is still growing.

When we simply insert new data (which is usual operation we do) all is
perfect. But lately we needed to delete some wrong data over all partitions
and PostgreSQL started to crash during this operation.

Crashes are always the same - PostgreSQL starts to use more and more memory
and eventually is killed by OOM killer. I tried to fiddle with work_mem and
other settings - nothing, database just crashes a bit later but crashes
anyway.

Of course workaround works - I can use script to do deletion or update over
each shop partition separately. There are only several dozens of monthly
partitions for each shop so it work perfectly. But anyway if problem would
be repaired and simple delete/update over top main table would be possible
this would be much better.

Best regards

Josef Machytka
Fit Analytics, Berlin