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 1oyiYR-0007Cs-Cs for pgsql-general@arkaria.postgresql.org; Fri, 25 Nov 2022 23:56:23 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1oyiYQ-0001Cm-91 for pgsql-general@arkaria.postgresql.org; Fri, 25 Nov 2022 23:56:22 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oyiYP-0001Cc-Uv for pgsql-general@lists.postgresql.org; Fri, 25 Nov 2022 23:56:21 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oyiYN-0001Zv-BX for pgsql-general@lists.postgresql.org; Fri, 25 Nov 2022 23:56:20 +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 2APNuHhB025926; Fri, 25 Nov 2022 18:56:17 -0500 From: Tom Lane To: Ron cc: pgsql-general@lists.postgresql.org Subject: Re: Practical maximum max_locks_per_transaction? In-reply-to: References: Comments: In-reply-to Ron message dated "Fri, 25 Nov 2022 17:45:42 -0600" MIME-Version: 1.0 Content-Type: text/plain; charset="UTF-8" Content-ID: <25924.1669420577.1@sss.pgh.pa.us> Content-Transfer-Encoding: quoted-printable Date: Fri, 25 Nov 2022 18:56:17 -0500 Message-ID: <25925.1669420577@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Ron writes: > How "raised" is too raised?=C2=A0 I just had to increase = > max_locks_per_transaction from 640 to 1024 on an instance with many chil= d = > tables and against which is run many large reports.=C2=A0 Another instan= ce has = > zero children, but had to increase the value to 1280 to prevent reports = from = > failing. max_locks_per_transaction in isolation means nothing. What counts is the product max_locks_per_transaction * max_connections, which is (more or less) the number of slots allocated in the shared lock table. Having said that, you can probably make it as big as you want on any reasonably modern machine. I wouldn't blink at a few million locktable entries, at least not on 64-bit hardware. regards, tom lane