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

--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.
---------------------------------------------------------------