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 1nP7TT-0006lf-TB for pgsql-performance@arkaria.postgresql.org; Tue, 01 Mar 2022 18:43:53 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1nP7TR-0001dX-Ei for pgsql-performance@arkaria.postgresql.org; Tue, 01 Mar 2022 18:43:49 +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 1nP7TQ-0001dO-TI for pgsql-performance@lists.postgresql.org; Tue, 01 Mar 2022 18:43:49 +0000 Received: from smtp39.i.mail.ru ([94.100.177.99]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nP7TN-0001wD-Ow for pgsql-performance@lists.postgresql.org; Tue, 01 Mar 2022 18:43:48 +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=k/paHZZwZc+jBC86DejHE/fhHr7BC78DbA4qWWUt+1c=; t=1646160225;x=1646765625; b=Wkk2005Kb6RFqS5AxUEeIFry1jfCgBA0Ee2yf8lwk29vExfrBk2JB5zJ2pCxLC9W6ZsRfW1mCDs+0oG8X3yPV7PljN8sHP1U8ZcuyJkIWuLv7V/LMt3kxanY1kTu4yNHeYL840AacMD3ZbvzJpAloI4rSqi+0Z6K1BMSI0pwmHgWnOyxCvh8SZPzGJg3ZBUDDgZ6Io1CDklPiNEVLvl5rRUEZyOmZLq/qbgSFwWnYEr1chuV/3Bak9qF2/YieWuS+6H8X00Z8gOGIeFMR2ikBfbaPwC/LEhUpCS6hL49+zYenqkgHjgrWap3LNH4b7GttX6m6T8nW4Bha0mfW4c4Zw==; Received: by smtp39.i.mail.ru with esmtpa (envelope-from ) id 1nP7TM-00053i-2w; Tue, 01 Mar 2022 21:43:44 +0300 From: "Andrew Zakharov" To: "'Marc Millas'" 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: Tue, 1 Mar 2022 21:43:43 +0300 Message-ID: <000a01d82d9c$47b06400$d7112c00$@mail.ru> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_000B_01D82DB5.6CFFE5F0" X-Mailer: Microsoft Outlook 16.0 Thread-Index: AQMqCSf02QMa+gddxm77SFmyorw+2AEijLK6AbsWq7QDB836k6nX96Xw Content-Language: ru Authentication-Results: smtp39.i.mail.ru; auth=pass smtp.auth=andrew898@mail.ru smtp.mailfrom=andrew898@mail.ru X-4EC0790: 10 X-7564579A: EEAE043A70213CC8 X-77F55803: 4F1203BC0FB41BD91CBEBCA77BD4D300BB7CE0F3CD5609BD069385592BCEC310182A05F5380850404C228DA9ACA6FE278A1D3F446ED9685233F2CA10B45BC315FD81324FF7FA03690F5716A966C13E7F X-7FA49CB5: FF5795518A3D127A4AD6D5ED66289B5278DA827A17800CE74B51810D54EC17F0C2099A533E45F2D0395957E7521B51C2CFCAF695D4D8E9FCEA1F7E6F0F101C6778DA827A17800CE758E3775C3D7FDEE4EA1F7E6F0F101C6723150C8DA25C47586E58E00D9D99D84E1BDDB23E98D2D38BEBC5CAB6D411FFA6E9CAE20B071465FDA566A14456D64DB020879F7C8C5043D14489FFFB0AA5F4BF176DF2183F8FC7C068077CCD40B79AC98941B15DA834481FA18204E546F3947C062BEEFFB5F8EA3EF6B57BC7E64490618DEB871D839B7333395957E7521B51C2DFABB839C843B9C08941B15DA834481F8AA50765F79006377C70927E34808485389733CBF5DBD5E9B5C8C57E37DE458BD9DD9810294C998ED8FC6C240DEA76428AA50765F790063751898D81C3DCE054D32BA5DBAC0009BE395957E7521B51C20BC6067A898B09E4090A508E0FED6299176DF2183F8FC7C070C819287814BA9ACD04E86FAF290E2D7E9C4E3C761E06A71DD303D21008E29813377AFFFEAFD269A417C69337E82CC2E827F84554CEF50127C277FBC8AE2E8BA83251EDC214901ED5E8D9A59859A8B60A62CEF541B197C8089D37D7C0E48F6C5571747095F342E88FB05168BE4CE3AF X-8FC586DF: 6EFBBC1D9D64D975 X-C1DE0DAB: 0D63561A33F958A5DF65092F658EF427ED46AECB28E39F5EB23113ECA96ACED5D59269BC5F550898D99A6476B3ADF6B47008B74DF8BB9EF7333BD3B22AA88B938A852937E12ACA75DF4B37519D6BB15B410CA545F18667F91A7EA1CDA0B5A7A0 X-C8649E89: 4E36BF7865823D7055A7F0CF078B5EC49A30900B95165D34197948450CB5442A2F4CC2B0BC48538AB8353D9E1DFFC29AF0FC484ED5F57573C7399EFD50EC86D41D7E09C32AA3244CFCC823E1ACFAE6F5B991CE2F06E50C0AE8FBBEFAE1C4874C729B2BEF169E0186 X-D57D3AED: 3ZO7eAau8CL7WIMRKs4sN3D3tLDjz0dLbV79QFUyzQ2Ujvy7cMT6pYYqY16iZVKkSc3dCLJ7zSJH7+u4VD18S7Vl4ZUrpaVfd2+vE6kuoey4m4VkSEu530nj6fImhcD4MUrOEAnl0W826KZ9Q+tr5ycPtXkTV4k65bRjmOUUP8cvGozZ33TWg5HZplvhhXbhDGzqmQDTd6OAevLeAnq3Ra9uf7zvY2zzsIhlcp/Y7m53TZgf2aB4JOg4gkr2biojWGWfNw4NKOmRFy6m/Lmlbg== X-Mailru-Sender: ED747E36EB90C325F1084D3EBE0B3C291616309EA5D68A1433F2CA10B45BC3154CF07495F417AF8B76CADC040CC768ECC77752E0C033A69E4431F22F943D8E2367DBD6A654610528B4A721A3011E896F 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_000B_01D82DB5.6CFFE5F0 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Yes, Marc =E2=80=93 I understood you properly and totally. I was just saying about the hope = that there is a trick to keep constraints on the base table level for my = case. Thanks a bunch. Andrew. =20 =20 On Tue, Mar 01, 2022 at 9:00 PM Marc Millas = wrote: 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 =20 On Tue, Mar 1, 2022 at 5:45 PM Andrew Zakharov > 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. =20 ------=_NextPart_000_000B_01D82DB5.6CFFE5F0 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

Yes, Marc =E2=80=93

I understood you properly and totally. I was = just saying about the hope that there is a trick to keep constraints on = the base table level for my case.

Thanks a bunch.

Andrew.

 

 

On = Tue, Mar 01, = 2022 at 9:00 PM =C2=A0Marc Millas <marc.millas@mokadb.com> = wrote:

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

 

 

 

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:<= /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
Subjec= t: Re: Simple task with partitioning which I can't = realize

 <= /o:p>

Hi,

 <= /o:p>

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

 <= /o:p>

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=

 <= /o:p>

 <= /o:p>

 <= /o:p>

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_000B_01D82DB5.6CFFE5F0--