From: Andrew Zakharov <Andrew898@mail.ru>
To: 'Marc Millas' <marc.millas@mokadb.com>
Cc: pgsql-performance@lists.postgresql.org
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> (raw)
In-Reply-To: <CADX_1aYe1BNFL--wwvo6+WyzCNEGN=gxwzRtzz=Y9qb+ypzALA@mail.gmail.com>
References: <011d01d82d82$42d20c50$c87624f0$@mail.ru>
<CADX_1aYe1BNFL--wwvo6+WyzCNEGN=gxwzRtzz=Y9qb+ypzALA@mail.gmail.com>
Hi Marc –
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’d believed there is a solutions with keys that’s why I’m seeking the clue.
Thanks.
Andrew.
From: 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
www.mokadb.com <http://www.mokadb.com;
On Tue, Mar 1, 2022 at 4:37 PM Andrew Zakharov <Andrew898@mail.ru <mailto:Andrew898@mail.ru> > wrote:
Hello all –
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,
…
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’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. Thus partition method “by list” is not appropriate method in this case and “by range” 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 “id” 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’s true.
Your advices are very important.
Thanks in advance.
Andrew.
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, marc.millas@mokadb.com, pgsql-performance@lists.postgresql.org
Subject: RE: Simple task with partitioning which I can't realize
In-Reply-To: <007501d82d8b$c97239c0$5c56ad40$@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