X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id ED0C6569B5 for ; Tue, 22 Mar 2005 11:29:47 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 86687-04 for ; Tue, 22 Mar 2005 11:29:44 +0000 (GMT) Received: from fuji.krosing.net (joltid-gw.joltid.org [195.50.194.24]) by svr1.postgresql.org (Postfix) with ESMTP id 4D23C569AE for ; Tue, 22 Mar 2005 11:29:43 +0000 (GMT) Received: from fuji.krosing.net (localhost [127.0.0.1]) by fuji.krosing.net (8.13.1/8.13.1) with ESMTP id j2LINKmj005821; Mon, 21 Mar 2005 20:23:21 +0200 Received: (from hannu@localhost) by fuji.krosing.net (8.13.1/8.13.1/Submit) id j2LINGTQ005820; Mon, 21 Mar 2005 20:23:16 +0200 X-Authentication-Warning: fuji.krosing.net: hannu set sender to hannu@tm.ee using -f Subject: Re: What needs to be done for real Partitioning? From: Hannu Krosing To: Tom Lane Cc: Josh Berkus , pgsql-performance@postgresql.org In-Reply-To: <3375.1111276999@sss.pgh.pa.us> References: <200503191202.38089.josh@agliodbs.com> <18168.1111265368@sss.pgh.pa.us> <200503191529.51794.josh@agliodbs.com> <3375.1111276999@sss.pgh.pa.us> Content-Type: text/plain Content-Transfer-Encoding: 7bit Date: Mon, 21 Mar 2005 20:23:15 +0200 Message-Id: <1111429395.4675.38.camel@fuji.krosing.net> Mime-Version: 1.0 X-Mailer: Evolution 2.0.4 (2.0.4-1) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.05 tagged_above=0 required=5 tests=FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200503/436 X-Sequence-Number: 11141 On L, 2005-03-19 at 19:03 -0500, Tom Lane wrote: > Josh Berkus writes: > >>> -- INSERT INTO should automatically create new partitions where necessary > >>> -- DELETE FROM should automatically drop empty partitions > >> > >> I am not sure I agree with either of those, and the reason is that they > >> would turn low-lock operations into high-lock operations. > > > For INSERT, I think that's a problem we need to work through. > > Possibly, but I'm concerned about locking and deadlock issues. The > reason that this is iffy is you would start the operation with only > an INSERT-grade lock, and then discover that you needed to add a > partition, which is surely something that needs an exclusive-grade > lock (consider two sessions trying to add the same partition at the > same time). So I don't see how to do it without lock upgrading, > and lock upgrading is always a recipe for deadlocks. > > The DELETE case is even worse because you can't physically release > storage until you're sure nothing in it is needed anymore by any open > transaction --- that introduces VACUUM-like issues as well as the > deadlock problem. > If we go with my proposal (other post in this thread) of doing most of the partitioning in the level between logical file and physikal 1Gb storage files, then adding a partition should be nearly the same as crossing the 1G boundary is now. removing the partition would be just plain vacuum (if we can make pg shring each 1G subfile independently) > ---------------------------(end of broadcast)--------------------------- > TIP 8: explain analyze is your friend -- Hannu Krosing