memory problems and crash of db when deleting data from table withthousands of partitions - Mailing list pgsql-bugs

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