From: Thomas Kellerer <shammat@gmx.net>
To: pgsql-admin@lists.postgresql.org
Subject: Re: Unique constraint across all partitions?
Date: Tue, 26 Jan 2021 09:48:01 +0100
Message-ID: <6a847296-d02f-6582-3dcb-e3e60dbc56b6@gmx.net> (raw)
In-Reply-To: <5ebaba7ee42d89f8bfb2599de203612024c86e8e.camel@cybertec.at>
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: shammat@gmx.net, pgsql-admin@lists.postgresql.org
Subject: Re: Unique constraint across all partitions?
In-Reply-To: <6a847296-d02f-6582-3dcb-e3e60dbc56b6@gmx.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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