Thread: PostgreSQL 11 global index

PostgreSQL 11 global index

From
Mariel Cherkassky
Date:
Hi,
I read the documentation but i didnt find any word regarding global index. I saw a new feature that indexes that  exist on the parent automaticly created on the childs but is there any connection between the indexes ? 

I'm trying to make sure that 2 different partitions wont have the same data on some of the columns and the partition col isnt one of those column. In oracle that kind of index is called global index.

Do you now some third extension maybe that allow you to use such feature ? 

Thanks , Mariel.

Re: PostgreSQL 11 global index

From
legrand legrand
Date:
Postgres 11 supports CREATE UNIQUE INDEX on  partitionned tables,
maybe that could be the answer for your question ?

Regards
PAscal



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html


Re: PostgreSQL 11 global index

From
Keith
Date:


On Sun, Aug 5, 2018 at 4:58 AM, Mariel Cherkassky <mariel.cherkassky@gmail.com> wrote:
Hi,
I read the documentation but i didnt find any word regarding global index. I saw a new feature that indexes that  exist on the parent automaticly created on the childs but is there any connection between the indexes ? 

I'm trying to make sure that 2 different partitions wont have the same data on some of the columns and the partition col isnt one of those column. In oracle that kind of index is called global index.

Do you now some third extension maybe that allow you to use such feature ? 

Thanks , Mariel.

This feature is not yet supported in PostgreSQL. In PG11, you can create a unique index, but in order for it to apply to the entire partition set, the column must be part of the partition key. I don't believe the native partitioning feature even allows you to create an unique index on the parent table if the partition key isn't part of it.

I've found some work-arounds for this in pg_partman in the mean time.


To support non-partition key unique columns on native partition sets, I have it use a separate template table where you apply your indexes instead of the parent table. And while it will enforce the uniqueness per child table, it will not enforce it across the entire set. To at least watch for this happening, I've provided a python script that goes through all the child tables and checks for any duplicates across the whole set. So it won't catch it at the time of insertion, but it should at least let you know if/when it happens.

Keith

Re: PostgreSQL 11 global index

From
Mariel Cherkassky
Date:
Hi,
The solution you suggested arent helpfull (both unique index and pg_partman) because I need to make sure that in all the partitions I have a specific column that is unique. In otherwords one column can have the same value in two different partitions and that is the concept of the global index.

Thanks , Mariel.

2018-08-05 23:31 GMT+03:00 Keith <keith@keithf4.com>:


On Sun, Aug 5, 2018 at 4:58 AM, Mariel Cherkassky <mariel.cherkassky@gmail.com> wrote:
Hi,
I read the documentation but i didnt find any word regarding global index. I saw a new feature that indexes that  exist on the parent automaticly created on the childs but is there any connection between the indexes ? 

I'm trying to make sure that 2 different partitions wont have the same data on some of the columns and the partition col isnt one of those column. In oracle that kind of index is called global index.

Do you now some third extension maybe that allow you to use such feature ? 

Thanks , Mariel.

This feature is not yet supported in PostgreSQL. In PG11, you can create a unique index, but in order for it to apply to the entire partition set, the column must be part of the partition key. I don't believe the native partitioning feature even allows you to create an unique index on the parent table if the partition key isn't part of it.

I've found some work-arounds for this in pg_partman in the mean time.


To support non-partition key unique columns on native partition sets, I have it use a separate template table where you apply your indexes instead of the parent table. And while it will enforce the uniqueness per child table, it will not enforce it across the entire set. To at least watch for this happening, I've provided a python script that goes through all the child tables and checks for any duplicates across the whole set. So it won't catch it at the time of insertion, but it should at least let you know if/when it happens.

Keith

Re: PostgreSQL 11 global index

From
Laurenz Albe
Date:
Mariel Cherkassky wrote:
> The solution you suggested arent helpfull (both unique index and pg_partman) because I need to
> make sure that in all the partitions I have a specific column that is unique.
> In otherwords one column can have the same value in two different partitions and that
> is the concept of the global index.

