From: Jim C. Nasby <decibel@decibel.org>
To: J. Andrew Rogers <jrogers@neopolitan.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Partitioning
Date: Thu, 16 Sep 2004 15:39:51 -0500
Message-ID: <20040916203951.GE56059@decibel.org> (raw)
In-Reply-To: <1095282571.25123.181.camel@vulture.corp.neopolitan.com>
References: <20040914223333.GL56059@decibel.org>
<NOEFLCFHBPDAFHEIPGBOEEHPCEAA.simon@2ndquadrant.com>
<60k6uw1cqa.fsf@dev6.int.libertyrms.info>
<4147C560.9060702@joeconway.com>
<1095282571.25123.181.camel@vulture.corp.neopolitan.com>
On Wed, Sep 15, 2004 at 02:09:31PM -0700, J. Andrew Rogers wrote:
> 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
Forgive my ignorance, but I didn't think you could have a table that
inherits from a parent not have all the columns. Or is that not what you
mean by 'you can drop columns from the table...'?
This is one advantage I see to a big UNION ALL view; if you're doing
partitioning based on unique values, you don't actually have to store
that value in the partition tables. For example,
http://stats.distributed.net has a table that details how much work each
participant did each day for each project. Storing project_id in that
table is an extra 4 bytes... doesn't sound like much until you consider
that the table has over 130M rows right now. So it would be nice to have
an easy way to partition the table based on unique project_id's and not
waste space in the partition tables on a field that will be the same for
every row (in each partition).
--
Jim C. Nasby, Database Consultant decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828
Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: decibel@decibel.org, jrogers@neopolitan.com
Subject: Re: Partitioning
In-Reply-To: <20040916203951.GE56059@decibel.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox