Patch: Global Unique Index

Hello
Do we need new syntax actually? I think that a global unique index can be created automatically instead of raising an error "unique constraint on partitioned table must include all partitioning columns"

regards, Sergei

pá 18. 11. 2022 v 10:04 odesílatel Sergei Kornilov <sk@zsrv.org> napsal:

Hello
Do we need new syntax actually? I think that a global unique index can be
created automatically instead of raising an error "unique constraint on
partitioned table must include all partitioning columns"

+1

Pavel

Show quoted text

regards, Sergei

Sergei Kornilov <sk@zsrv.org> writes:

Do we need new syntax actually? I think that a global unique index can be created automatically instead of raising an error "unique constraint on partitioned table must include all partitioning columns"

I'm not convinced that we want this feature at all: as far as I can see,
it will completely destroy the benefits of making a partitioned table
in the first place. But if we do want it, I don't think it should be
so easy to create a global index by accident as that syntax approach
would make it. I think there needs to be a pretty clear YES I WANT TO
SHOOT MYSELF IN THE FOOT clause in the command.

regards, tom lane

pá 18. 11. 2022 v 16:06 odesílatel Tom Lane <tgl@sss.pgh.pa.us> napsal:

Sergei Kornilov <sk@zsrv.org> writes:

Do we need new syntax actually? I think that a global unique index can

be created automatically instead of raising an error "unique constraint on
partitioned table must include all partitioning columns"

I'm not convinced that we want this feature at all: as far as I can see,
it will completely destroy the benefits of making a partitioned table
in the first place. But if we do want it, I don't think it should be
so easy to create a global index by accident as that syntax approach
would make it. I think there needs to be a pretty clear YES I WANT TO
SHOOT MYSELF IN THE FOOT clause in the command.

isn't possible to have a partitioned index?

https://www.highgo.ca/2022/10/14/global-index-a-different-approach/

Regards

Pavel

Show quoted text

regards, tom lane

On Thu, 17 Nov 2022 at 22:01, Cary Huang <cary.huang@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.

You don't seem to mention that this would require a uniqueness check
on each partition. Is that correct? This would result in O(N) cost of
uniqueness checks, severely limiting load speed. I notice you don't
offer any benchmarks on load speed or the overhead associated with
this, which is not good if you want to be taken seriously, but at
least it is recoverable.

(It might be necessary to specify some partitions as READ ONLY, to
allow us to record their min/max values for the indexed cols, allowing
us to do this more quickly.)

- Supported Features -
1. Global unique index is supported only on btree index type

Why? Surely any index type that supports uniqueness is good.

- Not-supported Features -
1. Global uniqueness check with Sub partition tables is not yet supported as we do not have immediate use case and it may involve majoy change in current implementation

Hmm, sounds like a problem. Arranging the calls recursively should work.

- Create a global unique index -
To create a regular unique index on a partitioned table, Postgres has to perform heap scan and sorting on every child partition. Uniqueness check happens during the sorting phase and will raise an error if multiple tuples with the same index key are sorted together. To achieve global uniqueness check, we make Postgres perform the sorting after all of the child partitions have been scanned instead of on the "sort per partition" fashion. In otherwords, the sorting only happens once at the very end and it sorts the tuples coming from all the partitions and therefore can ensure global uniqueness.

My feeling is that performance on this will suck so badly that we must
warn people away from it, and tell people if they want this, create
the index at the start and let it load.

Hopefully CREATE INDEX CONCURRENTLY still works.

Let's see some benchmarks on this also please.

You'll need to think about progress reporting early because correctly
reporting the progress and expected run times are likely critical for
usability.

Example:

CREATE TABLE gidx_part (a int, b int, c text) PARTITION BY RANGE (a);
CREATE TABLE gidx_part1 partition of gidx_part FOR VALUES FROM (0) TO (10);
CREATE TABLE gidx_part2 partition of gidx_part FOR VALUES FROM (10) TO (20);
CREATE UNIQUE INDEX global_unique_idx ON gidx_part USING BTREE(b) GLOBAL;
INSERT INTO gidx_part values(5, 5, 'test');
INSERT INTO gidx_part values(15, 5, 'test');

ERROR: duplicate key value violates unique constraint "gidx_part1_b_idx"
DETAIL: Key (b)=(5) already exists.

