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 DE21C329EC8 for ; Wed, 15 Sep 2004 06:02:43 +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 62201-04 for ; Wed, 15 Sep 2004 05:02:41 +0000 (GMT) Received: from gp.word-to-the-wise.com (gp.word-to-the-wise.com [64.71.176.18]) by svr1.postgresql.org (Postfix) with ESMTP id 540A6329D58 for ; Wed, 15 Sep 2004 06:02:41 +0100 (BST) Received: by gp.word-to-the-wise.com (Postfix, from userid 500) id 11910900014; Tue, 14 Sep 2004 22:10:04 -0700 (PDT) Date: Tue, 14 Sep 2004 22:10:04 -0700 From: Steve Atkins To: pgsql-performance@postgresql.org Subject: Re: Data Warehouse Reevaluation - MySQL vs Postgres -- merge tables Message-ID: <20040915051003.GA32043@gp.word-to-the-wise.com> References: <20040914223333.GL56059@decibel.org> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <20040914223333.GL56059@decibel.org> User-Agent: Mutt/1.4.1i 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/135 X-Sequence-Number: 8263 On Tue, Sep 14, 2004 at 05:33:33PM -0500, Jim C. Nasby wrote: > On Mon, Sep 13, 2004 at 11:07:35PM +0100, Simon Riggs wrote: > > PostgreSQL's functionality is in many ways similar to Oracle Partitioning. > > > > Loading up your data in many similar tables, then creating a view like: > > > > CREATE VIEW BIGTABLE (idate, col1, col2, col3...) AS > > SELECT 200409130800, col1, col2, col3... FROM table200409130800 > > UNION ALL > > SELECT 200409131000, col1, col2, col3... FROM table200409131000 > > UNION ALL > > SELECT 200409131200, col1, col2, col3... FROM table200409131200 > > ...etc... [...] > > Is there by any chance a set of functions to manage adding and removing > partitions? Certainly this can be done by hand, but having a set of > tools would make life much easier. I just looked but didn't see anything > on GBorg. I've done a similar thing with time-segregated data by inheriting all the partition tables from an (empty) parent table. Adding a new partition is just a "create table tablefoo () inherits(bigtable)" and removing a partition just "drop table tablefoo". Cheers, Steve