Thread: Unique constraint across all partitions?
All; Is it possible to enforce a unique constraint across all partitions for either declarative or inheritance based partitioning? Preferably declarative based. Thanks
On 1/25/21 4:44 PM, S Bob wrote: > All; > > > Is it possible to enforce a unique constraint across all partitions for > either declarative or inheritance based partitioning? Preferably > declarative based. You can create unique indexes on parent tables. Or am I misunderstanding you? -- Angular momentum makes the world go 'round.
On 1/25/21 4:44 PM, S Bob wrote:
> Is it possible to enforce a unique constraint across all partitions for
> either declarative or inheritance based partitioning? Preferably
> declarative based.
You can create unique indexes on parent tables. Or am I misunderstanding you?
On Mon, Jan 25, 2021 at 4:02 PM Ron <ronljohnsonjr@gmail.com> wrote:On 1/25/21 4:44 PM, S Bob wrote:
> Is it possible to enforce a unique constraint across all partitions for
> either declarative or inheritance based partitioning? Preferably
> declarative based.
You can create unique indexes on parent tables. Or am I misunderstanding you?You seem to misunderstand how partitions and unique constraints work.Each partition gets its own copy of the unique index/constraint and ensures that duplicates do not appear therein. If the specification for the unique constraint is something like "id bigint" then the value 5 can appear on multiple partitions just fine - the partition key(s) effectively become part of the unique constraint/index even though the values are not actually stored in the index.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.
Angular momentum makes the world go 'round.
On Mon, 2021-01-25 at 18:09 -0600, Ron wrote: > > > > Is it possible to enforce a unique constraint across all partitions for > > > > either declarative or inheritance based partitioning? Preferably > > > > declarative based. > > > > So, yes, so long as your unique constraint takes into consideration the partitioning scheme it will enforce uniquenessacross 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. 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. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
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 unaffected 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, uniqueness) outweigh the disadvantages. Is there any other downside I am missing? Would DML statements be affected (if you factor out the cost of maintaining the global index)? Thomas
> Global indexes are likely more pain than gain.
While not extremely popular, many Oracle users do find them useful.
I've heard and been advised many times exactly the opposite for ORACLE,
especially when performance is important.
Global indexes makes a 15-minute maintenance job into a 9-hour downtime experience.
But why use global indexes?
Oracle also has local partitioned indexes which are just like we would like to have in PostgreSQL.
From: Victor Yegorov <vyegorov@gmail.com>
Sent: tiistai 26. tammikuuta 2021 11.54
To: Thomas Kellerer <shammat@gmx.net>
Cc: pgsql-admin@lists.postgresql.org
Subject: Re: Unique constraint across all partitions?
вт, 26 янв. 2021 г. в 09:48, Thomas Kellerer <shammat@gmx.net>:
> Global indexes are likely more pain than gain.
While not extremely popular, many Oracle users do find them useful.
I've heard and been advised many times exactly the opposite for ORACLE,
especially when performance is important.
Global indexes makes a 15-minute maintenance job into a 9-hour downtime experience.
--
Victor Yegorov
Kanninen Anssi EXT schrieb am 26.01.2021 um 11:09: > But why use global indexes? To have unique constraints for values that should be unique across all partitions. That also allows foreign keys _referencing_ the partitioned table.
Hi all,
Global indexes are useful in Oracle for performance when indexed columns are different from partitioning key. An “index scan” (well it is named “range scan” in Oracle) using a local index has to navigate through all index partitions in that case. For a global index, it is only one tree to scan and this is more efficient than doing the same for a lot of them.
Regards
Michel SALAIS
Consultant Oracle, PostgreSQL
MSYM
23 rue du Départ
Boîte 37
75014 Paris
+33 6 68 94 19 60
De : Kanninen Anssi EXT <Anssi.Kanninen@digia.com>
Envoyé : mardi 26 janvier 2021 11:09
À : pgsql-admin@lists.postgresql.org
Objet : RE: Unique constraint across all partitions?
But why use global indexes?
Oracle also has local partitioned indexes which are just like we would like to have in PostgreSQL.
From: Victor Yegorov <vyegorov@gmail.com>
Sent: tiistai 26. tammikuuta 2021 11.54
To: Thomas Kellerer <shammat@gmx.net>
Cc: pgsql-admin@lists.postgresql.org
Subject: Re: Unique constraint across all partitions?
вт, 26 янв. 2021 г. в 09:48, Thomas Kellerer <shammat@gmx.net>:
> Global indexes are likely more pain than gain.
While not extremely popular, many Oracle users do find them useful.
I've heard and been advised many times exactly the opposite for ORACLE,
especially when performance is important.
Global indexes makes a 15-minute maintenance job into a 9-hour downtime experience.
--
Victor Yegorov
On Mon, 2021-01-25 at 18:09 -0600, Ron wrote:Is it possible to enforce a unique constraint across all partitions for either declarative or inheritance based partitioning? Preferably declarative based.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. 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.
Don't make my decisions for me.
When a PK or FK without an embedded partition key are needed, you bite the bullet and take the 9 hour downtime. (A legacy RDBMS I occasionally still maintain has had them for 25 years. What's just as useful are indices partitioned on keys which have nothing to do with the table's partition key.)
Angular momentum makes the world go 'round.