On Sat, Jul 9, 2011 at 6:09 AM, Guillaume Lelarge
<guillaume@lelarge.info> wrote:
>
> To have a primary key or a unique key on an partitioned table, it would
> mean that we should be able to have one index on multiple tables.
> Because primary key and unique constraints are enforced with an index.
> That's not something easy to do, and I guess it would make the index
> bigger, which isn't performance savvy.
I don't think you necessarily have to go as far as creating
cross-table indexes. In the case I am looking at, one of the values I
partition on is part of the primary key, so collisions across
partitions can be avoided in this way.
However, the problem here is that this also makes it far more
difficult to create partitioned tables which reference keys on table
partitions because those keys have to be defined on each partition.
Simply creating per-partition indexes would be sufficient for that use
case. Otherwise it becomes relatively maintenance heavy and quite
error prone.
I agree that this wouldn't get us to what would make everyone happy,
but it would create reasonable workarounds for when one needs fkeys
against partitioned tables....
Best Wishes,
Chris Travers