Well done.

- DETACH -
Since we retain the same partitioned structure, detaching a partition with global unique index is straightforward. Upon DETACH, Postgres will change its relkind from RELKIND_GLOBAL_INDEX to RELKIND_INDEX and remove their inheritance relationship as usual.

It's the only way that works

- Optimizer, query planning and vacuum -
Since no major modification is done on global unique index's structure and storage, it works in the same way as a regular partitioned index. No major change is required to be done on optimizer, planner and vacuum process as they should work in the same way as regular index.

Agreed

Making a prototype is a great first step.

The next step is to understand the good and the bad aspects of it, so
you can see what else needs to be done. You need to be honest and real
about the fact that this may not actually be desirable in practice, or
in a restricted use case.

That means performance analysis of create, load, attach, detach,
INSERT, SELECT, UPD/DEL and anything else that might be affected,
together with algorithmic analysis of what happens for larger N and
larger tables.

Expect many versions; take provisions for many days.

Best of luck

--
Simon Riggs http://www.EnterpriseDB.com/

Hi Simon

Thank you so much for sharing these valuable comments and concerns to our work. We understand there is a lot of TODOs left to be done to move forward with this in a serious matter. Your comments have been very helpful and we are very grateful.

You don't seem to mention that this would require a uniqueness check

on each partition. Is that correct? This would result in O(N) cost of

uniqueness checks, severely limiting load speed. I notice you don't

offer any benchmarks on load speed or the overhead associated with

this, which is not good if you want to be taken seriously, but at

least it is recoverable.

Yes, during INSERT and UPDATE, the uniqueness check happens on every partition including the current one. This introduces extra look-up costs and will limit the speed significantly especially when there is a large number of partitions. This is one drawback of global unique index that needs to be optimized / improved.

In fact, all other operations such as CREATE and ATTACH that involve global uniqueness check will have certain degree of performance loss as well. See benchmark figures below.

(It might be necessary to specify some partitions as READ ONLY, to

allow us to record their min/max values for the indexed cols, allowing

us to do this more quickly.)

Thank you so much for this great suggestion, If there were an indication that some partitions have become READ ONLY, record the min/max values of their global unique indexed columns to these partitions, then we might be able to skip these partitions for uniqueness checking if the value is out of the range (min/max)? Did we understand it correctly? Could you help elaborate more?

1. Global unique index is supported only on btree index type

Why? Surely any index type that supports uniqueness is good.

Yes, we can definitely have the same support for other index types that support UNIQUE.

- Not-supported Features -

1. Global uniqueness check with Sub partition tables is not yet supported as we do not have immediate use case and it may involve major change in current implementation

Hmm, sounds like a problem. Arranging the calls recursively should work.

Yes, it is a matter of rearranging the recursive calls to correctly find out all "leaves" partitions to be considered for global uniqueness check. So far, only the partitions in the first layer is considered.

My feeling is that performance on this will suck so badly that we must

warn people away from it, and tell people if they want this, create

the index at the start and let it load.

Yes, to support global unique index, extra logic needs to be run to ensure uniqueness and especially during INSERT and ATTACH where it needs to look up all involved partitions. We have a benchmark figures attached below.

This is also the reason that "global" syntax is required so people know they really want to have this feature. To help users better understand the potential performance drawbacks, should we add a warning in the documentation?

Hopefully CREATE INDEX CONCURRENTLY still works.

Yes, we verified this global unique index approach on Postgres 14.5 with a community CREATE INDEX CONCURRENTLY patch on partitioned table.

Let's see some benchmarks on this also please.

Here is a simple 'timing' comparison between regular and global unique index on a partitioned table having 6 partitions.

global unique index:

-> 156,285ms to insert 6 million records (1 million on each partition)

-> 6,592ms to delete all 6 million records

-> 3,957ms to create global unique index with 6 million records pre-inserted

-> 3,650ms to attach a new partition with 1 million records pre-inserted

-> 17ms to detach a partition with 1 million records in it

regular unique index:

-> 26,007ms to insert 6 million records (1 million on each partition)

-> 7,238ms to delete all  6 million records

-> 2,933ms to create regular unique index with 6 million records pre-inserted

-> 628ms to attach a new partition with 1 million records pre-inserted

-> 17ms to detach a partition with 1 million records in it

