Received: from magus.postgresql.org ([87.238.57.229]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TERQy-0000CU-4i for pgsql-admin@postgresql.org; Wed, 19 Sep 2012 21:03:48 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TERQs-00037j-7v for pgsql-admin@postgresql.org; Wed, 19 Sep 2012 21:03:47 +0000 Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.5/8.14.5) with ESMTP id q8JL3ejm009793; Wed, 19 Sep 2012 17:03:40 -0400 (EDT) From: Tom Lane To: Michael Holt cc: "pgsql-admin@postgresql.org" Subject: Re: max_locks_per_transaction and partitioned tables In-reply-to: References: Comments: In-reply-to Michael Holt message dated "Wed, 19 Sep 2012 18:28:16 -0000" Date: Wed, 19 Sep 2012 17:03:39 -0400 Message-ID: <9791.1348088619@sss.pgh.pa.us> X-Pg-Spam-Score: -2.5 (--) X-Archive-Number: 201209/106 X-Sequence-Number: 40047 Michael Holt writes: > We've had a system in operation for a few years that makes use of a substantial amount of partitioning. The parent table now has over 4,000 children tables. Within the last couple of days the server started giving "out of shared memory" errors with the suggestion to increase the max_locks_per_transaction. > If the parent table is queried will it require a lock for each one of the child tables? I'm guessing it will. Yup, it will. I'm a bit astonished that you've gotten this far without horrid performance problems. The underlying mechanisms for inheritance aren't really designed to scale past perhaps a hundred child tables. regards, tom lane