There is no way to guarantee uniqueness across partitions in PostgreSQL.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com


Re: PostgreSQL 11 global index

From
"Jehan-Guillaume (ioguix) de Rorthais"
Date:
Hi,

On Mon, 6 Aug 2018 09:20:45 +0300
Mariel Cherkassky <mariel.cherkassky@gmail.com> wrote:

> The solution you suggested arent helpfull (both unique index and
> pg_partman) because I need to make sure that in all the partitions I have a
> specific column that is unique.

Here is a workaround that actually implement a unique constraint over multi
relation. You can avoid first chapters about the problems a UNIQUE constraint
deal with. The following link jump directly to the solution:
http://blog.ioguix.net/postgresql/2015/02/05/Partitionning-and-constraints-part-1.html#real-solution-adding-locks

Regards,

> 2018-08-05 23:31 GMT+03:00 Keith <keith@keithf4.com>:
> 
> >
> >
> > On Sun, Aug 5, 2018 at 4:58 AM, Mariel Cherkassky <  
> > mariel.cherkassky@gmail.com> wrote:  
> >  
> >> Hi,
> >> I read the documentation but i didnt find any word regarding global
> >> index. I saw a new feature that indexes that  exist on the parent
> >> automaticly created on the childs but is there any connection between the
> >> indexes ?
> >>
> >> I'm trying to make sure that 2 different partitions wont have the same
> >> data on some of the columns and the partition col isnt one of those column.
> >> In oracle that kind of index is called global index.
> >>
> >> Do you now some third extension maybe that allow you to use such feature
> >> ?
> >>
> >> Thanks , Mariel.
> >>  
> >
> > This feature is not yet supported in PostgreSQL. In PG11, you can create a
> > unique index, but in order for it to apply to the entire partition set, the
> > column must be part of the partition key. I don't believe the native
> > partitioning feature even allows you to create an unique index on the
> > parent table if the partition key isn't part of it.
> >
> > I've found some work-arounds for this in pg_partman in the mean time.
> >
> > https://github.com/pgpartman/pg_partman
> >
> > To support non-partition key unique columns on native partition sets, I
> > have it use a separate template table where you apply your indexes instead
> > of the parent table. And while it will enforce the uniqueness per child
> > table, it will not enforce it across the entire set. To at least watch for
> > this happening, I've provided a python script that goes through all the
> > child tables and checks for any duplicates across the whole set. So it
> > won't catch it at the time of insertion, but it should at least let you
> > know if/when it happens.
> >
> > Keith
> >  



-- 
Jehan-Guillaume de Rorthais
Dalibo


Re: PostgreSQL 11 global index

From
Mariel Cherkassky
Date:
hI Jehan-Guillaume de Rorthais,
This solution looks good but I think that i will have a big problem. I need a global index because I try to insert a bulk data (alot of data) with the copy command / pgbulkload extension. The solution you provided will create a constraint trigger that will be used after very insert. Now, in my case I have 2 questions : 
1)When I use the copy command to load data into the table, do you think that the trigger will be effected ?
2)If the trigger will work I think that it would decrease the performance dramaticly.

2018-08-06 11:02 GMT+03:00 Jehan-Guillaume (ioguix) de Rorthais <ioguix@free.fr>:
Hi,

On Mon, 6 Aug 2018 09:20:45 +0300
Mariel Cherkassky <mariel.cherkassky@gmail.com> wrote:

> The solution you suggested arent helpfull (both unique index and
> pg_partman) because I need to make sure that in all the partitions I have a
> specific column that is unique.

Here is a workaround that actually implement a unique constraint over multi
relation. You can avoid first chapters about the problems a UNIQUE constraint
deal with. The following link jump directly to the solution:
http://blog.ioguix.net/postgresql/2015/02/05/Partitionning-and-constraints-part-1.html#real-solution-adding-locks

Regards,