These are the commands I use to get the numbers (switch create unique index clause between global and regular):

-> \timing on

-> create table test(a int, b int, c text) partition by range (a);

-> create table test1 partition of test for values from (MINVALUE) to (1000000);

-> create table test2 partition of test for values from (1000000) to (2000000);

-> create table test3 partition of test for values from (2000000) to (3000000);

-> create table test4 partition of test for values from (3000000) to (4000000);

-> create table test5 partition of test for values from (4000000) to (5000000);

-> create table test6 partition of test for values from (5000000) to (6000000);

-> create unique index myindex on test(b) global;

-> insert into test values(generate_series(0,5999999), generate_series(0,5999999), 'test'); /* record timing */

-> delete from test; /* record timing */

-> drop index myindex;

-> insert into test values(generate_series(0,5999999), generate_series(0,5999999), 'test');

-> create unique index myindex on test(b) global; /* record timing */

-> create table test7 (a int, b int, c text);

-> insert into test7 values(generate_series(6000000, 6999999), generate_series(6000000, 6999999), 'test');

-> alter table test attach partition test7 for values from (6000000) TO (7000000); /* record timing */

-> alter table test detach partition test7; /* record timing */

As you can see, insert operation suffers the most performance drawbacks. In fact, it takes roughly 6 times as much time to complete the insertion, which matches the number of partitions in the test.

The Attach operation also takes roughly 6 times as much time to complete, because it has to performs uniqueness check on all 6 existing partitions to determine global uniqueness. Detach in both case takes the same time to complete.

Create global unique index takes 35% longer to build.

We also ran some tests for random SELECT and UPDATE using non-partition key with pgbench to compare the performance among 3 conditions: no index, regular unique index (with partition-key involved), and global unique index:

Test 1: scale=100, 10 partitions, 1 million tuples/partition

SELECT:

-> No partitioned index: tps = 3.827886

-> regular unique index: tps = 14.713099

-> global unique index: tps = 23791.314238

UPDATE mixed with SELECT:

-> No partitioned index: tps = 1.926013

-> regular unique index: tps = 7.087059

-> global unique index: tps = 2253.098335

Test 2: scale=1,000, 100 partitions, 1 million tuples/partition

SELECT:

-> No partitioned index: tps = 0.110029

-> regular unique index: tps = 0.268199

-> global unique index: tps = 2334.811682

UPDATE mixed with SELECT:

-> No partitioned index: tps = 0.115329

-> regular unique index: tps = 0.197052

-> global unique index: tps = 541.488621

Test 3: scale=10,000, 1,000 partitions, 1 million tuples/partition

SELECT:

-> No partitioned index: tps = 0.011047

-> regular unique index: tps = 0.036812

-> global unique index: tps = 147.189456

UPDATE mixed with SELECT:

-> No partitioned index: tps = 0.008209

-> regular unique index: tps = 0.054367

-> global unique index: tps = 57.740432

thank you very much and we hope this information could help clarify some concerns about this approach.

David and Cary

============================

HighGo Software Canada

http://www.highgo.ca

---- On Mon, 21 Nov 2022 05:33:30 -0700  Simon Riggs  wrote ---

Show quoted text

On Thu, 17 Nov 2022 at 22:01, Cary Huang mailto:cary.huang@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.

You don't seem to mention that this would require a uniqueness check

on each partition. Is that correct? This would result in O(N) cost of

uniqueness checks, severely limiting load speed. I notice you don't

offer any benchmarks on load speed or the overhead associated with

this, which is not good if you want to be taken seriously, but at

least it is recoverable.

(It might be necessary to specify some partitions as READ ONLY, to

allow us to record their min/max values for the indexed cols, allowing

us to do this more quickly.)

- Supported Features -

1. Global unique index is supported only on btree index type

Why? Surely any index type that supports uniqueness is good.

- Not-supported Features -

1. Global uniqueness check with Sub partition tables is not yet supported as we do not have immediate use case and it may involve majoy change in current implementation

Hmm, sounds like a problem. Arranging the calls recursively should work.

- Create a global unique index -

