Subject: Re: Unique constraint across all partitions?
To: pgsql-admin@lists.postgresql.org
References: <e46db75f-02fc-1a79-5d04-92bc5ea05216@quadratum-braccas.com>
 <3d00c1f4-646c-59b7-e4dc-1d51190d2139@gmail.com>
 <CAKFQuwZx-3A4Oqk69DTBk0MaVa9W7f=LPnmQpx1Q5Nj+=9PPVw@mail.gmail.com>
 <e91069b5-f6a8-c812-a4c9-9c5c4383cb4f@gmail.com>
 <5ebaba7ee42d89f8bfb2599de203612024c86e8e.camel@cybertec.at>
From: Thomas Kellerer <shammat@gmx.net>
Message-ID: <6a847296-d02f-6582-3dcb-e3e60dbc56b6@gmx.net>
Date: Tue, 26 Jan 2021 09:48:01 +0100
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.23)
 Gecko/20090812 Thunderbird/2.0.0.23 Mnenhy/0.7.6.666
MIME-Version: 1.0
In-Reply-To: <5ebaba7ee42d89f8bfb2599de203612024c86e8e.camel@cybertec.at>
Content-Type: text/plain; charset=utf-8
Content-Language: en-US
Content-Transfer-Encoding: quoted-printable
Precedence: bulk

Laurenz Albe schrieb am 26.01.2021 um 09:33:
>>> So, yes, so long as your unique constraint takes into
>>> consideration the partitioning scheme it will enforce uniqueness
>>> across all of the partitions. But the answer to the full, but
>>> possibly
>>> implied, question is no.
>>
>> This is why global indices are needed.
>
> Global indexes are likely more pain than gain.

While not extremely popular, many Oracle users do find them useful.

> They will make the best part of partitioning (attaching and detaching
> partitions) painfully slow.  Hardly worth it to gain a tiny little bit
> of integrity.  Within a partition, you can guarantee uniqueness easily.

I wonder why they would make the "best parts" slow?

I would expect that partition pruning or partition wise joins are unaffect=
ed
by that (which I do consider one of the "best parts")

Obviously the overhead of maintaining the index is there, especially when
dropping partitions this would mean a substantial overhead.

But it's up to the user to decide if the benefits (proper foreign keys, un=
iqueness)
outweigh the disadvantages.

Is there any other downside I am missing?

Would DML statements be affected (if you factor out the cost of maintainin=
g
the global index)?

Thomas