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@xxxxxxxxxx>
Sent: Tuesday, March 01, 2022 7:29 PM
To: Andrew Zakharov <Andrew898@xxxxxxx>
Cc: pgsql-performance@xxxxxxxxxxxxxxxxxxxx
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.
On Tue, Mar 1, 2022 at 4:37 PM Andrew Zakharov <Andrew898@xxxxxxx> 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.
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.
On Tue, Mar 1, 2022 at 5:45 PM Andrew Zakharov <Andrew898@xxxxxxx> wrote:
