From: | Marc Millas <marc(dot)millas(at)mokadb(dot)com> |
---|---|
To: | Andrew Zakharov <Andrew898(at)mail(dot)ru> |
Cc: | pgsql-performance(at)lists(dot)postgresql(dot)org |
Subject: | Re: Simple task with partitioning which I can't realize |
Date: | 2022-03-01 16:28:45 |
Message-ID: | CADX_1aYe1BNFL--wwvo6+WyzCNEGN=gxwzRtzz=Y9qb+ypzALA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
On Tue, Mar 1, 2022 at 4:37 PM Andrew Zakharov <Andrew898(at)mail(dot)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.
>
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Andrew Zakharov | 2022-03-01 16:37:09 | RE: Simple task with partitioning which I can't realize |
Previous Message | ldh@laurent-hasson.com | 2022-03-01 16:28:31 | RE: An I/O error occurred while sending to the backend (PG 13.4) |