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 1nP5Uw-0001zV-A1 for pgsql-performance@arkaria.postgresql.org; Tue, 01 Mar 2022 16:37:15 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1nP5Uv-0003f0-8I for pgsql-performance@arkaria.postgresql.org; Tue, 01 Mar 2022 16:37:13 +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 1nP5Uu-0003er-O5 for pgsql-performance@lists.postgresql.org; Tue, 01 Mar 2022 16:37:12 +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 1nP5Ur-0000tJ-Jc for pgsql-performance@lists.postgresql.org; Tue, 01 Mar 2022 16:37:12 +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=cHbuCgi9gVP1sOhggcH+e+8v5fHSe/wWC2zB7hVIjlI=; t=1646152629;x=1646758029; b=gpNayL1eDPuvddnX9Snwv2uw/UgpM0x2KOXbw+AG8BE46WrIUYuBanVQTf2iR8h9hr2iXZceJTVUizg0GuFjSns3FVVdicnT4NkQ983YKCf5ss9a8jhg6FXA8qbNXh8fEs4BXzH2MzzS7ckbkpoWdcL3G7GRGAXutSVxsZSgon5dGh5J+1OgI4ShwksBH0lpsuAjlEaeHRZOsenhx7sPkFr9wTPYz4u1bkx5N2kwnEVTAhGGX3Piuh8hGOlPNv3LcWITyOmXBmsfGhgUiAOdUMufuHMWtoF+k7hqp69BTqkHOKiVCOhHsHu3Va+GQ7DEHXWn+CWQLDyk1BswCrcoQg==; Received: by smtp50.i.mail.ru with esmtpa (envelope-from ) id 1nP5Up-0007tC-OO; Tue, 01 Mar 2022 19:37:08 +0300 From: "Andrew Zakharov" To: "'David G. Johnston'" Cc: "'Pgsql Performance'" 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:37:09 +0300 Message-ID: <004c01d82d8a$988f1400$c9ad3c00$@mail.ru> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_004D_01D82DA3.BDDEBD00" X-Mailer: Microsoft Outlook 16.0 Thread-Index: AQMqCSf02QMa+gddxm77SFmyorw+2AG/CaBSqfkHGuA= Content-Language: ru Authentication-Results: smtp50.i.mail.ru; auth=pass smtp.auth=andrew898@mail.ru smtp.mailfrom=andrew898@mail.ru X-4EC0790: 10 X-7564579A: B8F34718100C35BD X-77F55803: 4F1203BC0FB41BD9A10ECB78D781867A73E61AC041C28E181D86CEDA692AA9CA182A05F538085040F2EF41F53E73EFA9E699184CFA57BDA131D99BCAF59BA93F38CC1B994563686F X-7FA49CB5: FF5795518A3D127A4AD6D5ED66289B5278DA827A17800CE7235646FAB97B4BEDEA1F7E6F0F101C67BD4B6F7A4D31EC0BCC500DACC3FED6E28638F802B75D45FF8AA50765F7900637593B4F2A76947A308638F802B75D45FF36EB9D2243A4F8B5A6FCA7DBDB1FC311F39EFFDF887939037866D6147AF826D82CEE6754B7D47E92B9F2A0E8CE42C6676F9789CCF6C18C3F8528715B7D10C86878DA827A17800CE7CB47DC196355BE109FA2833FD35BB23D9E625A9149C048EE26055571C92BF10FC26CFBAC0749D213D2E47CDBA5A96583BD4B6F7A4D31EC0BC014FD901B82EE079FA2833FD35BB23D27C277FBC8AE2E8BAA1C5B227563B4AFA471835C12D1D977C4224003CC836476EB9C4185024447017B076A6E789B0E975F5C1EE8F4F765FC5C53FBF615C06F413AA81AA40904B5D9CF19DD082D7633A078D18283394535A93AA81AA40904B5D98AA50765F7900637C991F317CB49A26CD81D268191BDAD3D698AB9A7B718F8C4D1B931868CE1C5781A620F70A64A45A98AA50765F79006372E808ACE2090B5E1725E5C173C3A84C3C5EA940A35A165FF2DBA43225CD8A89FB26E97DCB74E62525E1C53F199C2BB95B5C8C57E37DE458BEDA766A37F9254B7 X-8FC586DF: 6EFBBC1D9D64D975 X-C1DE0DAB: 0D63561A33F958A5E6C8FF1E65897FE5CA0F3453E39840B44195E8AD2A1B66D9D59269BC5F550898D99A6476B3ADF6B47008B74DF8BB9EF7333BD3B22AA88B938A852937E12ACA75DF4B37519D6BB15B410CA545F18667F91A7EA1CDA0B5A7A0 X-C8649E89: 4E36BF7865823D7055A7F0CF078B5EC49A30900B95165D34D75CEA0D7A80616ECDA7D54131A8B86E92CAAA0405DAC6EB685C970A0B5997B41A1930739B235A5F1D7E09C32AA3244CB64422B87553D8815699E5FE2D72194464EE5813BBCA3A9D729B2BEF169E0186 X-D57D3AED: 3ZO7eAau8CL7WIMRKs4sN3D3tLDjz0dLbV79QFUyzQ2Ujvy7cMT6pYYqY16iZVKkSc3dCLJ7zSJH7+u4VD18S7Vl4ZUrpaVfd2+vE6kuoey4m4VkSEu530nj6fImhcD4MUrOEAnl0W826KZ9Q+tr5ycPtXkTV4k65bRjmOUUP8cvGozZ33TWg5HZplvhhXbhDGzqmQDTd6OAevLeAnq3Ra9uf7zvY2zzsIhlcp/Y7m53TZgf2aB4JOg4gkr2biojWGWfNw4NKOlvNEGvuIwbLg== X-Mailru-Sender: ED747E36EB90C325F1084D3EBE0B3C2907F334F64E7A1C78E699184CFA57BDA1E261594A1D9C71E476CADC040CC768ECC77752E0C033A69E4431F22F943D8E2367DBD6A654610528B4A721A3011E896F 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_004D_01D82DA3.BDDEBD00 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable David, - yes, creation composite foreign/primary key is not a problem. = But the main question is what method should I use for partitioning by = composite key gid, region_code? The partition method itself created not = only for faster data access but for better administration. The = administration like a truncate/insert is a main reason why I split the = data for my DWH case. If the only hash method is left I cannot = administer the partitions separately this way. But anyway, could you = please provide your vision the brief declaration for main table and = partition? Thanks. Andrew. =20 From: David G. Johnston =20 Sent: Tuesday, March 01, 2022 6:54 PM To: Andrew Zakharov Cc: Pgsql Performance Subject: Re: Simple task with partitioning which I can't realize =20 On Tue, Mar 1, 2022 at 8:37 AM Andrew Zakharov > wrote: create table region_hierarchy( gid uuid not null default uuid_generate_v1mc(), parent_gid uuid null, region_code int2, =20 =20 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. =20 Yes, you are coming up against the following limitation: =20 "Unique constraints (and hence primary keys) on partitioned tables must = include all the partition key columns. This limitation exists because = the individual indexes making up the constraint can only directly = enforce uniqueness within their own partitions; therefore, the partition = structure itself must guarantee that there are not duplicates in = different partitions." =20 https://www.postgresql.org/docs/current/ddl-partitioning.html#DDL-PARTITI= ONING-DECLARATIVE =20 That limitation is independent of partitioning; i.e., the legacy = inheritance option doesn't bypass it. =20 Thus, your true "key" is composite: (region, identifier). Thus you need = to add a "parent_region_code" column as well, redefine the PK as = (region_code, gid), and the REFERENCES clause to link the two paired = fields. =20 You can decide whether that is sufficient or if you want some added = comfort in ensuring that a gid cannot appear in multiple regions by = creating a single non-partitioned table containing all gid values and = add a unique constraint there. =20 Or maybe allow for duplicates across region codes and save space by = using a smaller data type (int or bigint - while renaming the column to = "rid" or some such) - combined with having the non-partitioned reference = table being defined as (region_code, rid, gid). =20 David J. =20 ------=_NextPart_000_004D_01D82DA3.BDDEBD00 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

David, - yes, creation composite = foreign/primary key is not a problem. But the main question is what = method should I use for partitioning by composite key gid, region_code? = The partition method itself created not only for faster data access but = for better administration. The administration like a truncate/insert is = a main reason why I split the data for my DWH case. If the only hash = method is left I cannot administer the partitions separately this way. = But anyway, could you please provide your vision the brief declaration = for main table and partition?

Thanks.

Andrew.

 

From:<= /b> = David G. Johnston <david.g.johnston@gmail.com>
Sent: = Tuesday, March 01, 2022 6:54 PM
To: Andrew Zakharov = <Andrew898@mail.ru>
Cc: Pgsql Performance = <pgsql-performance@lists.postgresql.org>
Subject: Re: = Simple task with partitioning which I can't = realize

 

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

create table = region_hierarchy(

  gid uuid not = null default uuid_generate_v1mc(),

  parent_gid uuid = null,

  region_code = int2,

 

 

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.

 

Yes, = you are coming up against the following = limitation:

 

"Unique constraints (and = hence primary keys) on partitioned tables must include all the partition = key columns. This limitation exists because the individual indexes = making up the constraint can only directly enforce uniqueness within = their own partitions; therefore, the partition structure itself must = guarantee that there are not duplicates in different = partitions."

 

https://www.postgresql.org/docs/current/ddl-pa= rtitioning.html#DDL-PARTITIONING-DECLARATIVE

 

That limitation is independent = of partitioning; i.e., the legacy inheritance option doesn't bypass = it.

 

Thus, your true "key" = is composite: (region, identifier).  Thus you need to add a = "parent_region_code" column as well, redefine the PK as = (region_code, gid), and the REFERENCES clause to link the two paired = fields.

 

You can decide whether that is = sufficient or if you want some added comfort in ensuring that a gid = cannot appear in multiple regions by creating a single non-partitioned = table containing all gid values and add a unique constraint = there.

 

Or maybe allow for duplicates = across region codes and save space by using a smaller data type (int or = bigint - while renaming the column to "rid" or some such) - = combined with having the non-partitioned reference table being defined = as (region_code, rid, gid).

 

David = J.

 

------=_NextPart_000_004D_01D82DA3.BDDEBD00--