To create a regular unique index on a partitioned table, Postgres has to perform heap scan and sorting on every child partition. Uniqueness check happens during the sorting phase and will raise an error if multiple tuples with the same index key are sorted together. To achieve global uniqueness check, we make Postgres perform the sorting after all of the child partitions have been scanned instead of on the "sort per partition" fashion. In otherwords, the sorting only happens once at the very end and it sorts the tuples coming from all the partitions and therefore can ensure global uniqueness.

My feeling is that performance on this will suck so badly that we must

warn people away from it, and tell people if they want this, create

the index at the start and let it load.

Hopefully CREATE INDEX CONCURRENTLY still works.

Let's see some benchmarks on this also please.

You'll need to think about progress reporting early because correctly

reporting the progress and expected run times are likely critical for

usability.

Example:

CREATE TABLE gidx_part (a int, b int, c text) PARTITION BY RANGE (a);

CREATE TABLE gidx_part1 partition of gidx_part FOR VALUES FROM (0) TO (10);

CREATE TABLE gidx_part2 partition of gidx_part FOR VALUES FROM (10) TO (20);

CREATE UNIQUE INDEX global_unique_idx ON gidx_part USING BTREE(b) GLOBAL;

INSERT INTO gidx_part values(5, 5, 'test');

INSERT INTO gidx_part values(15, 5, 'test');

ERROR:  duplicate key value violates unique constraint "gidx_part1_b_idx"

DETAIL:  Key (b)=(5) already exists.

Well done.

- DETACH -

Since we retain the same partitioned structure, detaching a partition with global unique index is straightforward. Upon DETACH, Postgres will change its relkind from RELKIND_GLOBAL_INDEX to RELKIND_INDEX and remove their inheritance relationship as usual.

It's the only way that works

- Optimizer, query planning and vacuum -

Since no major modification is done on global unique index's structure and storage, it works in the same way as a regular partitioned index. No major change is required to be done on optimizer, planner and vacuum process as they should work in the same way as regular index.

Agreed

Making a prototype is a great first step.

The next step is to understand the good and the bad aspects of it, so

you can see what else needs to be done. You need to be honest and real

about the fact that this may not actually be desirable in practice, or

in a restricted use case.

That means performance analysis of create, load, attach, detach,

INSERT, SELECT, UPD/DEL and anything else that might be affected,

together with algorithmic analysis of what happens for larger N and

larger tables.

Expect many versions; take provisions for many days.

Best of luck

--

Simon Riggs                http://www.EnterpriseDB.com/

Tom Lane schrieb am 18.11.2022 um 16:06:

Do we need new syntax actually? I think that a global unique index
can be created automatically instead of raising an error "unique
constraint on partitioned table must include all partitioning
columns"

I'm not convinced that we want this feature at all: as far as I can
see, it will completely destroy the benefits of making a partitioned
table in the first place. But if we do want it, I don't think it
should be so easy to create a global index by accident as that syntax
approach would make it. I think there needs to be a pretty clear YES
I WANT TO SHOOT MYSELF IN THE FOOT clause in the command.

There are many Oracle users that find global indexes useful despite
their disadvantages.

I have seen this mostly when the goal was to get the benefits of
partition pruning at runtime which turned the full table scan (=Seq Scan)
on huge tables to partition scans on much smaller partitions.
Partition wise joins were also helpful for query performance.
The substantially slower drop partition performance was accepted in thos cases

I think it would be nice to have the option in Postgres as well.

I do agree however, that the global index should not be created automatically.

Something like CREATE GLOBAL [UNIQUE] INDEX ... would be a lot better

Just my 0.05€

st 23. 11. 2022 v 23:42 odesílatel Thomas Kellerer <shammat@gmx.net> napsal:

Tom Lane schrieb am 18.11.2022 um 16:06:

Do we need new syntax actually? I think that a global unique index
can be created automatically instead of raising an error "unique
constraint on partitioned table must include all partitioning
columns"

I'm not convinced that we want this feature at all: as far as I can
see, it will completely destroy the benefits of making a partitioned
table in the first place. But if we do want it, I don't think it
should be so easy to create a global index by accident as that syntax
approach would make it. I think there needs to be a pretty clear YES
I WANT TO SHOOT MYSELF IN THE FOOT clause in the command.

There are many Oracle users that find global indexes useful despite
their disadvantages.

