pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
To: Josef Machytka <josef.machytka@gmail.com>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: memory problems and crash of db when deleting data from table with thousands of partitions
Date: Wed, 23 Oct 2019 14:47:51 +0200
Message-ID: <20191023124751.pnzcos7i3ei2vjxc@development> (raw)
In-Reply-To: <CAGvVEFueqXeYL0z2fTQMWYSz7Gc9czSrOvCSNubb9rHHVL-2OA@mail.gmail.com>
References: <CAGvVEFueqXeYL0z2fTQMWYSz7Gc9czSrOvCSNubb9rHHVL-2OA@mail.gmail.com>

On Wed, Oct 23, 2019 at 01:46:23PM +0200, Josef Machytka wrote:
>Hi people,
>
>I know this is actually known problem (
>https://stackoverflow.com/questions/49291451/postgres-10-3-heavily-partitioned-table-and-cannot-dele...).
>
>
>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.
>

Yeah, I think this is a known issue - there are cases where we're not
smart enough and end up opening/locking all the partitions, resulting in
excessive memory consumption (and OOM). I suppose this is one of those
cases, but I'd have to see memory context stats to know for sure.

Unfortunately, that's a design issue, and it's not going to be fixed in
backbranches. We're improving this - perhaps PostgreSQL 12 would
improve the behavior in your case, and hopefully 13 will do even better.

>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.
>

Right, that's a reasonable workaround.


regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services





view thread (6+ messages)  latest in thread

Message-ID: <20191023124751.pnzcos7i3ei2vjxc@development>
Permalink:  ../20191023124751.pnzcos7i3ei2vjxc@development/
Also on:    postgresql.org/message-id/20191023124751.pnzcos7i3ei2vjxc@development

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-bugs@postgresql.org
  Cc: tomas.vondra@2ndquadrant.com, josef.machytka@gmail.com
  Subject: Re: memory problems and crash of db when deleting data from table with thousands of partitions
  In-Reply-To: <20191023124751.pnzcos7i3ei2vjxc@development>
  References: <CAGvVEFueqXeYL0z2fTQMWYSz7Gc9czSrOvCSNubb9rHHVL-2OA@mail.gmail.com> <20191023124751.pnzcos7i3ei2vjxc@development>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox