pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedUnique constraint across all partitions?
11+ messages / 8 participants
[nested] [flat]
* Unique constraint across all partitions?
@ 2021-01-25 22:44 S Bob <sbob@quadratum-braccas.com>
2021-01-25 23:02 ` Re: Unique constraint across all partitions? Ron <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: S Bob @ 2021-01-25 22:44 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
All;
Is it possible to enforce a unique constraint across all partitions for
either declarative or inheritance based partitioning? Preferably
declarative based.
Thanks
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Unique constraint across all partitions?
2021-01-25 22:44 Unique constraint across all partitions? S Bob <sbob@quadratum-braccas.com>
@ 2021-01-25 23:02 ` Ron <ronljohnsonjr@gmail.com>
2021-01-26 00:01 ` Re: Unique constraint across all partitions? David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Ron @ 2021-01-25 23:02 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Unique constraint across all partitions?
2021-01-25 22:44 Unique constraint across all partitions? S Bob <sbob@quadratum-braccas.com>
2021-01-25 23:02 ` Re: Unique constraint across all partitions? Ron <ronljohnsonjr@gmail.com>
@ 2021-01-26 00:01 ` David G. Johnston <david.g.johnston@gmail.com>
2021-01-26 00:09 ` Re: Unique constraint across all partitions? Ron <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: David G. Johnston @ 2021-01-26 00:01 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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.
David J.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Unique constraint across all partitions?
2021-01-25 22:44 Unique constraint across all partitions? S Bob <sbob@quadratum-braccas.com>
2021-01-25 23:02 ` Re: Unique constraint across all partitions? Ron <ronljohnsonjr@gmail.com>
2021-01-26 00:01 ` Re: Unique constraint across all partitions? David G. Johnston <david.g.johnston@gmail.com>
@ 2021-01-26 00:09 ` Ron <ronljohnsonjr@gmail.com>
2021-01-26 08:33 ` Re: Unique constraint across all partitions? Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 11+ messages in thread
From: Ron @ 2021-01-26 00:09 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
On 1/25/21 6:01 PM, David G. Johnston wrote:
> On Mon, Jan 25, 2021 at 4:02 PM Ron <ronljohnsonjr@gmail.com
> <mailto: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.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Unique constraint across all partitions?
2021-01-25 22:44 Unique constraint across all partitions? S Bob <sbob@quadratum-braccas.com>
2021-01-25 23:02 ` Re: Unique constraint across all partitions? Ron <ronljohnsonjr@gmail.com>
2021-01-26 00:01 ` Re: Unique constraint across all partitions? David G. Johnston <david.g.johnston@gmail.com>
2021-01-26 00:09 ` Re: Unique constraint across all partitions? Ron <ronljohnsonjr@gmail.com>
@ 2021-01-26 08:33 ` Laurenz Albe <laurenz.albe@cybertec.at>
2021-01-26 08:48 ` Re: Unique constraint across all partitions? Thomas Kellerer <shammat@gmx.net>
2021-01-26 14:21 ` Re: Unique constraint across all partitions? Ron <ronljohnsonjr@gmail.com>
0 siblings, 2 replies; 11+ messages in thread
From: Laurenz Albe @ 2021-01-26 08:33 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
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.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Unique constraint across all partitions?
2021-01-25 22:44 Unique constraint across all partitions? S Bob <sbob@quadratum-braccas.com>
2021-01-25 23:02 ` Re: Unique constraint across all partitions? Ron <ronljohnsonjr@gmail.com>
2021-01-26 00:01 ` Re: Unique constraint across all partitions? David G. Johnston <david.g.johnston@gmail.com>
2021-01-26 00:09 ` Re: Unique constraint across all partitions? Ron <ronljohnsonjr@gmail.com>
2021-01-26 08:33 ` Re: Unique constraint across all partitions? Laurenz Albe <laurenz.albe@cybertec.at>
@ 2021-01-26 08:48 ` Thomas Kellerer <shammat@gmx.net>
2021-01-26 09:54 ` Re: Unique constraint across all partitions? Victor Yegorov <vyegorov@gmail.com>
1 sibling, 1 reply; 11+ messages in thread
From: Thomas Kellerer @ 2021-01-26 08:48 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Unique constraint across all partitions?
2021-01-25 22:44 Unique constraint across all partitions? S Bob <sbob@quadratum-braccas.com>
2021-01-25 23:02 ` Re: Unique constraint across all partitions? Ron <ronljohnsonjr@gmail.com>
2021-01-26 00:01 ` Re: Unique constraint across all partitions? David G. Johnston <david.g.johnston@gmail.com>
2021-01-26 00:09 ` Re: Unique constraint across all partitions? Ron <ronljohnsonjr@gmail.com>
2021-01-26 08:33 ` Re: Unique constraint across all partitions? Laurenz Albe <laurenz.albe@cybertec.at>
2021-01-26 08:48 ` Re: Unique constraint across all partitions? Thomas Kellerer <shammat@gmx.net>
@ 2021-01-26 09:54 ` Victor Yegorov <vyegorov@gmail.com>
2021-01-26 10:09 ` RE: Unique constraint across all partitions? Kanninen Anssi EXT <Anssi.Kanninen@digia.com>
0 siblings, 1 reply; 11+ messages in thread
From: Victor Yegorov @ 2021-01-26 09:54 UTC (permalink / raw)
To: Thomas Kellerer <shammat@gmx.net>; +Cc: pgsql-admin@lists.postgresql.org
вт, 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
^ permalink raw reply [nested|flat] 11+ messages in thread
* RE: Unique constraint across all partitions?
2021-01-25 22:44 Unique constraint across all partitions? S Bob <sbob@quadratum-braccas.com>
2021-01-25 23:02 ` Re: Unique constraint across all partitions? Ron <ronljohnsonjr@gmail.com>
2021-01-26 00:01 ` Re: Unique constraint across all partitions? David G. Johnston <david.g.johnston@gmail.com>
2021-01-26 00:09 ` Re: Unique constraint across all partitions? Ron <ronljohnsonjr@gmail.com>
2021-01-26 08:33 ` Re: Unique constraint across all partitions? Laurenz Albe <laurenz.albe@cybertec.at>
2021-01-26 08:48 ` Re: Unique constraint across all partitions? Thomas Kellerer <shammat@gmx.net>
2021-01-26 09:54 ` Re: Unique constraint across all partitions? Victor Yegorov <vyegorov@gmail.com>
@ 2021-01-26 10:09 ` Kanninen Anssi EXT <Anssi.Kanninen@digia.com>
2021-01-26 11:08 ` Re: Unique constraint across all partitions? Thomas Kellerer <shammat@gmx.net>
2021-01-26 11:13 ` RE: Unique constraint across all partitions? Michel SALAIS <msalais@msym.fr>
0 siblings, 2 replies; 11+ messages in thread
From: Kanninen Anssi EXT @ 2021-01-26 10:09 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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<mailto: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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Unique constraint across all partitions?
2021-01-25 22:44 Unique constraint across all partitions? S Bob <sbob@quadratum-braccas.com>
2021-01-25 23:02 ` Re: Unique constraint across all partitions? Ron <ronljohnsonjr@gmail.com>
2021-01-26 00:01 ` Re: Unique constraint across all partitions? David G. Johnston <david.g.johnston@gmail.com>
2021-01-26 00:09 ` Re: Unique constraint across all partitions? Ron <ronljohnsonjr@gmail.com>
2021-01-26 08:33 ` Re: Unique constraint across all partitions? Laurenz Albe <laurenz.albe@cybertec.at>
2021-01-26 08:48 ` Re: Unique constraint across all partitions? Thomas Kellerer <shammat@gmx.net>
2021-01-26 09:54 ` Re: Unique constraint across all partitions? Victor Yegorov <vyegorov@gmail.com>
2021-01-26 10:09 ` RE: Unique constraint across all partitions? Kanninen Anssi EXT <Anssi.Kanninen@digia.com>
@ 2021-01-26 11:08 ` Thomas Kellerer <shammat@gmx.net>
1 sibling, 0 replies; 11+ messages in thread
From: Thomas Kellerer @ 2021-01-26 11:08 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 11+ messages in thread
* RE: Unique constraint across all partitions?
2021-01-25 22:44 Unique constraint across all partitions? S Bob <sbob@quadratum-braccas.com>
2021-01-25 23:02 ` Re: Unique constraint across all partitions? Ron <ronljohnsonjr@gmail.com>
2021-01-26 00:01 ` Re: Unique constraint across all partitions? David G. Johnston <david.g.johnston@gmail.com>
2021-01-26 00:09 ` Re: Unique constraint across all partitions? Ron <ronljohnsonjr@gmail.com>
2021-01-26 08:33 ` Re: Unique constraint across all partitions? Laurenz Albe <laurenz.albe@cybertec.at>
2021-01-26 08:48 ` Re: Unique constraint across all partitions? Thomas Kellerer <shammat@gmx.net>
2021-01-26 09:54 ` Re: Unique constraint across all partitions? Victor Yegorov <vyegorov@gmail.com>
2021-01-26 10:09 ` RE: Unique constraint across all partitions? Kanninen Anssi EXT <Anssi.Kanninen@digia.com>
@ 2021-01-26 11:13 ` Michel SALAIS <msalais@msym.fr>
1 sibling, 0 replies; 11+ messages in thread
From: Michel SALAIS @ 2021-01-26 11:13 UTC (permalink / raw)
To: 'Kanninen Anssi EXT' <Anssi.Kanninen@digia.com>; pgsql-admin@lists.postgresql.org
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
<mailto:msalais@msym.fr> msalais@msym.fr
<http://www.msym.fr/; www.msym.fr
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 <mailto:vyegorov@gmail.com> >
Sent: tiistai 26. tammikuuta 2021 11.54
To: Thomas Kellerer <shammat@gmx.net <mailto:shammat@gmx.net> >
Cc: pgsql-admin@lists.postgresql.org <mailto:pgsql-admin@lists.postgresql.org>
Subject: Re: Unique constraint across all partitions?
вт, 26 янв. 2021 г. в 09:48, Thomas Kellerer <shammat@gmx.net <mailto: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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Unique constraint across all partitions?
2021-01-25 22:44 Unique constraint across all partitions? S Bob <sbob@quadratum-braccas.com>
2021-01-25 23:02 ` Re: Unique constraint across all partitions? Ron <ronljohnsonjr@gmail.com>
2021-01-26 00:01 ` Re: Unique constraint across all partitions? David G. Johnston <david.g.johnston@gmail.com>
2021-01-26 00:09 ` Re: Unique constraint across all partitions? Ron <ronljohnsonjr@gmail.com>
2021-01-26 08:33 ` Re: Unique constraint across all partitions? Laurenz Albe <laurenz.albe@cybertec.at>
@ 2021-01-26 14:21 ` Ron <ronljohnsonjr@gmail.com>
1 sibling, 0 replies; 11+ messages in thread
From: Ron @ 2021-01-26 14:21 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
On 1/26/21 2:33 AM, Laurenz Albe wrote:
> 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.
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2021-01-26 14:21 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-01-25 22:44 Unique constraint across all partitions? S Bob <sbob@quadratum-braccas.com>
2021-01-25 23:02 ` Ron <ronljohnsonjr@gmail.com>
2021-01-26 00:01 ` David G. Johnston <david.g.johnston@gmail.com>
2021-01-26 00:09 ` Ron <ronljohnsonjr@gmail.com>
2021-01-26 08:33 ` Laurenz Albe <laurenz.albe@cybertec.at>
2021-01-26 08:48 ` Thomas Kellerer <shammat@gmx.net>
2021-01-26 09:54 ` Victor Yegorov <vyegorov@gmail.com>
2021-01-26 10:09 ` Kanninen Anssi EXT <Anssi.Kanninen@digia.com>
2021-01-26 11:08 ` Thomas Kellerer <shammat@gmx.net>
2021-01-26 11:13 ` Michel SALAIS <msalais@msym.fr>
2021-01-26 14:21 ` Ron <ronljohnsonjr@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox