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 1nPPag-0003ah-OT for pgsql-performance@arkaria.postgresql.org; Wed, 02 Mar 2022 14:04:30 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1nPPaf-0002Cb-Lc for pgsql-performance@arkaria.postgresql.org; Wed, 02 Mar 2022 14:04:29 +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 1nPPaf-0002Aw-8I for pgsql-performance@lists.postgresql.org; Wed, 02 Mar 2022 14:04:29 +0000 Received: from mail-qt1-x82e.google.com ([2607:f8b0:4864:20::82e]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1nPPac-0002sI-BV for pgsql-performance@lists.postgresql.org; Wed, 02 Mar 2022 14:04:28 +0000 Received: by mail-qt1-x82e.google.com with SMTP id n11so1629143qtk.8 for ; Wed, 02 Mar 2022 06:04:25 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=message-id:date:mime-version:user-agent:subject:content-language:to :references:from:in-reply-to; bh=uz1V+ujqawOABNfaOFp1C7XqcKYIYVm1bZdu/OdRkeM=; b=B8vLOT8XgZGzxGdC8ersJDhS8jqQBhNr6wT2lhg1b77UQmShOQOhxPmXo5E105Y8OV fMTZUnHkdgY5UWVTQqsBPv9qiBjfCrBPdlhWTwDQ6horyeqlE97jnFYHeqfiVS4tRh5R H1svqLA3RcJ9gnHlrC+JCgjbx7N5AhfMtsssNRqcdzAFU17pOykjGaBNerx9xsR5zBV+ VzERIYU6SPUEbLZPnJnh5QRxUUZor4RQL64XuKSbsTcwcE5KlPsE8zFWdoyk0hQQo8LC Wn683H2Z25YK3g8GwHhUUSEu/HtlCOX80ekJIlQAJwPMQ4ARKnC7avn2cvPTl9/TPrlV zuOg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=x-gm-message-state:message-id:date:mime-version:user-agent:subject :content-language:to:references:from:in-reply-to; bh=uz1V+ujqawOABNfaOFp1C7XqcKYIYVm1bZdu/OdRkeM=; b=0yT3ZXcvVPd8c2hG6OeEwFO9gGM8tkZhpUzcuSB/a4+IaYjsSkzp/sXzYjHagk7tot fWeT7OFh4QFFVSPPnWsKu9rpebm/OS9fq9MPjz5pJoRMexzSpW6FZMJS9phUPgJ4mUr9 oS30jZu155S/4BjtqKBHcqf2dXexGhutKKqbWcN8dqfaV2Qts7xUFoTDfC+97Qis2p3Z KxEU2Pf4+6ZwrriTKsj09R4sxhfuGt7x8ACSqPlradSNYNTHHPODFk+WvyJlrhSGEVXv wv67HJHLm8L/w0NlePL097i78xIi+x/fE5GPwy+Txi0v9GJzGU5m7MMpanK9mDl1BNeB 5fSA== X-Gm-Message-State: AOAM5304d+38uEL6osoFYzXqP9cr5SaX9eDlUw9Tn0NU7d8Im8NJEw8R HSrYz8cRPMau0/g/+MLNQid65T7oFVw= X-Google-Smtp-Source: ABdhPJzz9vQCIOeYhV+Fts3RP/o1YURAIaNaWP8O0ClI2TKmMa4atOwiMra8iyjxK187Uqg2xagrCw== X-Received: by 2002:a05:622a:1648:b0:2de:ade0:3750 with SMTP id y8-20020a05622a164800b002deade03750mr22281458qtj.586.1646229863100; Wed, 02 Mar 2022 06:04:23 -0800 (PST) Received: from [192.168.2.100] (pool-72-79-19-93.nwrknj.east.verizon.net. [72.79.19.93]) by smtp.gmail.com with ESMTPSA id t2-20020a05622a01c200b002de5098fbaesm11438984qtw.26.2022.03.02.06.04.21 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Wed, 02 Mar 2022 06:04:21 -0800 (PST) Content-Type: multipart/alternative; boundary="------------yuIuYpfsQ4FKOxY4m5t3UINN" Message-ID: Date: Wed, 2 Mar 2022 09:04:20 -0500 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:91.0) Gecko/20100101 Thunderbird/91.5.0 Subject: Re: Simple task with partitioning which I can't realize Content-Language: en-US To: pgsql-performance@lists.postgresql.org References: <011d01d82d82$42d20c50$c87624f0$@mail.ru> From: Mladen Gogala In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------yuIuYpfsQ4FKOxY4m5t3UINN Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 3/1/22 10:54, David G. Johnston wrote: > 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, > > I’ve carefully looked thru docs/faqs/google/communities and found > out that I must include “gid” 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-partitioning.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. > Hi David, Are there any concrete plans to address that particular limitation? That limitation can be re-stated as "PostgreSQL doesn't support global indexes on the partitioned tables" and I've have also run into it. My way around it was not to use partitioning but to use much larger machine with the NVME disks, which can handle the necesary I/O. Are there any plans to allow global indexes? I am aware that this is not a small change but is the only real advantage that Oracle holds over PostgreSQL. Regards -- Mladen Gogala Database Consultant Tel: (347) 321-1217 https://dbwhisperer.wordpress.com --------------yuIuYpfsQ4FKOxY4m5t3UINN Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit
On 3/1/22 10:54, David G. Johnston wrote:
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’ve carefully looked thru docs/faqs/google/communities and found out that I must include “gid” 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-partitioning.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.

Hi David,

Are there any concrete plans to address that particular limitation? That limitation can be re-stated as "PostgreSQL doesn't support global indexes on the partitioned tables" and I've have also run into it. My way around it was not to use partitioning but to use much larger machine with the NVME disks, which can handle the necesary I/O. Are there any plans to allow global indexes? I am aware that this is not a small change but is the only real advantage that Oracle holds over PostgreSQL.

Regards

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com
--------------yuIuYpfsQ4FKOxY4m5t3UINN--