Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1n9ibK-0003nj-QV for pgsql-admin@arkaria.postgresql.org; Tue, 18 Jan 2022 07:08:18 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1n9ibJ-0004E1-DB for pgsql-admin@arkaria.postgresql.org; Tue, 18 Jan 2022 07:08:17 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1n9ibJ-0004Ds-2m for pgsql-admin@lists.postgresql.org; Tue, 18 Jan 2022 07:08:17 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1n9ibC-0003U1-LQ for pgsql-admin@lists.postgresql.org; Tue, 18 Jan 2022 07:08:16 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 20I787nc268772; Tue, 18 Jan 2022 02:08:08 -0500 From: Tom Lane To: Victor Sudakov cc: pgsql-admin@lists.postgresql.org, Ron Subject: Re: Large number of partitions of a table In-reply-to: References: <3919502.1642393668@sss.pgh.pa.us> <671340a5-1960-e5dc-1d09-4b7f36cda2c8@gmail.com> <254601.1642480213@sss.pgh.pa.us> Comments: In-reply-to Victor Sudakov message dated "Tue, 18 Jan 2022 05:53:21 +0000" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <268770.1642489687.1@sss.pgh.pa.us> Date: Tue, 18 Jan 2022 02:08:07 -0500 Message-ID: <268771.1642489687@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Victor Sudakov writes: > Tom Lane wrote: >> Well, yeah, actually. An ill-advised query will blow out your backend's >> memory consumption, potentially leading to a SIGKILL from the dreaded OOM >> killer[1] (if you're on Linux), resulting in a backend crash and cluster >> restart. > Why should "SELECT COUNT(*) FROM t" ever consume more than work_mem > even if t has 10000 partitions? Sure, COUNT(*)'s runtime memory consumption is negligible. But you're not thinking about overhead --- specifically, 1. 10000 relcache entries for the base tables. 2. If you have N indexes per table, N*10000 relcache entries for the indexes. (The planner will probably have looked at all those indexes, even if it didn't find any use for them.) 3. 10000 SeqScan plan nodes and associated rangetable entries, 4. Likewise, 10000 instances of executor per-node state. 5. 10000 lock table entries (both shared and local lock tables). 6. Probably a few per-relation things I didn't think of. Admittedly, each of those individual data structures is going to be at most in the few-kilobytes range, so this is going to total somewhere in the hundreds of megabytes, which you can probably tolerate on modern hardware --- unless some dozens of sessions are all doing the same kind of thing. Basically, this is likely to work until it doesn't ... and when it doesn't, you're going to be in the office at 3AM trying to wrangle a recalcitrant system. Just sayin'. regards, tom lane