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 187E9569C1 for ; Tue, 22 Mar 2005 11:30:13 +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 86714-07 for ; Tue, 22 Mar 2005 11:30:03 +0000 (GMT) Received: from fuji.krosing.net (joltid-gw.joltid.org [195.50.194.24]) by svr1.postgresql.org (Postfix) with ESMTP id C0FFD563F1 for ; Tue, 22 Mar 2005 11:30:02 +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 j2LG7dlp005623; Mon, 21 Mar 2005 18:07:39 +0200 Received: (from hannu@localhost) by fuji.krosing.net (8.13.1/8.13.1/Submit) id j2LG7Q7w005618; Mon, 21 Mar 2005 18:07:26 +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: Alvaro Herrera , PFC , Josh Berkus , pgsql-performance@postgresql.org In-Reply-To: <4507.1111294073@sss.pgh.pa.us> References: <200503191202.38089.josh@agliodbs.com> <20050319225624.GA8467@dcc.uchile.cl> <3404.1111277153@sss.pgh.pa.us> <20050320042917.GA25831@dcc.uchile.cl> <4507.1111294073@sss.pgh.pa.us> Content-Type: text/plain Content-Transfer-Encoding: 7bit Date: Mon, 21 Mar 2005 18:07:25 +0200 Message-Id: <1111421245.4675.8.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=AWL, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200503/437 X-Sequence-Number: 11142 On L, 2005-03-19 at 23:47 -0500, Tom Lane wrote: > Alvaro Herrera writes: > > Hmm. You are right, but without that we won't be able to enforce > > uniqueness on the partitioned table (we could only enforce it on each > > partition, which would mean we can't partition on anything else than > > primary keys if the tables have one). IMHO this is something to > > consider. > > Well, partitioning on the primary key would be Good Enough for 95% or > 99% of the real problems out there. I'm not excited about adding a > large chunk of complexity to cover another few percent. That automatically means that partitioning expression has to be a range over PK. (you dont want to have every tuple in separate tabel :) And it also means that you have to automatically create new partitions. Are you sure that partitioning on anything else than PK would be significantly harder ? I have a case where I do manual partitioning over start_time (timestamp), but the PK is an id from a sequence. They are almost, but not exactly in the same order. And I don't think that moving the PK to be (start_time, id) just because of "partitioning on PK only" would be a good design in any way. So please don't design the system to partition on PK only. -- Hannu Krosing