Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nPJPg-0003vx-Kr for pgsql-performance@arkaria.postgresql.org; Wed, 02 Mar 2022 07:28:44 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1nPJPf-0006Do-J3 for pgsql-performance@arkaria.postgresql.org; Wed, 02 Mar 2022 07:28:43 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nPJPf-0006Cm-0v for pgsql-performance@lists.postgresql.org; Wed, 02 Mar 2022 07:28:43 +0000 Received: from mout.kundenserver.de ([212.227.17.10]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nPJPb-0005i9-Pi for pgsql-performance@lists.postgresql.org; Wed, 02 Mar 2022 07:28:41 +0000 Received: from ANAT ([78.197.128.121]) by mrelayeu.kundenserver.de (mreue109 [212.227.15.184]) with ESMTPSA (Nemesis) id 1MplPf-1nzTdh0TDZ-00qAgV; Wed, 02 Mar 2022 08:28:36 +0100 From: "Michel SALAIS" To: "'Marc Millas'" , "'Andrew Zakharov'" Cc: References: <011d01d82d82$42d20c50$c87624f0$@mail.ru> <007501d82d8b$c97239c0$5c56ad40$@mail.ru> In-Reply-To: Subject: RE: Simple task with partitioning which I can't realize Date: Wed, 2 Mar 2022 08:28:34 +0100 Message-ID: <00cc01d82e07$2057bfa0$61073ee0$@msym.fr> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_00CD_01D82E0F.821E2370" X-Mailer: Microsoft Outlook 16.0 Thread-Index: AQMqCSf02QMa+gddxm77SFmyorw+2AEijLK6AbsWq7QDB836k6nYzRaA Content-Language: fr X-Provags-ID: V03:K1:NbqQrQMhjiCWaeTujcjfBwwHmTOj42F9YTyFO9/BPYA0fv+okiA CqoVnl8yTUH8hYfJJTqAG1mHg6rRPN5+HQiYgsHhJ/bKP42DLcbE2gu88HVWFnpD6qE+vNq 6LNNI7sHmnPQEmhv0L3Lu0US1Pt8y3g4JrgYrgRlDUgBHWpMFl/LIZ4MvLQ9LWKIYEeEfwF du3CEqfrtccyaW4pUJUsQ== X-Spam-Flag: NO X-UI-Out-Filterresults: notjunk:1;V03:K0:Jg6MItVAdxs=:3GAvCw6Gus5/9AkUuw7pbk rrF2JGw+hs+dxwqAWQvQ/NGPkEB6ivoD/3rZj0YWMw1kg13HgbAB8gMtqzSW8II02dVrJ6qjW SuQ+mPKMH6LjiMSru6B+yKoilyDO6V7E0pnO9BkZIdY0VuJv61WROcjsaVi4c78RoK9QKxzrm VTl8P10yhxMTVArDuAKaCalKns0G5LV4nTIC5YDaaVS4OaFtqgGMnfdxfhyGK5qPPsC8ERtnC mHkvDCESIV1Ce7UD1Gx6Eq8Y7MhgUH6zheuEH+1n7BlnJ1yaZIfECBfgUUGXfcSPzUoz681bU BbuyJZVsoKuMdXBNYdgKw3lJLvf09AZYmoKWKOIT3F79nA0NMUJn2tps8hMxIEOLaqog8B8Vp oq9orcfymRHOA48F37G2D5xP+9uzPcny9u7lfqKu7QmyV6RTpVWeAJWn+EvzUyfqFlTjlw+I1 FRzq1YZ9GrLhWOfrZ2eU5WNv+ByhiMmhvXmC5P8Mlol3s0oMwTrYl7HMBLShgvHq0rEbfbcbx reFMhnFC4ERQgAmnwTRFPUrtBx8hFtX5Gq1gbs/vs9fDKMtCq78FyB7DFNOGzI7XJ2W4u11zP Tj68nlqc0QTkDP1TO9uzS07CoJtyYJtCfuw8pxRox9z21uNL3xhhS0Vxrz6o+TSfu94XejFmP bxOMo8g8xdQiA3SivAv4NteqFc/Z4aZF/QY6s/UMH74u9R/Nj6SWnVJu+nFt1VikufYY= List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multipart message in MIME format. ------=_NextPart_000_00CD_01D82E0F.821E2370 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable De : Marc Millas =20 Envoy=C3=A9 : mardi 1 mars 2022 19:00 =C3=80 : Andrew Zakharov Cc : pgsql-performance@lists.postgresql.org Objet : Re: Simple task with partitioning which I can't realize =20 Andrew, =20 contrary to Oracle, in postgres you can add the indexes and/or the = constraints which are meaningful to you at partition level. I was not saying NOT to create keys, but I was saying to create them at = partition level. =20 Marc MILLAS Senior Architect +33607850334 www.mokadb.com =20 =20 =20 On Tue, Mar 1, 2022 at 5:45 PM Andrew Zakharov < = Andrew898@mail.ru> wrote: Hi Marc =E2=80=93 Since there is a DWH fed by ETL there no risks to have same gids in = different region partitions. I considered simple partitioned table w/o = any keys but I=E2=80=99d believed there is a solutions with keys = that=E2=80=99s why I=E2=80=99m seeking the clue. Thanks. Andrew. =20 From: Marc Millas >=20 Sent: Tuesday, March 01, 2022 7:29 PM To: Andrew Zakharov > Cc: pgsql-performance@lists.postgresql.org = =20 Subject: Re: Simple task with partitioning which I can't realize =20 Hi, =20 is there any chance (risk ?) that a given gid be present in more than = one region ? if not (or if you implement it via a dedicated, non partition table),=20 =20 you may create a simple table partitioned by region, and create unique = indexes for each partition. this is NOT equivalent to a unique constraint at global table level, of = course. Marc MILLAS Senior Architect +33607850334 www.mokadb.com =20 =20 =20 =20 On Tue, Mar 1, 2022 at 4:37 PM Andrew Zakharov > wrote: Hello all =E2=80=93 I have a task which is simple at the first look. I have a table which = contains hierarchy of address objects starting with macro region end = ends with particular buildings. You can imagine how big is it.=20 Here is short sample of table declaration: =20 create table region_hierarchy( gid uuid not null default uuid_generate_v1mc(), parent_gid uuid null, region_code int2, =E2=80=A6 constraint pk_region_hierarchy primary key (gid), constraint fk_region_hierarchy_region_hierarchy_parent foreign key = (parent_gid) references region_hierarchy(gid) ); =20 Being an Oracle specialist, I planned to using same declarative = partitioning by list on the region_code field as I did in Oracle = database. I=E2=80=99ve carefully looked thru = docs/faqs/google/communities and found out that I must include = =E2=80=9Cgid=E2=80=9D field into partition key because a primary key = field. Thus partition method =E2=80=9Cby list=E2=80=9D is not = appropriate method in this case and =E2=80=9Cby range=E2=80=9D either. = What I have left from partition methods? Hash? How can I create = partitions by gid & region_code by hash? Feasible? Will it be working = properly (with partition pruning) when search criteria is by region_code = only? Same problem appears when there is simple serial = =E2=80=9Cid=E2=80=9D used as primary identifier. Removing all = constraints is not considered. I understand that such specific = PostgreSQL partitioning implementation has done by tons of reasons but = how I can implement partitioning for my EASY case? I see the only legacy = inheritance is left, right? Very sad if it=E2=80=99s true. Your advices are very important. Thanks in advance. Andrew. = _________________________________________________________________________= ________________ Hi To say it using Oracle vocabulary, PostgreSQL doesn=E2=80=99t offer = GLOBAL INDEXES. Even when we create an index on the partitioned table = which is now possible, PostgreSQL create LOCAL indexes on each partition = separately. There is no global indexes on partitioned tables in PostgreSQL. So it is = not simple to offer uniqueness at global level using indexes. That is = why, it is required that partition key columns be part of the primary = key AND any other UNIQE constraint. =20 Michel SALAIS ------=_NextPart_000_00CD_01D82E0F.821E2370 Content-Type: text/html; charset="utf-8" Content-Transfer-Encoding: quoted-printable

De : Marc Millas = <marc.millas@mokadb.com>
Envoy=C3=A9 : mardi 1 = mars 2022 19:00
=C3=80 : Andrew Zakharov = <Andrew898@mail.ru>
Cc : = pgsql-performance@lists.postgresql.org
Objet : Re: Simple = task with partitioning which I can't realize

 

Andrew,

 

contrary to Oracle, in postgres you = can add the indexes and/or the constraints which are meaningful to you = at partition level.

I was not saying NOT to create keys, but I was saying to = create them at partition level.

 


Marc MILLAS

Senior Architect

+33607850334

www.mokadb.com

 

 

 

On Tue, Mar 1, 2022 at 5:45 PM Andrew Zakharov = <Andrew898@mail.ru> = wrote:

Hi Marc =E2=80=93

Since there is a DWH fed by ETL = there no risks to have same gids in different region partitions. I = considered simple partitioned table w/o any keys but I=E2=80=99d = believed there is a solutions with keys that=E2=80=99s why I=E2=80=99m = seeking the clue.

Thanks.

Andrew.

 

From: Marc Millas <marc.millas@mokadb.com>
Sent: = Tuesday, March 01, 2022 7:29 PM
To: Andrew Zakharov <Andrew898@mail.ru>
Cc: pgsql-performance@lists.postgresql.org
Subjec= t: Re: Simple task with partitioning which I can't = realize

 

Hi,

 

is there any chance (risk ?) that a given gid be present = in more than one region ?

if not (or if you implement it via a dedicated, non partition = table), 

 

you may create a simple table partitioned by region, and = create unique indexes for each partition.

this is NOT equivalent to a unique constraint at global table = level, of course.


Marc MILLAS

Senior Architect

+33607850334

www.mokadb.com

 

 

 

On Tue, Mar 1, 2022 at 4:37 PM Andrew Zakharov <Andrew898@mail.ru> = wrote:

Hello all =E2=80=93

I have a task which is simple at the first look. I have a = table which contains hierarchy of address objects starting with macro = region end ends with particular buildings. You can imagine how big is = it.

Here is short sample of table declaration:

 

create table = region_hierarchy(

  gid uuid not = null default uuid_generate_v1mc(),

  parent_gid uuid = null,

  region_code = int2,

  = =E2=80=A6

    = constraint pk_region_hierarchy primary key (gid),

    = constraint fk_region_hierarchy_region_hierarchy_parent foreign key = (parent_gid) references region_hierarchy(gid)

);

 

Being an Oracle specialist, I planned to using same = declarative partitioning by list on the region_code field as I did in = Oracle database. I=E2=80=99ve carefully looked thru = docs/faqs/google/communities and found out that I must include = =E2=80=9Cgid=E2=80=9D field into partition key because a primary key = field. Thus partition method =E2=80=9Cby list=E2=80=9D is not = appropriate method in this case and =E2=80=9Cby range=E2=80=9D either. = What I have left from partition methods? Hash? How can I create = partitions by gid & region_code by hash? Feasible? Will it be = working properly (with partition pruning) when search criteria is by = region_code only? Same problem appears when there is simple serial = =E2=80=9Cid=E2=80=9D used as primary identifier. Removing all = constraints is not considered. I understand that such specific = PostgreSQL partitioning implementation has done by tons of reasons but = how I can implement partitioning for my EASY case? I see the only legacy = inheritance is left, right? Very sad if it=E2=80=99s true.

Your advices are very important.

Thanks in advance.

Andrew.

 ______________________________________________________= ___________________________________

Hi

To say it using Oracle vocabulary, = PostgreSQL doesn=E2=80=99t offer GLOBAL INDEXES. Even when we create an = index on the partitioned table which is now possible, PostgreSQL create = LOCAL indexes on each partition separately.

There is no global indexes on = partitioned tables in PostgreSQL. So it is not simple to offer = uniqueness at global level using indexes. That is why, it is required = that partition key columns be part of the primary key AND any other = UNIQE constraint.

 

Michel = SALAIS

------=_NextPart_000_00CD_01D82E0F.821E2370--