From: Andrew Zakharov <Andrew898@mail.ru>
To: 'David G. Johnston' <david.g.johnston@gmail.com>
Cc: 'Pgsql Performance' <pgsql-performance@lists.postgresql.org>
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> (raw)
In-Reply-To: <CAKFQuwZbJObQmVcCOYNiYS_PHu2n39xZzoFY_4KZLSZ6MWuPsQ@mail.gmail.com>
References: <011d01d82d82$42d20c50$c87624f0$@mail.ru>
<CAKFQuwZbJObQmVcCOYNiYS_PHu2n39xZzoFY_4KZLSZ6MWuPsQ@mail.gmail.com>
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: 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 <mailto: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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: Andrew898@mail.ru, david.g.johnston@gmail.com, pgsql-performance@lists.postgresql.org
Subject: RE: Simple task with partitioning which I can't realize
In-Reply-To: <004c01d82d8a$988f1400$c9ad3c00$@mail.ru>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox