Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iNG3W-0007kp-PU for pgsql-bugs@arkaria.postgresql.org; Wed, 23 Oct 2019 12:48:03 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1iNG3T-000350-AX for pgsql-bugs@arkaria.postgresql.org; Wed, 23 Oct 2019 12:47:59 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iNG3S-00034s-Vh for pgsql-bugs@lists.postgresql.org; Wed, 23 Oct 2019 12:47:59 +0000 Received: from mail-wm1-x341.google.com ([2a00:1450:4864:20::341]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iNG3P-00034Z-GE for pgsql-bugs@lists.postgresql.org; Wed, 23 Oct 2019 12:47:58 +0000 Received: by mail-wm1-x341.google.com with SMTP id v6so2547421wmj.0 for ; Wed, 23 Oct 2019 05:47:55 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=2ndquadrant-com.20150623.gappssmtp.com; s=20150623; h=date:from:to:cc:subject:message-id:references:mime-version :content-disposition:in-reply-to:user-agent; bh=YGGJN7U8ljuEaLT4KrMg6gCLv3fM6FVL4YX4LLtYOYg=; b=U6u+WlCxndh0YFVPHM14OF2g2z5pBNf4nY9IFtW7bHz5hh1AWqtRJrVgTubLnDG8mt ZReFS3bvSI8yiv3vaZeRdnV6R0p+gkc8LyHTIE7Sr96REaqejdOS0+ptYMwN6gFYE1mL x5o6v0As0JzWcCX6mqmBMxsw1smX/Do+CoYsQwm2zgEbd8raI3ySQOkT8XUGBNIdzTLD EFnzsX0PogzubUrfTXPF+T9WF+mOZtDbBLxJ1GuV5SAY3bY99Ac/OPsRAb4BP8ShBQ8t eFWCfOyjNU4wpkRAektlTFgq93RhP+gC6iTW2mclVVVKhJIJc8e0m+qcMgk4lniL4r08 k0AQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:date:from:to:cc:subject:message-id:references :mime-version:content-disposition:in-reply-to:user-agent; bh=YGGJN7U8ljuEaLT4KrMg6gCLv3fM6FVL4YX4LLtYOYg=; b=IMdFT4nikGBn+4vVrSQAYILeQkMWov4ONYqmy2B5DOecP7vzFyn4cHmusMNcGaWmGB JVaxyvSWmhMMB+DCI/w7L0dX2MbE5ZC5/7phePf0CkrjYMhTw1ANU0sMnFJyDxeqYxI8 SvZyMcB54GdGJWQcH9UNsegYoh/ZC9OfDDS41T9NToE1c8+mGlzDldvxCeCfcZRQOmNG 5iJaBkIjOihOEqK+xiqbIPdc3zTQ+Awmeqps61BnuqyqYCKItLTyKGory0eaHF/Yez42 BIo24+7HJckWiiNs+KW1CMB0xajFZi0Lk2QeOYgovQ4KqbZq8KP6n8dQkmaiMwylmtKu hq9Q== X-Gm-Message-State: APjAAAUgF+Zs+ovRybu4g8gFCF5FvADoYX5cN1Ajc98gArdcFJm3TSIc to6ByykY6dAFcyUwTOwRkUiM8Q== X-Google-Smtp-Source: APXvYqwhWFcfpcH0zdO6tq3PrxQfU9ZR5plt69byXgU38R/JaRnSu2uJ543jW7m+8UMsx67P1VHxWg== X-Received: by 2002:a05:600c:2318:: with SMTP id 24mr7497826wmo.146.1571834873656; Wed, 23 Oct 2019 05:47:53 -0700 (PDT) Received: from localhost (static-84-42-175-93.net.upcbroadband.cz. [84.42.175.93]) by smtp.gmail.com with ESMTPSA id q124sm36606858wma.5.2019.10.23.05.47.52 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Wed, 23 Oct 2019 05:47:52 -0700 (PDT) Date: Wed, 23 Oct 2019 14:47:51 +0200 From: Tomas Vondra To: Josef Machytka Cc: pgsql-bugs@lists.postgresql.org Subject: Re: memory problems and crash of db when deleting data from table with thousands of partitions Message-ID: <20191023124751.pnzcos7i3ei2vjxc@development> References: MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Disposition: inline In-Reply-To: User-Agent: NeoMutt/20180716 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk 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-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. > 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