I have seen this mostly when the goal was to get the benefits of
partition pruning at runtime which turned the full table scan (=Seq Scan)
on huge tables to partition scans on much smaller partitions.
Partition wise joins were also helpful for query performance.
The substantially slower drop partition performance was accepted in thos
cases

I think it would be nice to have the option in Postgres as well.

I do agree however, that the global index should not be created
automatically.

Something like CREATE GLOBAL [UNIQUE] INDEX ... would be a lot better

Is it necessary to use special marks like GLOBAL if this index will be
partitioned, and uniqueness will be ensured by repeated evaluations?

Or you think so there should be really forced one relation based index?

I can imagine a unique index on partitions without a special mark, that
will be partitioned, and a second variant classic index created over a
partitioned table, that will be marked as GLOBAL.

Regards

Pavel

Show quoted text

Just my 0.05€

Pavel Stehule schrieb am 24.11.2022 um 07:03:

There are many Oracle users that find global indexes useful despite
their disadvantages.

I have seen this mostly when the goal was to get the benefits of
partition pruning at runtime which turned the full table scan (=Seq Scan)
on huge tables to partition scans on much smaller partitions.
Partition wise joins were also helpful for query performance.
The substantially slower drop partition performance was accepted in thos cases

I think it would be nice to have the option in Postgres as well.

I do agree however, that the global index should not be created automatically.

Something like CREATE GLOBAL [UNIQUE] INDEX ... would be a lot better

Is it necessary to use special marks like GLOBAL if this index will
be partitioned, and uniqueness will be ensured by repeated
evaluations?

Or you think so there should be really forced one relation based
index?

I can imagine a unique index on partitions without a special mark,
that will be partitioned, and a second variant classic index created
over a partitioned table, that will be marked as GLOBAL.

My personal opinion is, that a global index should never be created
automatically.

The user should consciously decide on using a feature
that might have a serious impact on performance in some areas.

On Fri, Nov 18, 2022 at 3:31 AM Cary Huang <cary.huang@highgo.ca> wrote:

Patch: Global Unique Index
- Optimizer, query planning and vacuum -
Since no major modification is done on global unique index's structure and storage, it works in the same way as a regular partitioned index. No major change is required to be done on optimizer, planner and vacuum process as they should work in the same way as regular index.

It might not need changes in the vacuum to make it work. But this can
not be really useful without modifying the vacuum the way it works. I
mean currently, the indexes are also partitioned based on the table so
whenever we do table vacuum it's fine to do index vacuum but now you
will have one gigantic index and which will be vacuumed every time we
vacuum any of the partitions. So for example, if you have 10000
partitions then by the time you vacuum the whole table (all 10000
partitions) the global index will be vacuumed 10000 times.

There was some effort in past (though it was not concluded) about
decoupling the index and heap vacuuming such that instead of doing the
index vacuum for each partition we remember the dead tids and we only
do the index vacuum when we think there are enough dead items so that
the index vacuum makes sense[1]/messages/by-id/CA+TgmoZgapzekbTqdBrcH8O8Yifi10_nB7uWLB8ajAhGL21M6A@mail.gmail.com.

[1]: /messages/by-id/CA+TgmoZgapzekbTqdBrcH8O8Yifi10_nB7uWLB8ajAhGL21M6A@mail.gmail.com

--
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com

On Thu, Nov 24, 2022 at 07:03:24AM +0100, Pavel Stehule wrote:

I can imagine a unique index on partitions without a special mark, that
will be partitioned,

That exists since v11, as long as the index keys include the partition
keys.

and a second variant classic index created over a partitioned table,
that will be marked as GLOBAL.

That's not what this patch is about, though.

On Thu, Nov 24, 2022 at 08:52:16PM +0530, Dilip Kumar wrote:

but now you will have one gigantic index and which will be vacuumed
every time we vacuum any of the partitions.

This patch isn't implemented as "one gigantic index", though.

--
Justin

---- On Thu, 24 Nov 2022 08:00:59 -0700 Thomas Kellerer wrote ---

Pavel Stehule schrieb am 24.11.2022 um 07:03:

There are many Oracle users that find global indexes useful despite
their disadvantages.

I have seen this mostly when the goal was to get the benefits of
partition pruning at runtime which turned the full table scan (=Seq Scan)
on huge tables to partition scans on much smaller partitions.
Partition wise joins were also helpful for query performance.
The substantially slower drop partition performance was accepted in thos cases

