Skip site navigation (1) Skip section navigation (2)

Partitioning (was: Re: INHERIT and FOREIGN KEY issues)

From: Daniel Staal <DStaal(at)usa(dot)net>
To: Kedar Rasik Parikh <kedarr(at)netcore(dot)co(dot)in>, pgsql-novice(at)postgresql(dot)org
Subject: Partitioning (was: Re: INHERIT and FOREIGN KEY issues)
Date: 2009-05-25 07:06:15
Message-ID: 787C803C15BB9DD6AB21E481@Mac-Pro.magehandbook.com (view raw or flat)
Thread:
Lists: pgsql-novice
--As of May 25, 2009 12:57:51 AM +0530, Kedar Rasik Parikh is alleged to 
have said:

> We use partitioning and inheritance to a great extent to manage our
> massive tables, I just hope that partitioning will be as simple,
> effective and free from hacks and work around as it is in Oracle.
>
> I love postgres otherwise.

--As for the rest, it is mine.

Partitioning under Postgres is simple, and works fairly well from what I've 
seen, with one big cravat:

Partitioning and foreign keys do not mix.  You can create a foreign key 
from one partitioned table to another table, or to a specific partition, 
but not to the top level of a partitioned table.  Not and have it work, 
anyway.  (The key will see only the top-level table, which you've probably 
got set up to be empty.)

Basically, figure you can create a foreign key from a partitioned table, 
but not one that references one.  (At least, not without hackery.)

There are a couple of other things that you'll probably have to do 
manually, (keys in general don't inherit, so you'll want to re-create them 
on each partition, and the query planner may not see that a constraint is 
being satisfied if it's not directly listed in the where clause) but that's 
the big hole in Postgres' partitioning at the moment, as far as I can see.

Daniel T. Staal

---------------------------------------------------------------
This email copyright the author.  Unless otherwise noted, you
are expressly allowed to retransmit, quote, or otherwise use
the contents for non-commercial purposes.  This copyright will
expire 5 years after the author's death, or in 30 years,
whichever is longer, unless such a period is in excess of
local copyright law.
---------------------------------------------------------------

In response to

pgsql-novice by date

Next:From: Luiz Eduardo Cantanhede NeriDate: 2009-05-25 10:44:36
Subject: Tool for modeling
Previous:From: TanuwijayaDate: 2009-05-25 03:03:38
Subject: cannot install postgres

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group