Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 799C064FCE6 for ; Thu, 27 Nov 2008 09:31:14 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 64685-08 for ; Thu, 27 Nov 2008 09:31:11 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from ey-out-2122.google.com (ey-out-2122.google.com [74.125.78.27]) by mail.postgresql.org (Postfix) with ESMTP id 5237A64FC93 for ; Thu, 27 Nov 2008 09:31:10 -0400 (AST) Received: by ey-out-2122.google.com with SMTP id 6so417901eyi.61 for ; Thu, 27 Nov 2008 05:31:08 -0800 (PST) Received: by 10.210.34.19 with SMTP id h19mr2158910ebh.160.1227792667257; Thu, 27 Nov 2008 05:31:07 -0800 (PST) Received: from oxford.xeocode.com.enterprisedb.com ([82.16.16.210]) by mx.google.com with ESMTPS id f4sm123569nfh.27.2008.11.27.05.31.05 (version=TLSv1/SSLv3 cipher=RC4-MD5); Thu, 27 Nov 2008 05:31:06 -0800 (PST) To: "Robert Haas" Cc: "Alvaro Herrera" , "Nikhil Sontakke" , "Jaime Casanova" , "Emmanuel Cecchet" , PostgreSQL-development Subject: Re: Fwd: [PATCHES] Auto Partitioning Patch - WIP version 1 In-Reply-To: <603c8f070811270507i36e18e0bu5d461a4f957e24d3@mail.gmail.com> (Robert Haas's message of "Thu\, 27 Nov 2008 08\:07\:51 -0500") User-Agent: Gnus/5.11 (Gnus v5.11) Emacs/22.1 (gnu/linux) X-Draft-From: ("nnimap+imap.gmail.com:HACKERS" 49696) References: <3073cc9b0810220744y987173avd560a33fcb58ede5@mail.gmail.com> <490B5178.7070600@frogthinker.org> <3073cc9b0811052047o4ebe24b4vd0ab24fd3095d342@mail.gmail.com> <3073cc9b0811261335q5e35d336i33faa17eede7d417@mail.gmail.com> <603c8f070811261352ta6da0b8xcbb8aaf87f050e6e@mail.gmail.com> <3073cc9b0811261613l18e5283ft448ea74fed414dd@mail.gmail.com> <603c8f070811262009v459d0701w151c16533d4ab947@mail.gmail.com> <20081127120402.GC4586@alvh.no-ip.org> <603c8f070811270507i36e18e0bu5d461a4f957e24d3@mail.gmail.com> From: Gregory Stark Organization: EnterpriseDB Date: Thu, 27 Nov 2008 13:31:04 +0000 Message-ID: <871vwxpa7b.fsf@oxford.xeocode.com> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.41 tagged_above=0 required=5 tests=SUBJECT_FUZZY_TION=0.41 X-Spam-Level: X-Archive-Number: 200811/1814 X-Sequence-Number: 128526 "Robert Haas" writes: > CREATE PARTITION transaction_2008_11 ON transaction WHERE record_date > BETWEEN '2008-11-01' AND '2008-11-30'; I think the main advantage to a better partitioning method would be teaching Postgres about the partition key. Instead of a collection of different constraints Postgres would know that "record_date" is *always* the partition key. So it wouldn't have to be specified every time you declare a partition. > I like the idea of using table inheritance as a foundation for this > feature, but I think it's not going to be very useful for real-world > applications without cross-table indexes. Well we could add support for cross-table indexes. It's not hard from the point of low level implementation -- just include the table oid in the index pointers. Figuring out how to represent such a thing at the index description point of view would be quite tricky though. *But*... in practice I would suggest that cross-table indexes are actually very rarely useful. Having them defeats much of the advantage of partitioning in the first place. Suddenly you would not be able to instantly drop and load whole partitions. They're a big check-list item that people want to have before they partition in case they need them but then they find out that the down-sides of actually using them makes them quite useless. Postgres's current architecture actually has a big advantage over more methodical partitioning methods in this case. You can always add additional constraints on other columns even if they aren't the "real" partitioning key. So for example if you partition the invoice table by month once you close the books for a previous month you can add a constraint WHERE invoice_id < 'xxx'. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training!