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 1nP5dB-0002PL-4F for pgsql-performance@arkaria.postgresql.org; Tue, 01 Mar 2022 16:45:45 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1nP5dA-00034B-0K for pgsql-performance@arkaria.postgresql.org; Tue, 01 Mar 2022 16:45:44 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nP5d9-00033j-LH for pgsql-performance@lists.postgresql.org; Tue, 01 Mar 2022 16:45:43 +0000 Received: from smtp50.i.mail.ru ([94.100.177.110]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nP5d6-0000yZ-MZ for pgsql-performance@lists.postgresql.org; Tue, 01 Mar 2022 16:45:43 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=mail.ru; s=mail4; h=Content-Type:MIME-Version:Message-ID:Date:Subject:In-Reply-To:References:Cc:To:From:From:Subject:Content-Type:Content-Transfer-Encoding:To:Cc; bh=/J+Uj1IylFiRo6EetSIMNjwUOS0jrWLNAHlUrRlCvfA=; t=1646153140;x=1646758540; b=ZFLhs+sGlEMrm6bVrHbkMB0BuYUIbvHAOm1diQykpJtShHfNOPLRv+TY893BYBVPiS5SaMYZ6sJlbSQy7MwonwaJclpYPVChIlIX6vSOsZIwsuU0i3HEXcSTgx5tN55nISBJeLc/4rMdzqQtKFJcmq17cDh6ZL+LmXdfTZsFmtLWt2TXvROeMNAKFA6WkmZ7QVltEJFTapz9EZAimWLuJhcBBRvUpaDh4HTLeVsCLmbNTBjfVUfLAF84pVmDv0C7dCZHrar5SArDIef/TZLZ4BIIwm9iGD3290DRQ+yWiQp6Hkoj7VA2Y5kjHvrSVxkfTESv40NtfCkZqHor1cCkpw==; Received: by smtp50.i.mail.ru with esmtpa (envelope-from ) id 1nP5d5-0004sr-7E; Tue, 01 Mar 2022 19:45:39 +0300 From: "Andrew Zakharov" To: "'Marc Millas'" Cc: References: <011d01d82d82$42d20c50$c87624f0$@mail.ru> In-Reply-To: Subject: RE: Simple task with partitioning which I can't realize Date: Tue, 1 Mar 2022 19:45:40 +0300 Message-ID: <007501d82d8b$c97239c0$5c56ad40$@mail.ru> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0076_01D82DA4.EEC27F00" X-Mailer: Microsoft Outlook 16.0 Thread-Index: AQMqCSf02QMa+gddxm77SFmyorw+2AEijLK6qf3ulxA= Content-Language: ru Authentication-Results: smtp50.i.mail.ru; auth=pass smtp.auth=andrew898@mail.ru smtp.mailfrom=andrew898@mail.ru X-7564579A: EEAE043A70213CC8 X-77F55803: 4F1203BC0FB41BD9A10ECB78D781867AED8311F80DD7C4DADBC9A5D426C827BB182A05F538085040BFDB496609B48416CB328825CCC5F276D1F3AE447CEDDA107F22F6869B952283 X-7FA49CB5: FF5795518A3D127A4AD6D5ED66289B5278DA827A17800CE73230F712CF4B3924EA1F7E6F0F101C67BD4B6F7A4D31EC0BCC500DACC3FED6E28638F802B75D45FF8AA50765F7900637E31772672C957B658638F802B75D45FF36EB9D2243A4F8B5A6FCA7DBDB1FC311F39EFFDF887939037866D6147AF826D81B7931BECDB2FC070A0CEF10060EBD146F9789CCF6C18C3F8528715B7D10C86878DA827A17800CE7B24B16DE58C70C409FA2833FD35BB23D9E625A9149C048EE33AC447995A7AD182CC0D3CB04F14752D2E47CDBA5A96583BD4B6F7A4D31EC0BC014FD901B82EE079FA2833FD35BB23D27C277FBC8AE2E8BECADA55FE5B58BB7A471835C12D1D977C4224003CC836476EB9C4185024447017B076A6E789B0E975F5C1EE8F4F765FC6F8235414F7F731CD81D268191BDAD3DBD4B6F7A4D31EC0BEA7A3FFF5B025636D81D268191BDAD3D78DA827A17800CE7E7E616C4F51B9FAAEC76A7562686271EEC990983EF5C03292E808ACE2090B5E14AD6D5ED66289B5259CC434672EE63711DD303D21008E298D5E8D9A59859A8B6B372FE9A2E580EFC725E5C173C3A84C369456C5265B6C55C35872C767BF85DA2F004C90652538430E4A6367B16DE6309 X-8FC586DF: 6EFBBC1D9D64D975 X-C1DE0DAB: 0D63561A33F958A5287EF15A66215D57F0944C59AFA619448F87095F8A31B74FD59269BC5F550898D99A6476B3ADF6B47008B74DF8BB9EF7333BD3B22AA88B938A852937E12ACA75DF4B37519D6BB15B410CA545F18667F91A7EA1CDA0B5A7A0 X-C8649E89: 4E36BF7865823D7055A7F0CF078B5EC49A30900B95165D348E5EF936B2E46EBA68AD855708A42B3ACD563E6D7A5197A1034865FD0F49FFF1738997742FC810951D7E09C32AA3244C3E610862D8ED43DB7EA7B107D6461477F522A1CF68F4BE05729B2BEF169E0186 X-D57D3AED: 3ZO7eAau8CL7WIMRKs4sN3D3tLDjz0dLbV79QFUyzQ2Ujvy7cMT6pYYqY16iZVKkSc3dCLJ7zSJH7+u4VD18S7Vl4ZUrpaVfd2+vE6kuoey4m4VkSEu530nj6fImhcD4MUrOEAnl0W826KZ9Q+tr5ycPtXkTV4k65bRjmOUUP8cvGozZ33TWg5HZplvhhXbhDGzqmQDTd6OAevLeAnq3Ra9uf7zvY2zzsIhlcp/Y7m53TZgf2aB4JOg4gkr2biojWGWfNw4NKOmlaxxxouU1XA== X-Mailru-Sender: ED747E36EB90C325F1084D3EBE0B3C29E7B1DCC1E7F7FD2FCB328825CCC5F276E29C3C9F99C12D6F76CADC040CC768ECC77752E0C033A69E4431F22F943D8E2367DBD6A654610528B4A721A3011E896F X-Mras: Ok 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_0076_01D82DA4.EEC27F00 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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 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. =20 ------=_NextPart_000_0076_01D82DA4.EEC27F00 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

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:<= /b> = 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
Subject: 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

 

 

 

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.

 

<= /div> ------=_NextPart_000_0076_01D82DA4.EEC27F00--