I think it would be nice to have the option in Postgres as well.

I do agree however, that the global index should not be created automatically.

Something like CREATE GLOBAL [UNIQUE] INDEX ... would be a lot better

Is it necessary to use special marks like GLOBAL if this index will
be partitioned, and uniqueness will be ensured by repeated
evaluations?

Or you think so there should be really forced one relation based
index?

I can imagine a unique index on partitions without a special mark,
that will be partitioned, and a second variant classic index created
over a partitioned table, that will be marked as GLOBAL.

My personal opinion is, that a global index should never be created
automatically.

The user should consciously decide on using a feature
that might have a serious impact on performance in some areas.

Agreed, if a unique index is created on non-partition key columns without including the special mark (partition key columns), it may be a mistake from user. (At least I make this mistake all the time). Current PG will give you a warning to include the partition keys, which is good.

If we were to automatically turn that into a global unique index, user may be using the feature without knowing and experiencing some performance impacts (to account for extra uniqueness check in all partitions).

On Thu, Nov 24, 2022 at 9:39 PM Justin Pryzby <pryzby@telsasoft.com> wrote:

On Thu, Nov 24, 2022 at 08:52:16PM +0530, Dilip Kumar wrote:

but now you will have one gigantic index and which will be vacuumed
every time we vacuum any of the partitions.

This patch isn't implemented as "one gigantic index", though.

If this patch is for supporting a global index then I expect that the
global index across all the partitions is going to be big. Anyway, my
point was about vacuuming the common index every time you vacuum any
of the partitions of the table is not the right way and that will make
global indexes less usable.

--
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com

On Fri, Nov 25, 2022 at 8:49 AM Dilip Kumar <dilipbalaut@gmail.com> wrote:

On Thu, Nov 24, 2022 at 9:39 PM Justin Pryzby <pryzby@telsasoft.com> wrote:

On Thu, Nov 24, 2022 at 08:52:16PM +0530, Dilip Kumar wrote:

but now you will have one gigantic index and which will be vacuumed
every time we vacuum any of the partitions.

This patch isn't implemented as "one gigantic index", though.

If this patch is for supporting a global index then I expect that the
global index across all the partitions is going to be big. Anyway, my
point was about vacuuming the common index every time you vacuum any
of the partitions of the table is not the right way and that will make
global indexes less usable.

Okay, I got your point. After seeing the details it seems instead of
supporting one common index it is just allowing uniqueness checks
across multiple index partitions. Sorry for the noise.

--
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com

On Mon, Nov 21, 2022 at 12:33:30PM +0000, Simon Riggs wrote:

On Thu, 17 Nov 2022 at 22:01, Cary Huang <cary.huang@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
/messages/by-id/CA+Tgmob_J2M2+QKWrhg2NjQEkMEwZNTfd7a6Ubg34fJuZPkN2g@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 <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Embrace your flaws. They make you human, rather than perfect,
which you will never be.

Hi Bruce,

Thank you for helping review the patches in such detail.

On 2022-11-25 9:48 a.m., Bruce Momjian wrote:

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.)

For the uniqueness check cross all partitions, we tried to follow the
implementation of uniqueness check on a single partition, and added a
loop to check uniqueness on other partitions after the index tuple has
been inserted to current index partition but before this index tuple has
been made visible. The uniqueness check will wait `XactLockTableWait` if
there is a valid transaction in process, and performs the uniqueness
check again after the in-process transaction finished.

We tried to simulate this duplicate value case in blow steps:

1) prepare the partitioned table,
CREATE TABLE gidx_part (a int, b int, c text) PARTITION BY RANGE (a);
CREATE TABLE gidx_part1 partition of gidx_part FOR VALUES FROM (0) TO (10);
CREATE TABLE gidx_part2 partition of gidx_part FOR VALUES FROM (10) TO (20);

2) having two psql consoles hooked up with gdbs and set break points
after _bt_doinsert

result = _bt_doinsert(rel, itup, checkUnique, indexUnchanged, heapRel);

inside btinsert function in nbtree.c file.

3) first, execute `INSERT INTO gidx_part values(1, 1, 'test');` on
console-1, and then execute `INSERT INTO gidx_part values(11, 1,
'test');` on console-2 (expect duplicated value '1' in the 2nd column to
be detected),

