From: | "Andrew Zakharov" <Andrew898(at)mail(dot)ru> |
---|---|
To: | <pgsql-performance(at)lists(dot)postgresql(dot)org> |
Subject: | Simple task with partitioning which I can't realize |
Date: | 2022-03-01 15:37:28 |
Message-ID: | 011d01d82d82$42d20c50$c87624f0$@mail.ru |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Tomas Vondra | 2022-03-01 15:39:12 | Re: Never Ending query in PostgreSQL |
Previous Message | Kumar, Mukesh | 2022-03-01 15:01:23 | RE: Never Ending query in PostgreSQL |