On Wed, 2007-12-12 at 11:22 -0500, Andrew Sullivan wrote:
> On Wed, Dec 12, 2007 at 12:14:43PM +0100, Zeugswetter Andreas ADI SD wrote:
> > Uniqueness is currently perfectly practical, when the unique index
> > contains
> > the column[s] that is/are used in a non overlapping partitioning scheme.
>
> Well, yes, assuming you have no bugs. Part of the reason I want the
> database to handle this for me is because, where I've come from, the only
> thing I can be sure of is that there will be bugs. There'll even be bugs
> before there is running code. One bug I can easily imagine is that the
> non-overlapping partitioning scheme has a bug in it, such that it turns out
> there _is_ an overlap some time.
Enforcing uniqueness with a global index has a number of disadvantages.
The worst of these is that the index continues to get bigger and bigger
as the total data volume increases. You have to index all partitions,
plus each index entry needs to include a partition id as well as the
index key. So not only is it big, its huge. Huge indexes are slow, so an
index with terabytes of data in it is going to be almost unusable.
The best thing to do would be to sit down and work out exactly how big
and deep such an index would be in the case you're thinking of so we can
tell whether it is very bad or merely bad.
I seem to be the only one saying global indexes are bad, so if people
that want them can do the math and honestly say they want them, then I
will listen.
-- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com