The test results is that: console-2 query will have to wait until either
console-1 committed or aborted. If console-1 committed, then console-2
reports duplicated value already exists; if console-1 aborted, then
console-2 will report insert successfully. If there is a deadlock, then
the one detected this deadlock will error out to allow the other one
continue.

I am not quite sure if this is a proper way to deal with a deadlock in
this case. It would be so grateful if someone could help provide some
cases/methods to verify this cross all partitions uniqueness.

Best regards,

David

============================
HighGo Software Canada
www.highgo.ca <http://www.highgo.ca&gt;

On Fri, Nov 25, 2022 at 05:03:06PM -0800, David Zhang wrote:

Hi Bruce,

Thank you for helping review the patches in such detail.

On 2022-11-25 9:48 a.m., Bruce Momjian wrote:

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.)

For the uniqueness check cross all partitions, we tried to follow the
implementation of uniqueness check on a single partition, and added a loop to
check uniqueness on other partitions after the index tuple has been inserted to
current index partition but before this index tuple has been made visible. The
uniqueness check will wait `XactLockTableWait` if there is a valid transaction
in process, and performs the uniqueness check again after the in-process
transaction finished.

I can't see why this wouldn't work, but I also can't think of any cases
where we do this in our code already, so it will need careful
consideration.

We kind of do this for UPDATE and unique key conflicts, but only for a
single index entry. where we peek and sleep on pending changes, but not
across indexes.

I am not quite sure if this is a proper way to deal with a deadlock in this
case. It would be so grateful if someone could help provide some cases/methods
to verify this cross all partitions uniqueness.

I assume you are using our existing deadlock detection code, and just
sleeping in various indexes and expecting deadlock detection to happen.

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Embrace your flaws. They make you human, rather than perfect,
which you will never be.

On Fri, Nov 18, 2022 at 12:03:53PM +0300, Sergei Kornilov wrote:

Hello
Do we need new syntax actually? I think that a global unique index can be created automatically instead of raising an error "unique constraint on partitioned table must include all partitioning columns"

I may suggest even more of the new syntax.

If someone has to implement sequential index checking on unique
constraints, then it would be useful to be able to do that inde-
pendent of partitioning also.

E.g. for some kinds of manual partitions or for strangely de-
signed datasets. Or for some of the table partitions instead for
all of them.

For that reason, perhaps some other type of unique index -- that
is not an index per se, but a check against a set of indexes --
could be added. Or, perhaps, not an index, but an EXCLUDE con-
straint of that kind.

On 11/24/22 19:15, Cary Huang wrote:

---- On Thu, 24 Nov 2022 08:00:59 -0700 Thomas Kellerer wrote ---

Pavel Stehule schrieb am 24.11.2022 um 07:03:

There are many Oracle users that find global indexes useful despite
their disadvantages.

I have seen this mostly when the goal was to get the benefits of
partition pruning at runtime which turned the full table scan (=Seq Scan)
on huge tables to partition scans on much smaller partitions.
Partition wise joins were also helpful for query performance.
The substantially slower drop partition performance was accepted in thos cases

I think it would be nice to have the option in Postgres as well.

I do agree however, that the global index should not be created automatically.

Something like CREATE GLOBAL [UNIQUE] INDEX ... would be a lot better

Is it necessary to use special marks like GLOBAL if this index will
be partitioned, and uniqueness will be ensured by repeated
evaluations?

Or you think so there should be really forced one relation based
index?

I can imagine a unique index on partitions without a special mark,
that will be partitioned, and a second variant classic index created
over a partitioned table, that will be marked as GLOBAL.

My personal opinion is, that a global index should never be created
automatically.

The user should consciously decide on using a feature
that might have a serious impact on performance in some areas.

Agreed, if a unique index is created on non-partition key columns without including the special mark (partition key columns), it may be a mistake from user. (At least I make this mistake all the time). Current PG will give you a warning to include the partition keys, which is good.

If we were to automatically turn that into a global unique index, user may be using the feature without knowing and experiencing some performance impacts (to account for extra uniqueness check in all partitions).

I disagree. A user does not need to know that a table is partitionned,
and if the user wants a unique constraint on the table then making them
type an extra word to get it is just annoying.
--
Vik Fearing