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 03E46329ECF for ; Wed, 15 Sep 2004 22:09:39 +0100 (BST) 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 09893-03 for ; Wed, 15 Sep 2004 21:09:32 +0000 (GMT) Received: from mx1.neopolitan.us (mx1.neopolitan.us [65.87.16.224]) by svr1.postgresql.org (Postfix) with ESMTP id E7EDB329F01 for ; Wed, 15 Sep 2004 22:09:32 +0100 (BST) Received: from [65.87.16.98] (HELO vulture.corp.neopolitan.com) by mx1.neopolitan.us (CommuniGate Pro SMTP 4.1.8) with ESMTP-TLS id 5871740 for pgsql-performance@postgresql.org; Wed, 15 Sep 2004 14:09:32 -0700 Subject: Partitioning From: "J. Andrew Rogers" To: pgsql-performance@postgresql.org In-Reply-To: <4147C560.9060702@joeconway.com> References: <20040914223333.GL56059@decibel.org> <60k6uw1cqa.fsf@dev6.int.libertyrms.info> <4147C560.9060702@joeconway.com> Content-Type: text/plain Organization: Message-Id: <1095282571.25123.181.camel@vulture.corp.neopolitan.com> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.2.2 (1.2.2-5) Date: 15 Sep 2004 14:09:31 -0700 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200409/147 X-Sequence-Number: 8275 On Tue, 2004-09-14 at 21:30, Joe Conway wrote: > That's exactly what we're doing, but using inherited tables instead of a > union view. With inheritance, there is no need to rebuild the view each > time a table is added or removed. Basically, in our application, tables > are partitioned by either month or week, depending on the type of data > involved, and queries are normally date qualified. We do something very similar, also using table inheritance and a lot of triggers to automatically generate partitions and so forth. It works pretty well, but it is a custom job every time I want to implement a partitioned table. You can save a lot on speed and space if you use it to break up large tables with composite indexes, since you can drop columns from the table depending on how you use it. A big part of performance gain is that the resulting partitions end up being more well-ordered than the non-partitioned version, since inserts are hashed to different partition according to the key and hash function. It is kind of like a cheap and dirty real-time CLUSTER operation. It also lets you truncate, lock, and generally be heavy-handed with subsets of the table without affecting the rest of the table. I think generic table partitioning could pretty much be built on top of existing capabilities with a small number of tweaks. The main difference would be the ability to associate a partitioning hash function with a table (probably defined inline at CREATE TABLE time). Something with syntax like: ...PARTITION ON 'date_trunc(''hour'',ts)'... There would also probably need to be some type of metadata table to associate specific hashes with partition table names. Other than that, the capabilities largely already exist, and managing the partition hashing and association is the ugly part when rolling your own. Intercepting DML when necessary and making it behave correctly is already pretty easy, but could probably be streamlined. j. andrew rogers