> 2018-08-05 23:31 GMT+03:00 Keith <keith@keithf4.com>:
>
> >
> >
> > On Sun, Aug 5, 2018 at 4:58 AM, Mariel Cherkassky < 
> > mariel.cherkassky@gmail.com> wrote: 
> > 
> >> Hi,
> >> I read the documentation but i didnt find any word regarding global
> >> index. I saw a new feature that indexes that  exist on the parent
> >> automaticly created on the childs but is there any connection between the
> >> indexes ?
> >>
> >> I'm trying to make sure that 2 different partitions wont have the same
> >> data on some of the columns and the partition col isnt one of those column.
> >> In oracle that kind of index is called global index.
> >>
> >> Do you now some third extension maybe that allow you to use such feature
> >> ?
> >>
> >> Thanks , Mariel.
> >> 
> >
> > This feature is not yet supported in PostgreSQL. In PG11, you can create a
> > unique index, but in order for it to apply to the entire partition set, the
> > column must be part of the partition key. I don't believe the native
> > partitioning feature even allows you to create an unique index on the
> > parent table if the partition key isn't part of it.
> >
> > I've found some work-arounds for this in pg_partman in the mean time.
> >
> > https://github.com/pgpartman/pg_partman
> >
> > To support non-partition key unique columns on native partition sets, I
> > have it use a separate template table where you apply your indexes instead
> > of the parent table. And while it will enforce the uniqueness per child
> > table, it will not enforce it across the entire set. To at least watch for
> > this happening, I've provided a python script that goes through all the
> > child tables and checks for any duplicates across the whole set. So it
> > won't catch it at the time of insertion, but it should at least let you
> > know if/when it happens.
> >
> > Keith
> > 



--
Jehan-Guillaume de Rorthais
Dalibo

Re: PostgreSQL 11 global index

From
legrand legrand
Date:
+1
Very interesting  article

Regards
PAscal



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html


Re: PostgreSQL 11 global index

From
"Jehan-Guillaume (ioguix) de Rorthais"
Date:
On Mon, 6 Aug 2018 11:48:04 +0300
Mariel Cherkassky <mariel.cherkassky@gmail.com> wrote:

> hI Jehan-Guillaume de Rorthais,
> This solution looks good but I think that i will have a big problem. I need
> a global index because I try to insert a bulk data (alot of data) with the
> copy command / pgbulkload extension. The solution you provided will create
> a constraint trigger that will be used after very insert.

NB: it could be fired "before insert" as well depending on your schema, with
minimal modification (see comments in the article)

> Now, in my case I have 2 questions :
> 1)When I use the copy command to load data into the table, do you think
> that the trigger will be effected ?

The doc says yes:

  « COPY FROM will invoke any triggers and check constraints on the destination
  table. However, it will not invoke rules.»

https://www.postgresql.org/docs/11/static/sql-copy.html

> 2)If the trigger will work I think that it would decrease the performance
> dramaticly.

You will have to measure it.

I doubt rewriting the trigger in C would help on this matter, but if you do
test it, I would be glad to hear about the results :)

