On Mon, Nov 21, 2022 at 12:33:30PM +0000, Simon Riggs wrote: > On Thu, 17 Nov 2022 at 22:01, Cary Huang <cary.hu...@highgo.ca> wrote: > > > > Patch: Global Unique Index > > Let me start by expressing severe doubt on the usefulness of such a > feature, but also salute your efforts to contribute. > > > In other words, a global unique index and a regular partitioned index are > > essentially the same in terms of their storage structure except that one > > can do cross-partition uniqueness check, the other cannot. > > This is the only workable architecture, since it allows DETACH to be > feasible, which is essential.
I had trouble understanding this feature so I spent some time thinking about it. I don't think this is really a global unique index, meaning it is not one index with all the value in the index. Rather it is the enforcement of uniqueness across all of a partitioned table's indexes. I think global indexes have a limited enough use-case that this patch's approach is as close as we are going to get to it in the foreseeable future. Second, I outlined the three values of global indexes in this blog entry, based on a 2019 email thread: https://momjian.us/main/blogs/pgblog/2020.html#July_1_2020 https://www.postgresql.org/message-id/ca+tgmob_j2m2+qkwrhg2njqekmewzntfd7a6ubg34fjuzpk...@mail.gmail.com The three values are: 1. The ability to reference partitioned tables as foreign keys without requiring the partition key to be part of the foreign key reference; Postgres 12 allows such foreign keys if they match partition keys. 2. The ability to add a uniqueness constraint to a partitioned table where the unique columns are not part of the partition key. 3. The ability to index values that only appear in a few partitions, and are not part of the partition key. This patch should help with #1 and #2, but not #3. The uniqueness guarantee allows, on average, half of the partitioned table's indexes to be checked if there is a match, and all partitioned table's indexes if not. This is because once you find a match, you don't need to keep checking because the value is unique. Looking at the patch, I am unclear how the the patch prevents concurrent duplicate value insertion during the partitioned index checking. I am actually not sure how that can be done without locking all indexes or inserting placeholder entries in all indexes. (Yeah, that sounds bad, unless I am missing something.) -- Bruce Momjian <br...@momjian.us> https://momjian.us EDB https://enterprisedb.com Embrace your flaws. They make you human, rather than perfect, which you will never be.