Re: Partitioning on Table with Foreign Key

On Thu, Jan 21, 2010 at 3:46 AM, Yan Cheng Cheok <yccheok(at)yahoo(dot)com> wrote:
> table measurement will have a *lot* of row (millions). I want to speed up write and read access. Hence, I use partition technique.
>
> CREATE TABLE measurement_y2006m02 (
>    CHECK ( date >= DATE '2006-02-01' AND date < DATE '2006-03-01' )
> ) INHERITS (measurement);
>
> Opps! But measurement do not have date column. How I can refer to measurement's lot's date?
>

Split on your lot ID number instead of date, using a modulo operation.
I've done this by splitting a table 100 ways using the constraing (id
% 100 == N) for N = 0 .. 99.

We don't know what your data retention or distribution needs are, so
it is hard to offer solid advice.