> 2018-08-06 11:02 GMT+03:00 Jehan-Guillaume (ioguix) de Rorthais <
> ioguix@free.fr>:
>
> > Hi,
> >
> > On Mon, 6 Aug 2018 09:20:45 +0300
> > Mariel Cherkassky <mariel.cherkassky@gmail.com> wrote:
> >
> > > The solution you suggested arent helpfull (both unique index and
> > > pg_partman) because I need to make sure that in all the partitions I
> > have a
> > > specific column that is unique.
> >
> > Here is a workaround that actually implement a unique constraint over multi
> > relation. You can avoid first chapters about the problems a UNIQUE
> > constraint
> > deal with. The following link jump directly to the solution:
> > http://blog.ioguix.net/postgresql/2015/02/05/
> > Partitionning-and-constraints-part-1.html#real-solution-adding-locks
> >
> > Regards,
> >
> > > 2018-08-05 23:31 GMT+03:00 Keith <keith@keithf4.com>:
> > >
> > > >
> > > >
> > > > On Sun, Aug 5, 2018 at 4:58 AM, Mariel Cherkassky <
> > > > mariel.cherkassky@gmail.com> wrote:
> > > >
> > > >> Hi,
> > > >> I read the documentation but i didnt find any word regarding global
> > > >> index. I saw a new feature that indexes that  exist on the parent
> > > >> automaticly created on the childs but is there any connection between
> > the
> > > >> indexes ?
> > > >>
> > > >> I'm trying to make sure that 2 different partitions wont have the same
> > > >> data on some of the columns and the partition col isnt one of those
> > column.
> > > >> In oracle that kind of index is called global index.
> > > >>
> > > >> Do you now some third extension maybe that allow you to use such
> > feature
> > > >> ?
> > > >>
> > > >> Thanks , Mariel.
> > > >>
> > > >
> > > > This feature is not yet supported in PostgreSQL. In PG11, you can
> > create a
> > > > unique index, but in order for it to apply to the entire partition
> > set, the
> > > > column must be part of the partition key. I don't believe the native
> > > > partitioning feature even allows you to create an unique index on the
> > > > parent table if the partition key isn't part of it.
> > > >
> > > > I've found some work-arounds for this in pg_partman in the mean time.
> > > >
> > > > https://github.com/pgpartman/pg_partman
> > > >
> > > > To support non-partition key unique columns on native partition sets, I
> > > > have it use a separate template table where you apply your indexes
> > instead
> > > > of the parent table. And while it will enforce the uniqueness per child
> > > > table, it will not enforce it across the entire set. To at least watch
> > for
> > > > this happening, I've provided a python script that goes through all the
> > > > child tables and checks for any duplicates across the whole set. So it
> > > > won't catch it at the time of insertion, but it should at least let you
> > > > know if/when it happens.
> > > >
> > > > Keith
> > > >
> >
> >
> >
> > --
> > Jehan-Guillaume de Rorthais
> > Dalibo
> >



--
Jehan-Guillaume de Rorthais
Dalibo


Re: PostgreSQL 11 global index

From
legrand legrand
Date:
Hello,

I was wondering if a simple trigger inserting ID values in a dedicated table
with an UNIQUE key
would have a chance to give similar performances than you trigger checking
ID with 
SELECT count(1) > 1 FROM tabpart WHERE id = NEW.id

knowing that both triggers gives the same concurrency benefits ...

Find attached a little test case on my 
 PostgreSQL 11beta2 on x86_64-w64-mingw32, compiled by gcc.exe
(x86_64-win32-seh-rev1, Built by MinGW-W64 project) 7.2.0, 64-bit

inserting 100.000 lignes in a non partitionned table with a unique KEY takes
627 ms 
inserting 100.000 lignes in a partitionned table (10 part)  with "SELECT
COUNT" trigger takes 6696 ms
inserting 100.000 lignes in a partitionned table (10 part)  with "INSERT ID
values" trigger takes 1634 ms

Is that ratio the same on a Linux box ?
I'm suspecting parsing time for "SELECT count(1) > 1 FROM tabpart WHERE id =
NEW.id",
is that possible ?

test_gbl_index.sql
<http://www.postgresql-archive.org/file/t348768/test_gbl_index.sql>  

Regards
PAscal



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html


Re: PostgreSQL 11 global index

From
Scott Ribe
Date:
Parse time & execution time.

Why are you not just using a sequence?

Do you have an application which requires contiguous numbers?

--
Scott Ribe
scott_ribe@elevated-dev.com
https://www.linkedin.com/in/scottribe/



> On Aug 13, 2018, at 4:39 PM, legrand legrand <legrand_legrand@hotmail.com> wrote:
>
> I'm suspecting parsing time for "SELECT count(1) > 1 FROM tabpart WHERE id =
> NEW.id",



Re: PostgreSQL 11 global index

From
Scott Ribe
Date:
>
> On Aug 13, 2018, at 4:44 PM, Scott Ribe <scott_ribe@elevated-dev.com> wrote:
>
> Parse time & execution time.
>

I expect using an ordinary sequence and setting it as the column's default would be fastest.